Difference between # and ## in Sql Server

# [Temporary Table]

Ø The name of this table starts with #.

Ø Temp table can be accessed within the declared stored procedure.

Ø The scope of temp table does not exist beyond the stored procedure.

Ø Temporary table will be dropped automatically on end of each session.

But it will always be better to drop the table physically using the code

Ø Temp tables can be created with the same name in multiple windows.

Ø Different users can create diff temp tables with the same name.

Ø The tables created will be having a unique id for each session.

Ø The table name will be appended with the number allocated for that session.

Ø It will always be better to create indexes on the temp tables and use that within the stored procedures.

Ø Any procedure with a temporary table cannot be pre-compiled

Code

CREATE TABLE #Temp_TestTable

([TestTableID] [int] NOT NULL,

[FirstCol] [varchar](200) NULL,

[SecondCol] [int] NULL

)

Code

DROP TABLE #Temp_TestTable

## [Global Temporary Table]

Ø The name of this table will be started with ##.

Ø Global temp table can be accessed across the sessions.

Ø You can create the table in stored procedure and can use this across multiple stored procedures.

Ø Global temp tables are created with the same name given by the user.

Ø These are just like simple temporary tables but are available to all sessions and will only be dropped automatically when last session of database will be closed. If single session is active, global temporary tables will remain available.

Code

CREATE TABLE ##GTemp_TestTable

([TestTableID] [int] NOT NULL,

[FirstCol] [varchar](200) NULL,

[SecondCol] [int] NULL

)

Code

DROP TABLE ##GTemp_TestTable