Arun CM Arun CM - 2 years ago 75
SQL Question

Is it necessary to Use # for creating temp table in sql server?

is it necessary to use # before creating a temporary table in sql server ?


SELECT column1, column2, someInt, someVarChar
INTO ItemBack1
FROM table2
WHERE table2.ID = 7

for ItemBack1 is it necessary to use # symbol ????

if not then what is the use of # in creating temp tables ???

pls help me

Answer Source

Yes. You need to prefix the table name with "#" (hash) to create temporary tables.

If you do NOT need the table later, go ahead & create it. Temporary Tables are very much like normal tables. However, it gets created in tempdb. Also, it is only accessible via the current session i.e. For EG: if another user tries to access the temp table created by you, he'll not be able to do so.

"##" (double-hash creates "Global" temp table that can be accessed by other sessions as well.

Refer the below link for the Basics of Temporary Tables:

If the content of your table is less than 5000 rows & does NOT contain data types such as nvarchar(MAX), varbinary(MAX), consider using Table Variables.

They are the fastest as they are just like any other variables which are stored in the RAM. THEY ARE STORED IN TEMPDB AS WELL NOT IN RAM!

 column1 int,
 column2 int,
 someInt int,
 someVarChar nvarchar(50)

SELECT column1, 
  FROM table2
 WHERE table2.ID = 7;

More Info on Table Variables:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download