Difference between temporary table and table variable

1. Creation 

Temporary tables are created.

There are 2 types of temp tables
          1. local  - prefix with #
          2. global - prefix with ##

    create table #Temp1
    (
      column1 int not null
    )

   create table ##Temp1
    (
      column1 int not null
    )

Table variable are decarled as opposed to being created. They are name with the sign @ as a prefix.
 
    declare @TableTemp as table
      (
        column1 int not null
     )



2. Scope

It is possible to create temporary local table in separate session with same name.
But it is not possible to create temporary global table in separate session with same name, there will be only one global temporary table in the whole database instance.

Local temporary table are visible throughout the level that created them, across batches, and in all inner levels of the call stack. If you dont drop the temporary table explicitly, it is destroyed when the level that created it terminates. But table variable is only visible to only the batch that declared them and are destroyed automatically at the end of the batch.

select column1 from #Temp1; -- it works from same session
select column1 from @Temp1; -- it works from same batch and same session

exec ('select column1 from #Temp1'); -- it works from same session

exec('select column1 from @Temp1'); -- it wont works even if it is in the same batch and session


3. Physical allocation

Both temporary table and table variable are created in tempdb. Here to be noted that, common table expression (CTE) is not a temp table, so you will not find it in tempdb.

select * from tempdb.sys.objects where name like '%#%'

4. Transaction

Temporary tables are similar to regular tables. Changes applied to a temporary table are undone if the transaction rolls back. But transaction are not undone if the transaction rolls back to a table variable.

5. Index

For temp table, if you define a constraint without naming, SQL server creates constraint with unique name. That's why it is recommended to use unnamed constraint in temporary table. But if you use constraint with a name, constraint will be created in tempdb. In that case, if you try to create a same named constraint from another session, it will be failed because of duplicate constraint name.

For table variable, SQL server doesn't allow explicit naming  of constranint - not even in a single session.

5. Statistics

SQL server maintains distribution statistics (histograms) fro temporary tables but not for table variables.

List all the tables in a database

SELECT * FROM sysobjects WHERE xtype='U'

Here is a list of other object types you can search for as well:

• C: Check constraint
• D: Default constraint
• F: Foreign Key constraint
• L: Log
• P: Stored procedure
• PK: Primary Key constraint
• RF: Replication Filter stored procedure
• S: System table
• TR: Trigger
• U: User table
• UQ: Unique constraint
• V: View
• X: Extended stored procedure

In SQL Server 2012, you can use

SELECT * FROM information_schema.tables where table_type='BASE TABLE'

You can also use

select * from sys.tables

List all the function name for a database

connect to any database.
Use [Database name]


SELECT name AS function_name,type_desc
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%'

MSSQL: List all table size in a database

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN    
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name