THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Zilberstein

Name resolution in SQL Server

This post is inspired by recent post by Mladen Prajdić. Mladen had found out (as many did before him - hanging around in blogs and communities, I see questions about this issue from time to time) that name resolution works for temporary tables differs from the one for permanent tables. While for permanent table more than one CREATE TABLE statement with the same table name can be issued inside the same batch or procedure, for temporary table it will fail compilation saying: "There is already an object named '#t1' in the database".

-- Succeeds
CREATE TABLE t1(col1 int);
DROP TABLE t1(col1 int);
CREATE TABLE t1(col1 int);
GO

-- Fails
CREATE TABLE #t1(col1 int);
DROP TABLE #t1(col1 int);
CREATE TABLE #t1(col1 int);
GO

Reasons for this differentiation are historical. Deferred name resolution had been introduced in SQL Server 7  but for permanent tables only. It doesn't work for temporary tables and doesn't work for variables even when defined in different scope:

-- Fails with "The variable name '@a' has already been declared" error despite the fact that @a variable is declared in 2 different scopes.
BEGIN 
    BEGIN 
       DECLARE
@a INT 
    END
    BEGIN 
       DECLARE
@a INT 
    END 
END
GO

Possible workaround for temporary tables (if for some reason you desperately need to create twice temporary table with the same name inside the batch) is to alter table schema instead of dropping and recreating from scratch.

CREATE TABLE #t1(col1 int);
ALTER TABLE #t1 ADD col2 VARCHAR(32);
ALTER TABLE #t1 DROP COLUMN col1 
GO
Published Thursday, August 28, 2008 2:57 PM by Michael Zilberstein
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement