SQL Server的嵌套存盤程序中使用同名的臨時表怪像淺析

   SQL Server的嵌套存盤程序，外層存盤程序和內層存盤程序（被嵌套呼叫的存盤程序）中可以存在相同名稱的本地臨時表嗎？如果可以的話，那么有沒有什么問題或限制呢？ 在嵌套存盤程序中，呼叫的是外層存盤程序的臨時表還是自己定義的臨時表呢？ 是否類似高級語言的變數一樣，本地臨時表有沒有“作用域“范圍呢？

注意：也可以稱呼為父存盤程序和子存盤程序，外層存盤程序和內層存盤程序，這些只是不同的稱呼或叫法而已，我們這里統一使用外層存盤程序和內層存盤程序，后續文章部分不再述說，

我們先來看一個例子，如下所示，我們構造一個簡單的例子， 

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;

簡單測驗似乎正常，并沒有發現什么問題，如果此時你就下一個結論的話，那么就為時過早了！ 打個比方，你看見一只天鵝是白色的，如果你下了一個定論：“所有天鵝都是白色的”，其實這個世界真的有黑天鵝，只是你沒有見過而已！如下所示，我們修改一下存盤程序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

然后重復上面測驗，如下所示，此時執行存盤程序dbo.PRC_TEST的話，就會報錯：“Invalid column name 'name'.”

此時只要先我執行一次存盤程序dbo.PRC_SUB_TEST，然后再去執行存盤程序dbo.PRC_TEST就不會報錯了，而且只要執行過一次這個存盤程序，然后在當前會話或其它任何會話執行dbo.PRC_TEST都不會報錯了，是否非常讓人迷惑或不解， 

EXEC dbo.PRC_SUB_TEST;

EXEC PRC_TEST;

如果你要再次重現這個現象的話，只能通過下面SQL或者洗掉/重建存盤程序的方式，才能重現這個現象，似乎有點幽靈現象的感覺，

DBCC FREEPROCCACHE

  關于這個現象，官方檔案（詳見參考資料的鏈接地址）有這么一段描述：

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.

在存盤程序或觸發器中創建的本地臨時表的名稱可以與在呼叫存盤程序或觸發器之前創建的臨時表名稱相同， 但是，如果查詢參考臨時表，而同時有兩個同名的臨時表，則不定義針對哪個表決議該查詢， 嵌套存盤程序同樣可以創建與呼叫它的存盤程序所創建的臨時表同名的臨時表，但是，為了對其進行修改以決議為在嵌套程序中創建的表，此表必須與呼叫程序創建的表具有相同的結構和列名，下面的示例說明了這一點，

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

官方檔案中“同時有兩個同名的臨時表，則不定義針對哪個表決議該查詢”這種闡述感徑訓是讓人有點迷糊，這里簡單解釋一下，在存盤程序的嵌套呼叫中，允許外層程序和內層存盤程序中存在相同名字的本地臨時表，但是在記憶體程序中，如果要對其進行修改或決議（修改很好理解，例如新增索引，增加欄位等這類DDL操作；關于決議，查詢臨時表，SQL中指定欄位名，就需要決議resolve），那么此時這個臨時表必須表結構一致，否則就會報錯，官方檔案，就是這么一句話，告訴你不行，但是具體原因沒有說，那么我們不妨做一些推測，在存盤程序的嵌套呼叫中，是否創建了兩個本地臨時表呢？有沒有可能實際只創建了一個本地臨時表呢？出現本地臨時表重用的情況呢？ 那么我們簡單驗證一下，如下所示，這里可以判斷實際上創建了兩個本地臨時表，并沒有出現臨時表重用的情況， 

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%';

當然你可以用下面SQL來進行驗證，跟上面驗證的結果一致， 

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

然后我們來看看臨時表的“作用域”，抱歉我用這么一個概念，官方檔案是沒有這個概念，這個只是我們思考的一個方面，細節方面沒有必要抬杠，如下所示，我們修改一下存盤程序

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

通過實驗驗證，我們發現外層存盤程序的臨時表在內層存盤程序中有效，它的“作用域”是在內層存盤程序的同名臨時表創建之前，這個跟高級語言中的全域變數和區域變數作用域有點類似，

既然創建了兩個本地臨時表，那么為什么修改或決議的時候就會報錯呢？ 個人的一個猜測是，優化器決議過后，在執行程序中，決議或修改的時候，資料庫引擎無法判斷或者代碼里面沒有這種邏輯去控制檢索哪一個臨時表，有可能是代碼里面的一個缺陷亦或是某種邏輯原因導致，上述僅僅是個人的一個猜測、推理，如有不足或不對的地方，敬請指正，

參考資料：

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

標籤：其他

上一篇：SQL Server的嵌套存盤程序中使用同名的臨時表怪像淺析

下一篇：騰訊云TcaplusDB成為首批通過信通院-鍵值型記憶體資料庫功能評測的產品，樹立記憶體資料庫行業標桿