Coder Perfect

Is it required to use # while creating SQL Server transient tables?


Is it required to use # before creating a SQL Server temporary table?


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

Is it necessary to use the # sign for ItemBack1?

If not, what is the point of using # to create temp tables?

Asked by Arun CM

Solution #1

Yes. To create temporary tables, prefix the table name with “#” (hash).

Create the table now if you will not need it later. Temporary tables are similar to regular tables. It is, however, created in tempdb. Furthermore, it can only be accessed within the current session, i.e. For example, if another user tries to access the temp table you established, he will be denied access.

“##” (double-hash produces a “Global” temp table that other sessions can access as well.

Basics of Temporary Tables may be found in

Consider utilizing Table Variables if your table has fewer than 5000 rows and does not contain data types like nvarchar(MAX) or varbinary(MAX).

They are the fastest since they are stored in RAM just like any other variable. They are also kept in tempdb rather than RAM.

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

SELECT column1, 
  FROM table2
 WHERE table2.ID = 7; for further information about Table Variables

Answered by mobiledaemon

Solution #2

The distinction between ItemBack1 and #ItemBack1 is that the first is persistent (permanent), while the second is transitory.

Take a look at your question once more.

Yes, because the table will not be a temporary table without this preceding #, and it will be independent of all sessions and scopes.

Answered by zzlalani

Post is based on