SQL Server tables what is the difference between and

Question

In SQL Server  what is the difference between a   table  a   table and a    table

User · Answer

CREATE TABLE  t   Creates a table that is only visible on and during that CONNECTION the same user who creates another connection will not be able to see table  t from the other connection   CREATE TABLE   t   Creates a temporary table visible to other connections  But the table is dropped when the creating connection is ended

User · Answer

and    tables are actual tables represented in the temp database   These tables can have indexes and statistics  and can be accessed across sprocs in a session  in the case of a global temp table  it is available across sessions    The  table is a table variable   For more  http   www sqlteam com article temporary-tables

User · Answer

if you need a unique global temp table  create your own with a Uniqueidentifier Prefix Suffix and drop post execution if an if object id       The only drawback is using Dynamic sql and need to drop explicitly

User · Answer

table refers to a local  visible to only the user who created it  temporary table     table refers to a global  visible to all users  temporary table    variableName refers to a variable which can hold values depending on its type

User · Answer

I would focus on the differences between  table and  table     table is a global temporary table and for the record in over 10 years of using SQL Server I have yet to come across a valid use case   I m sure that some exist but the nature of the object makes it highly unusable IMHO   The response to  whiner by  marc s is absolutely true  it is a prevalent myth that table variables always live in memory   It is actually quite common for a table variable to go to disk and operate just like a temp table   Anyway I suggest reading up on the set of differences by following the links pointed out by  Astander   Most of the difference involve limitations on what you can t do with  table variables

User · Answer

Have a look at   Temporary Tables vs  Table Variables and Their Effect on SQL Server Performance Differences between SQL Server temporary tables and table variables Temp Tables and Table Variables  When To Use What And Why

[sql-server] SQL Server tables: what is the difference between @, # and ##?

Examples related to sql-server