There are some important differences between temporary tables:
create table #T (…)
And table variables:
declare @T table (…)
- They are created using the “Create table” syntax, preceding the table name with a ’#’ for a local temp table and ‘##’ for a global temp table.
- The allocated storage space within the TempDB database and entered into the TempDB system tables.
- The table’s actual name is the name is was created with, a large number of underscores and a hash value, to prevent object name collisions if two connections create a temp table with the same name.
- Can have a primary key, defaults, constraints and indexes (however the names of these are not hashed, possibly leading to duplicate object errors for constraints and defaults).
- Can’t have triggers.
- Foreign keys are permitted, but are not enforced.
- Have column statistics kept on them. The algorithm for determining when to update is different to permanent tables.
- Exist until they are dropped, or the connection closes.
- Are visible in any child procedures called from the one where the table was created. Are not visible to parent procedures.
- Created with a Declare statement, prefixing the table name with ‘@’, like all other variables.
- Allocated storage space within the TempDB database and entered into the TempDB system tables.
- The table variable’s name within TempDB starts with a #, followed by a hex string.
- Can have a primary key and defaults. May not have constraints or indexes.
- Can’t have triggers or foreign keys.
- Do not have column statistics maintained on them.
- Exist only while they are in scope, as any other variable, and are automatically dropped when they go out of scope.
- Are not visible in any procedures other than the one they were created in.
- Do not take part in transactions. Data modification done to a table variable within a transaction will remain if the transaction is rolled back.
Aside from those differences, which one will give the best performance you’ll ask me. Well, like a lot of things in the SQL world, it depends.
I would say first that, when in doubt, you should try both solutions as the fastest solution is not always the most obvious one.
Also, be sure to note these important points:
- Table variables are MUCH slower in SQL Server 2005 in comparaison to SQL Server 2000 (I don’t know for 2008).
- Creating a primary key or a clustered index on a temporary table is useful only for large datasets (I would say above 50k records). It as no significant impact on small sets of data. In fact, it can lower your execution performance if you’re using stored procedures as clustered indexes creation forces the recompilation of the stored procedure, which can take more resources than the original query.
Other popular SQL Posts :How to insert a file in an image column in SQL Server 2005
How to get the total number of rows in a database
How to remove leading zeros within an SQL Query
How to track the growth of your database