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