The difference between tables and topic tables

Question

why this code works without problems:

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

Exit

 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 

Exit

 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 tempo tables in this code?

Answer 1

To counter other incorrect answers, the correct way to check the #temp table is

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

---

Here's an interesting article on how to compile a phase and execute a phase using # temp tables.

---

This is the MSDN link for Deferred Name Resolution (DNR). To aid in the assembly and assembly of stored procedures, a Deferred Name Resolution was added to SQL Server 7. Prior to this (Sybase), it would be very difficult to create and use tables inside a package without using a lot of dynamic SQL.

However, there are restrictions that if a name exists SQL Server will continue to work and validate other aspects of the statements, such as the column names of table objects. The DNR never expanded to variables or temporary (#) / (##) objects, and when table-based built-in functions were added to SQL Server 2000, the DNR did not extend to them either since the purpose of the DNR was only for multi-batch solutions. Not to be confused, the built-in table functions do not support DNR; multitasking TVF.

The workaround is NOT to use this template 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;