Difference between Tables and Temp Tables

Question

why this code work without problem :

drop table t1 
select * into t1 from  master..spt_values
drop table t1 
select * into t1 from  master..spt_values

Output

Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 't1', because it does not exist or you do not have permission.

(2508 row(s) affected)

(2508 row(s) affected)

but this code does not :

drop table #t1 
select * into #t1 from  master..spt_values
drop table #t1 
select * into #t1 from  master..spt_values

Output

Msg 2714, Level 16, State 1, Line 4
There is already an object named '#t1' in the database.

what is the difference between Tables and Temp Tables in this code ?

Answer 1

To counter all the other wrong answers, the correct way to test for a #temp table is

if object_id('tempdb..#temp') is not null
   drop table #temp;

---

Here's an interesting article about compile phase and execution phase fun with #temp tables.

---

This is the MSDN reference for Deferred Name Resolution (DNR). To aid in Stored Procedure creation and statements batches, Deferred Name Resolution was added in SQL Server 7. Prior to that (Sybase), it would have been very hard to create and use tables within a batch without using a lot of dynamic SQL.

There are still limitations however, in that if the name does exist, SQL Server will go on and check other aspects of the statements, such as column names of table objects. DNR was never expanded to variables or temporary (#)/(##) objects, and when inline table-valued functions were added in SQL Server 2000, DNR was not extended to them either since the purpose of DNRs were only to solve the multi-statement batch issue. Not to be confused, inline table-valued functions do not support DNR; multi-statement TVFs do.

The workaround is NOT to use that pattern and instead create the table first and only once.

-- drop if exists
if object_id('tempdb..#t1') is not null
   drop table #t1; 
-- create table ONCE only
select * into #t1 from master..spt_values where 1=0;
-- .... 
-- populate
insert #t1
select * from master..spt_values
-- as quick as drop
truncate table #t1; 
-- populate
insert #t1
select * from master..spt_values
-- as quick as drop
truncate table #t1; 

-- clean up
drop table #t1;