|
Character
|
TempTable
|
TempVariable
|
|
1. Definition
|
#TempTable
|
@TempVariable
|
|
2. Where we Use
|
For small to medium volumes of data and simple
usage scenarios you should use table variables
Stored Procedures, Triggers, Batches.
|
UDFs, Stored Procedures, Triggers, Batches
|
|
3. Creation
|
CREATE TABLE statement.
SELECT INTO statement.
|
DECLARE statement only.
|
|
4. Table Name
|
Maximum 116 characters.
|
Maximum 128 characters
|
|
5. Index Creation
|
Indexes can be added after the table has been
created.
For large amounts of data for which accessing
by index will be faster then temporary tables are a good option
|
Can only have indexes that are automatically
created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE
statement.
|
|
6. Constraints
|
PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part
of the CREATE TABLE statement, or can be added after the table has been
created. FOREIGN KEY not allowed.
|
PRIMARY KEY, UNIQUE, NULL, CHECK, but they
must be incorporated with the creation of the table in the DECLARE statement.
FOREIGN KEY not allowed.
|
|
7.
Data insertion
|
INSERT statement, including INSERT/EXEC.
SELECT INTO statement.
|
INSERT statement (SQL 2000: cannot use
INSERT/EXEC).
|
|
8.
Post-creation DDL (indexes, columns)
|
Statements are allowed.
|
Statements are not allowed.
|
|
9.
Truncate table
|
Allowed.
|
Not Allowed.
|
|
10.
Destruction
|
Explicitly with DROP TABLE statement.
Automatically when session ends. (Global: also when other sessions have no
statements using table.)
|
Automatically at the end of the batch.
|
|
11.
Rollbacks
|
Affected (Data is rolled back).
|
Not affected (Data not rolled back).
|
|
12.
Statistics
|
Optimizer can create statistics on columns.
Uses actual row count for generation execution plan.
|
Optimizer cannot create any statistics on
columns, so it treats table variable has having 1 record when creating
execution plans.
|
|
13. Transactions
|
Last for the length of the transaction. Uses
more than table variables.
|
Last only for length of update against the
table variable. Uses less than temporary tables.
Table variables don't participate in
transactions, logging or locking.
|
|
14. SELECT INTO
|
You can create a temp table
using SELECT INTO, which can be quicker to write (good for ad-hoc querying)
and may allow you to deal with changing data types over time, since you don't
need to define your temp table structure upfront
|
|
|
15. Storage
|
TempDB
|
TempDB
|
|
16. Bulk Insert
|
Can’t possible.
You cannot use table variable when using
BULK
INSERT |
Possible
|
|
17. Scope
|
1.
#TempTable: is Local
temp tables
Scope: current session of current user
means to the current query window.
And in Nested SPs
These are only available to the SQL Server
session or connection (means single user) that created the tables
These are automatically deleted when the
session that created the tables has been closed.
2.
##TempTable : is Global TempTable
Scope: Global temp tables are available to all SQL
Server sessions or connections (means all the user).
These can be created by any SQL Server
connection user and these are automatically deleted when all the SQL Server
connections have been closed.
Global temporary tables are visible to all
SQL Server connections while Local temporary tables are visible to only
current SQL Server connection.
|
This acts like a variable and exists for a particular
batch of query execution.
TableVariable gets dropped once it comes
out of batch
|
|
18. Recompilation in SP
|
procedure with a temporary table cannot be pre-compiled
|
execution plan of procedures with table variables can be
statically compiled in advance.
Pre-compiling a script gives a major advantage to its speed
of execution.
This advantage can be dramatic for long procedures, where
recompilation can be too pricy.
|
|
19. Usage in INSERT/EXEC
statements
|
|
table variables exist only in the same scope as variables. Opposite
to the temporary tables, they are not visible in inner stored procedures and
in exec(string) statements. Also, they cannot be used in an insert/exec
statement.
|
Similarities with TempTable & TempVariable:
- Instantiated in tempdb
- Clustered indexes can be created on table variables and temporary
tables
- Both are logged in the transaction log
- Just as with temp and regular tables, users can perform all Data
Modification Language (DML) queries against a table variable:
SELECT, INSERT, UPDATE, and DELETE.
No comments:
Post a Comment