代码改变世界

There is already an object named '#xxxx' in the database.

2018-12-31 10:23  潇湘隐者  阅读(1584)  评论(0)  编辑  收藏  举报

这个案例是前几天同事遇到的一个案例，在存储过程中“删除”了一个临时表，然后重新创建这个临时表时遇到“There is already an object named 'xxxx' in the database."这样的错误。下面简单演示一下这个案例（不用存储过程，而是直接用简单的SQL语句重现）

 

 

CREATE TABLE #temp_test( id INT, name VARCHAR(32));
 
 
IF  EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name='#tmp_test')
BEGIN
    DROP TABLE #temp_test;
END
GO
 
 
CREATE TABLE #temp_test( id INT, name VARCHAR(32));

 

 

clip_image001

 

 

 

   同事很是不解，问我为什么在存储过程里面“删除”了这个临时表居然不能重建呢？ 其实这里面并没有什么玄机，而是仅仅犯了逻辑错误。上面这个SQL语句，其实永远也不会删除这个临时表。 

 

   原因很简单，临时表#temp_test在tempdb..sysobjects里面存储的名字为“#temp_test_________________________________________________________________0000000000EE”,所以上述脚本犯了一个逻辑错误： 开发人员以为临时表被删除了，其实实质上永远不会执行DROP TABLE #temp_test这句SQL。存储在tempdb的sysobjects表中的临时表，其全名由 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。为了允许追加后缀，为本地临时表指定的table_name不能超过 116 个字符（关于为什么是116字符，也是有原因的，此处不展开）

 

 

Both regular and delimited identifiers must contain from 1 through 128 characters. For local temporary tables, the identifier can have a maximum of 116 characters.

 

clip_image002

 

 

那么为什么数据库自动会给临时表这样命名呢？ 因为本地临时表是对当前连接（当前会话）可见的。但是任意会话都可以创建同样名字的临时表，那么临时表的元数据在数据库内部存储的时候，使用同样的名字就不能定位和区别，所以设计上，为了区别不同会话下同样命名的临时表，在tempdb..sysobjects里面临时表会保存的是数据库自动生成的名字（#临时表名+下划线+12位的十六进制字符），如下测试所示，在另外一个会话中，我们创建一个同样名字的临时表，然后我们去数据库检查，你会发现tempdb..sysobjects中有两个对象，他们名字的后缀是不一样的。

 

SELECT @@SPID;

GO

CREATE TABLE #temp_test( id INT, name VARCHAR(32));

 

 

clip_image003

 

 

那么要如何定位、查找当前会话是否创建了本地临时表对象呢？ 其实使用OBJECT_ID函数就OK了：

 

 

 

IF OBJECT_ID('tempdb..#temp_test' ) IS NOT NULL DROP TABLE #temp_test;

 

 

 

那么存储过程中使用上述脚本删除本地临时表，然后重建同样名字的临时表就可以了吗？ 事实告诉我们，虽然上面SQL可以找到当前会话创建的本地临时表删除，但是如果是在存储过程里面，使用这种方式，创建本地临时表，然后删除、创建，依然会遇到这个错误，如下测试所示：

 

 

 

CREATE PROCEDURE PRC_TEST
AS
BEGIN 
 
    IF OBJECT_ID('tempdb..#temp_test' ) IS NOT NULL DROP TABLE #temp_test;
    CREATE TABLE #temp_test( id INT, name VARCHAR(32));
 
    INSERT INTO #temp_test VALUES(10, 'jimmy');
 
    IF OBJECT_ID('tempdb..#temp_test' ) IS NOT NULL DROP TABLE #temp_test;
 
    CREATE TABLE #temp_test( id INT, name VARCHAR(32));
 
    INSERT INTO #temp_test VALUES(100, 'kerry');
 
 
    DROP TABLE #temp_test;
END
GO

 

 

clip_image004

 

 

其实在一个批处理里面执行下面SQL语句也会报错，但是改写一下SQL，在删除后面加上一个GO语句，那么这样是不会报错的(但是你单个SQL，一条一条执行是不会报错的).

 

 

    CREATE TABLE #temp_test( id INT, name VARCHAR(32));

 

    INSERT INTO #temp_test VALUES(10, 'jimmy');

 

    IF OBJECT_ID('tempdb..#temp_test' ) IS NOT NULL DROP TABLE #temp_test;

 

    CREATE TABLE #temp_test( id INT, name VARCHAR(32));

 

    INSERT INTO #temp_test VALUES(100, 'kerry');

 

 

    DROP TABLE #temp_test;

 

 

 

clip_image005

 

 

至于原因是什么呢？网上有种分析是因为解析错误（parse error），这里我也倾向于这种说法，因为测试过程中，发现其实上面SQL语句报错，但是实质上，本地临时表已经在tempdb被删除了。加上一个GO这种改写方法，其实使用两个批处理，下面这样的SQL是不会报错的。

 

 

    CREATE TABLE #temp_test( id INT, name VARCHAR(32));

 

    INSERT INTO #temp_test VALUES(10, 'jimmy');

 

    IF OBJECT_ID('tempdb..#temp_test' ) IS NOT NULL DROP TABLE #temp_test;

 

    GO

 

    CREATE TABLE #temp_test( id INT, name VARCHAR(32));

 

    INSERT INTO #temp_test VALUES(100, 'kerry');

 

 

    DROP TABLE #temp_test;

 

 

 

 

参考资料：

 

https://blog.sqlauthority.com/2012/05/01/sql-server-maximum-allowable-length-of-characters-for-temp-objects-is-116-guest-post-by-balmukund-lakhani/

https://blogs.msdn.microsoft.com/sqlserverfaq/2012/03/15/an-interesting-find-about-temp-tables-in-sql-server/