[[381365]]

本文轉(zhuǎn)載自微信公眾號(hào)「DBA閑思雜想錄」，作者瀟湘隱者 。轉(zhuǎn)載本文請(qǐng)聯(lián)系DBA閑思雜想錄公眾號(hào)。  

SQL Server的嵌套存儲(chǔ)過(guò)程，外層存儲(chǔ)過(guò)程和內(nèi)層存儲(chǔ)過(guò)程(被嵌套調(diào)用的存儲(chǔ)過(guò)程)中可以存在相同名稱的本地臨時(shí)表嗎?如果可以的話，那么有沒(méi)有什么問(wèn)題或限制呢?在嵌套存儲(chǔ)過(guò)程中，調(diào)用的是外層存儲(chǔ)過(guò)程的臨時(shí)表還是自己定義的臨時(shí)表呢?是否類似高級(jí)語(yǔ)言的變量一樣，本地臨時(shí)表有沒(méi)有“作用域“范圍呢?

注意：也可以稱呼為父存儲(chǔ)過(guò)程和子存儲(chǔ)過(guò)程，外層存儲(chǔ)過(guò)程和內(nèi)層存儲(chǔ)過(guò)程...。這些只是不同的稱呼或叫法而已。我們這里統(tǒng)一使用外層存儲(chǔ)過(guò)程和內(nèi)層存儲(chǔ)過(guò)程。后續(xù)文章部分不再述說(shuō)。

我們先來(lái)看一個(gè)例子，如下所示，我們構(gòu)造一個(gè)簡(jiǎn)單的例子。

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.PRC_TEST') AND OBJECTPROPERTY(object_id, 'IsProcedure') =1) 
BEGIN 
 DROP PROCEDURE dbo.PRC_TEST 
END 
GO 
CREATE PROC dbo.PRC_TEST 
AS 
BEGIN 
 
 CREATE TABLE #tmp_test(id INT); 
 
 INSERT INTO #tmp_test 
 SELECT 1; 
 
 SELECT * FROM #tmp_test; 
 
 EXEC PRC_SUB_TEST 
 
 SELECT * FROM #tmp_test 
  
 
END 
GO 
 
 
 
IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id= OBJECT_ID(N'dbo.PRC_SUB_TEST' ) AND OBJECTPROPERTY(object_id, 'IsProcedure')=1) 
BEGIN 
 DROP PROCEDURE dbo.PRC_SUB_TEST; 
END 
GO 
 
 
CREATE PROCEDURE dbo.PRC_SUB_TEST 
AS 
BEGIN 
     
 CREATE TABLE #tmp_test(name VARCHAR(128)); 
 
 INSERT INTO #tmp_test 
 SELECT name FROM sys.objects 
 
 SELECT * FROM #tmp_test; 
END 
GO 
 
 
EXEC PRC_TEST; 

簡(jiǎn)單測(cè)試似乎正常，并沒(méi)有發(fā)現(xiàn)什么問(wèn)題。如果此時(shí)你就下一個(gè)結(jié)論的話，那么就為時(shí)過(guò)早了!打個(gè)比方，你看見(jiàn)一只天鵝是白色的，如果你下了一個(gè)定論：“所有天鵝都是白色的”，其實(shí)這個(gè)世界真的有黑天鵝，只是你沒(méi)有見(jiàn)過(guò)而已!如下所示，我們修改一下存儲(chǔ)過(guò)程dbo.PRC_SUB_TEST，使用字段名name替換*，如下所示：

IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id= OBJECT_ID(N'dbo.PRC_SUB_TEST' ) AND OBJECTPROPERTY(object_id, 'IsProcedure')=1) 
BEGIN 
 DROP PROCEDURE dbo.PRC_SUB_TEST; 
END 
GO 
 
CREATE PROCEDURE dbo.PRC_SUB_TEST 
AS 
BEGIN 
     
 CREATE TABLE #tmp_test(name VARCHAR(128)); 
 
 INSERT INTO #tmp_test 
 SELECT name FROM sys.objects 
 
 SELECT name FROM #tmp_test; 
END 
GO 

然后重復(fù)上面測(cè)試，如下所示，此時(shí)執(zhí)行存儲(chǔ)過(guò)程dbo.PRC_TEST的話，就會(huì)報(bào)錯(cuò)：“Invalid column name 'name'.”

此時(shí)只要先我執(zhí)行一次存儲(chǔ)過(guò)程dbo.PRC_SUB_TEST，然后再去執(zhí)行存儲(chǔ)過(guò)程dbo.PRC_TEST就不會(huì)報(bào)錯(cuò)了。而且只要執(zhí)行過(guò)一次這個(gè)存儲(chǔ)過(guò)程，然后在當(dāng)前會(huì)話或其它任何會(huì)話執(zhí)行dbo.PRC_TEST都不會(huì)報(bào)錯(cuò)了。是否非常讓人迷惑或不解。

EXEC dbo.PRC_SUB_TEST; 
EXEC PRC_TEST; 

如果你要再次重現(xiàn)這個(gè)現(xiàn)象的話，只能通過(guò)下面SQL或者刪除/重建存儲(chǔ)過(guò)程的方式，才能重現(xiàn)這個(gè)現(xiàn)象。似乎有點(diǎn)幽靈現(xiàn)象的感覺(jué)。

DBCC FREEPROCCACHE 

關(guān)于這個(gè)現(xiàn)象，官方文檔(詳見(jiàn)參考資料的鏈接地址)有這么一段描述：

A local temporary table created within a stored procedure or trigger can have the same name as a temporary table that was created before the stored procedure or trigger is called. However, if a query references a temporary table and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against. Nested stored procedures can also create temporary tables with the same name as a temporary table that was created by the stored procedure that called it. However, for modifications to resolve to the table that was created in the nested procedure, the table must have the same structure, with the same column names, as the table created in the calling procedure. This is shown in the following example.

在存儲(chǔ)過(guò)程或觸發(fā)器中創(chuàng)建的本地臨時(shí)表的名稱可以與在調(diào)用存儲(chǔ)過(guò)程或觸發(fā)器之前創(chuàng)建的臨時(shí)表名稱相同。但是，如果查詢引用臨時(shí)表，而同時(shí)有兩個(gè)同名的臨時(shí)表，則不定義針對(duì)哪個(gè)表解析該查詢。嵌套存儲(chǔ)過(guò)程同樣可以創(chuàng)建與調(diào)用它的存儲(chǔ)過(guò)程所創(chuàng)建的臨時(shí)表同名的臨時(shí)表。但是，為了對(duì)其進(jìn)行修改以解析為在嵌套過(guò)程中創(chuàng)建的表，此表必須與調(diào)用過(guò)程創(chuàng)建的表具有相同的結(jié)構(gòu)和列名。下面的示例說(shuō)明了這一點(diǎn)。

CREATE PROCEDURE dbo.Test2 
AS 
    CREATE TABLE #t(x INT PRIMARY KEY); 
    INSERT INTO #t VALUES (2); 
    SELECT Test2Col = x FROM #t; 
GO 
 
CREATE PROCEDURE dbo.Test1 
AS 
    CREATE TABLE #t(x INT PRIMARY KEY); 
    INSERT INTO #t VALUES (1); 
    SELECT Test1Col = x FROM #t; 
EXEC Test2; 
GO 
 
CREATE TABLE #t(x INT PRIMARY KEY); 
INSERT INTO #t VALUES (99); 
GO 
 
EXEC Test1; 
GO 

官方文檔中“同時(shí)有兩個(gè)同名的臨時(shí)表，則不定義針對(duì)哪個(gè)表解析該查詢”這種闡述感覺(jué)還是讓人有點(diǎn)迷糊。這里簡(jiǎn)單解釋一下，在存儲(chǔ)過(guò)程的嵌套調(diào)用中，允許外層過(guò)程和內(nèi)層存儲(chǔ)過(guò)程中存在相同名字的本地臨時(shí)表，但是在內(nèi)存過(guò)程中，如果要對(duì)其進(jìn)行修改或解析(修改很好理解，例如新增索引，增加字段等這類DDL操作;關(guān)于解析，查詢臨時(shí)表，SQL中指定字段名，就需要解析resolve)，那么此時(shí)這個(gè)臨時(shí)表必須表結(jié)構(gòu)一致，否則就會(huì)報(bào)錯(cuò)。官方文檔，就是這么一句話，告訴你不行，但是具體原因沒(méi)有說(shuō)。那么我們不妨做一些推測(cè)，在存儲(chǔ)過(guò)程的嵌套調(diào)用中，是否創(chuàng)建了兩個(gè)本地臨時(shí)表呢?有沒(méi)有可能實(shí)際只創(chuàng)建了一個(gè)本地臨時(shí)表呢?出現(xiàn)本地臨時(shí)表重用的情況呢?那么我們簡(jiǎn)單驗(yàn)證一下，如下所示，這里可以判斷實(shí)際上創(chuàng)建了兩個(gè)本地臨時(shí)表。并沒(méi)有出現(xiàn)臨時(shí)表重用的情況。

SELECT *  
FROM sys.dm_os_performance_counters 
WHERE counter_name LIKE 'Temp Tables Creation Rate%'; 
 
EXEC PRC_TEST; 
 
SELECT *  
FROM sys.dm_os_performance_counters 
WHERE counter_name LIKE 'Temp Tables Creation Rate%'; 

當(dāng)然你可以用下面SQL來(lái)進(jìn)行驗(yàn)證，跟上面驗(yàn)證的結(jié)果一致。

IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id= OBJECT_ID(N'dbo.PRC_SUB_TEST' ) AND OBJECTPROPERTY(object_id, 'IsProcedure')=1) 
BEGIN 
 DROP PROCEDURE dbo.PRC_SUB_TEST; 
END 
GO 
 
 
CREATE PROCEDURE dbo.PRC_SUB_TEST 
AS 
BEGIN 
     
 SELECT * FROM #tmp_test; 
 
 SELECT *  FROM tempdb.dbo.sysobjects WHERE name LIKE '#tmp_test%' 
 CREATE TABLE #tmp_test(name VARCHAR(128)); 
 
 INSERT INTO #tmp_test 
 SELECT name FROM sys.objects 
 SELECT *  FROM tempdb.dbo.sysobjects WHERE name LIKE '#tmp_test%' 
 SELECT * FROM #tmp_test; 
END 
GO 

然后我們來(lái)看看臨時(shí)表的“作用域”，抱歉我用這么一個(gè)概念，官方文檔是沒(méi)有這個(gè)概念，這個(gè)只是我們思考的一個(gè)方面，細(xì)節(jié)方面沒(méi)有必要抬杠。如下所示，我們修改一下存儲(chǔ)過(guò)程

IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id= OBJECT_ID(N'dbo.PRC_SUB_TEST' ) AND OBJECTPROPERTY(object_id, 'IsProcedure')=1) 
BEGIN 
 DROP PROCEDURE dbo.PRC_SUB_TEST; 
END 
GO 
CREATE PROCEDURE dbo.PRC_SUB_TEST 
AS 
BEGIN 
     
 SELECT * FROM #tmp_test; 
 CREATE TABLE #tmp_test(name VARCHAR(128)); 
 
 INSERT INTO #tmp_test 
 SELECT name FROM sys.objects 
 
 SELECT * FROM #tmp_test; 
END 
GO 

通過(guò)實(shí)驗(yàn)驗(yàn)證，我們發(fā)現(xiàn)外層存儲(chǔ)過(guò)程的臨時(shí)表在內(nèi)層存儲(chǔ)過(guò)程中有效，它的“作用域”是在內(nèi)層存儲(chǔ)過(guò)程的同名臨時(shí)表創(chuàng)建之前， 這個(gè)跟高級(jí)語(yǔ)言中的全局變量和局部變量作用域有點(diǎn)類似。

既然創(chuàng)建了兩個(gè)本地臨時(shí)表，那么為什么修改或解析的時(shí)候就會(huì)報(bào)錯(cuò)呢?個(gè)人的一個(gè)猜測(cè)是，優(yōu)化器解析過(guò)后，在執(zhí)行過(guò)程中，解析或修改的時(shí)候，數(shù)據(jù)庫(kù)引擎無(wú)法判斷或者代碼里面沒(méi)有這種邏輯去控制檢索哪一個(gè)臨時(shí)表。有可能是代碼里面的一個(gè)缺陷亦或是某種邏輯原因?qū)е隆Ｉ鲜鰞H僅是個(gè)人的一個(gè)猜測(cè)、推理。如有不足或不對(duì)的地方，敬請(qǐng)指正。

參考資料：

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms174979(v=sql.110)?redirectedfrom=MSDN