|Programming in MS SQL Server 2012|
Temporary table is similar to table object in SQL Server. By default, table object is stores in user defined database, yet temporary table is stores in tempdb system database. Local temporary table must define with # sign like # TableName as well as Global temporary table must define with ## signs like ## TableName.
Local temporary table records automatically erase from the memory once complete the particular user defined function or stored procedure execution. Global temporary table records erase from the memory when restart SQL Server services.
If a temporary table is created with a named constraint and the temporary table is created within the scope of a user-defined transaction, only one user at a time can execute the statement that creates the temp table. For example, if a stored procedure creates a temporary table with a named primary key constraint, the stored procedure cannot be executed simultaneously by multiple users.
The following example describes a #Local Temporary Table.
Query Output Screen
* * * * *