Local temporary tables are visible only in 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 disconnects from SQL Server instance .
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.
You can create tables using following syntax.
Table Variable :
Table variables are only visible to the the connection that creates it, are stored in RAM,
and are deleted after the batch or stored procedure ends.
Local temporary tables :
Local temporary tables are only visible to the connection that creates it,
and are deleted after the connection is closed.
Global temporary tables :
Global temporary tables are visible to everyone, and are deleted after the connection that created it is closed.
Tempdb permanent tables :
Tempdb permanent tables are visible to everyone, and are deleted when the server is restarted.
Local Table can not be shared between multiple users.
Global Table can be shared between multiple users.
Write in comment if you know more differences. I will add this in my blog.
Local temporary tables are deleted after disconnects from SQL Server instance .
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.
You can create tables using following syntax.
Table Variable :
DECLARE @tmp TABLE
Table variables are only visible to the the connection that creates it, are stored in RAM,
and are deleted after the batch or stored procedure ends.
Local temporary tables :
CREATE TABLE #tmp
Local temporary tables are only visible to the connection that creates it,
and are deleted after the connection is closed.
Global temporary tables :
CREATE TABLE ##tmp
Global temporary tables are visible to everyone, and are deleted after the connection that created it is closed.
Tempdb permanent tables :
USE tempdb CREATE TABLE tmp
Tempdb permanent tables are visible to everyone, and are deleted when the server is restarted.
Local Table can not be shared between multiple users.
Global Table can be shared between multiple users.
Write in comment if you know more differences. I will add this in my blog.
How about multiple temp DB's with the same name from different connections?
ReplyDeletethere also available @@ temp variable tables
ReplyDeleteDECLARE @@tmp TABLE(ii INT)
INSERT INTO @@tmp VALUES (8),(9)
SELECT * FROM @@tmp