Is It Necessary to Use # for Creating Temp Tables in SQL Server

Is it necessary to use # for creating temp tables in SQL server?

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:
http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in-SQL-Server-2005

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.

DECLARE @ItemBack1 TABLE
(
 column1 int,
 column2 int,
 someInt int,
 someVarChar nvarchar(50)
);

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

More Info on Table Variables:
http://odetocode.com/articles/365.aspx

Temp table with ### (triple) or more hashes

It is a global temp table. It is considering the third # as part of the tablename. If you check the temdb database you can see the table without the session ID. If the temp table is created as local temp table then you can see the particular sessionID will be appended with the temptable name, Since there is no session ID appended with the temp tablename it is a global temp table.

CREATE TABLE ###MyTable
  (
     IntColumn   INT,
     ValueColumn VARCHAR(100)
  ) 

After running the above query

GOTO Server -> Databases -> System Database -> Tempdb -> Temporary Tables

you can find the created Global temptable just like the below image.

SQL Temp DB , Difference Between Temporary tables & Tables

Does creating a table in the table section of TempDB differ from creating a Hash table.Are these Table in the temp DB and Hash table same ?

dbo.temp in TempDB will persist until system reboot.since Tempdb will be recreated from scratch ,after any reboot (using Model database as template)

#temp will persist only for that session

There are also some more differences,which apply to #temp like, you can't create

1.triggers

2.Foreign keys

In summary,dbo.temp in TempDB acts just like normal table

Local and global temporary tables in SQL Server

I find this explanation quite clear (it's pure copy from Technet):

There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

How to create temp table using Create statement in SQL Server?

A temporary table can have 3 kinds, the # is the most used. This is a temp table that only exists in the current session.
An equivalent of this is @, a declared table variable. This has a little less "functions" (like indexes etc) and is also only used for the current session.
The ## is one that is the same as the #, however, the scope is wider, so you can use it within the same session, within other stored procedures.

You can create a temp table in various ways:

declare @table table (id int)
create table #table (id int)
create table ##table (id int)
select * into #table from xyz

How to create Temp table with SELECT * INTO tempTable FROM CTE Query

Sample DDL

create table #Temp
(
    EventID int, 
    EventTitle Varchar(50), 
    EventStartDate DateTime, 
    EventEndDate DatetIme, 
    EventEnumDays int,
    EventStartTime Datetime,
    EventEndTime DateTime, 
    EventRecurring Bit, 
    EventType int
)

---

;WITH Calendar
AS (SELECT /*...*/)

Insert Into #Temp
Select EventID, EventStartDate, EventEndDate, PlannedDate as [EventDates], Cast(PlannedDate As datetime) AS DT, Cast(EventStartTime As time) AS ST,Cast(EventEndTime As time) AS ET, EventTitle
,EventType from Calendar
where (PlannedDate >= GETDATE()) AND ',' + EventEnumDays + ',' like '%,' + cast(datepart(dw, PlannedDate) as char(1)) + ',%'
    or EventEnumDays is null

---

Make sure that the table is deleted after use

If(OBJECT_ID('tempdb..#temp') Is Not Null)
Begin
    Drop Table #Temp
End

Scope of temporary tables in SQL Server

From CREATE TABLE:

Local temporary tables are visible only in the current session

and (more importantly):

If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, the Database Engine must be able to distinguish the tables created by the different users [sic - almost certainly this should say sessions not users]. The Database Engine does this by internally appending a numeric suffix to each local temporary table name.

Which exactly rebuts the point of whoever said that they would be shared.

---

Also, there's no need to DROP TABLE at the end of your procedure (from same link again):

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished

---

Related Topics