Temp table in sql server

Question

what is differnt between #temp, ##temp and @temp in sql server , where temp is table name in sql server?

Pankaj Kumar Choudhary · Answer

Local Temporary Tables: Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them. Local temp tables are only available to the current connection for the user. Names of Local temp tables starts with (“#”) hash sign. Global Temporary Tables Global temporary tables are temporary tables that are available to all sessions and all users. Once this table has been created by a connection, like a permanent table then it is available to any user by any connection. They are dropped automatically when the last session using the temporary table has completed. Names of global temp tables start with (“##”) double hash sign. Temp Variables: Temp Variables are also used for holding the data fora temporary time just like Temp tables. Temp variables are created using “DECLARE” statements and are assigned values by using either a SET or SELECT command. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. This acts like a variable and exists for a particular batch of query executions. It gets dropped once it comes out of batch. Temp variables are also created in the Tempdb database but not the memory.

Anil Jha · Answer

Please check below link that explains well. https://stackoverflow.com/questions/2219714/sql-server-tables-what-is-the-difference-between-and

Gokhul Varman · Answer

Please refer the link more detailed explnation:https://www.tutorialspoint.com/sql/sql-temporary-tables.htm

Puneet Kankar · Answer

hi friend, Read this article and watch this vedio you all doubt are clear. http://www.dotnettricks.com/learn/sqlserver/difference-between-cte-and-temp-table-and-table-variable https://www.youtube.com/watch?v=oGuS1rdfaMI

Temp table in sql server

Answers (4)