|
Feature
|
Table Variables
|
Temporary Tables
|
|
Scope
|
Current batch
|
Current session, nested stored procedures. Global: all
sessions.
|
|
Usage
|
UDFs, Stored Procedures, Triggers, Batches.
|
Stored Procedures, Triggers, Batches.
|
|
Creation
|
DECLARE statement only.
|
CREATE TABLE statement.
SELECT INTO statement.
|
|
Table name
|
Maximum 128 characters.
|
Maximum 116 characters.
|
|
Column data types
|
Can use user-defined data types.
Can use XML collections.
|
User-defined data types and XML collections must be in
tempdb to use.
|
|
Collation
|
String columns inherit collation from current database.
|
String columns inherit collation from tempdb database.
|
|
Indexes
|
Can only have indexes that are automatically created with
PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.
|
Indexes can be added after the table has been created.
|
|
Constraints
|
PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be
incorporated with the creation of the table in the DECLARE statement. FOREIGN
KEY not allowed.
|
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.
|
|
Post-creation DDL (indexes, columns)
|
Statements are not allowed.
|
Statements are allowed.
|
|
Data insertion
|
INSERT statement (SQL 2000: cannot use INSERT/EXEC).
|
INSERT statement, including INSERT/EXEC.
SELECT INTO statement.
|
|
Insert explicit values into identity columns (SET
IDENTITY_INSERT).
|
The SET IDENTITY_INSERT statement is not supported.
|
The SET IDENTITY_INSERT statement is supported.
|
|
Truncate table
|
Not allowed.
|
Allowed.
|
|
Destruction
|
Automatically at the end of the batch.
|
Explicitly with DROP TABLE statement. Automatically when
session ends. (Global: also when other sessions have no statements using
table.)
|
|
Transactions
|
Last only for length of update against the table variable.
Uses less than temporary tables.
|
Last for the length of the transaction. Uses more than
table variables.
|
|
Stored procedure recompilations
|
Not applicable.
|
Creating temp table and data inserts cause procedure
recompilations.
|
|
Rollbacks
|
Not affected (Data not rolled back).
|
Affected (Data is rolled back).
|
|
Statistics
|
Optimizer cannot create any statistics on columns, so it
treats table variable has having 1 record when creating execution plans.
|
Optimizer can create statistics on columns. Uses actual
row count for generation execution plan.
|
|
Pass to stored procedures
|
SQL 2008 only, with predefined user-defined table type.
|
Not allowed to pass, but they are still in scope to nested
procedures.
|
|
Explicitly named objects (indexes, constraints).
|
Not allowed.
|
Allowed, but be aware of multi-user issues.
|
|
Dynamic SQL
|
Must declare table variable inside the dynamic SQL.
|
Can use temporary tables created prior to calling the
dynamic sql.
|
Friday, May 25, 2012
Table Variables Vs Temporary Tables
Tuesday, May 22, 2012
Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT
What SCOPE_IDENTITY is
SCOPE_IDENTITY is:
What IDENT_CURRENT is
IDENT_CURRENT is:
What @@IDENTITY is
@@IDENTITY is:
Differences
The differences between them are:
SCOPE_IDENTITY is:
- SCOPE_IDENTITY returns the last IDENTITY value inserted into an IDENTITY column in the same scope.
- SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
- A scope is a module; a Stored Procedure, trigger, function, or batch.
- Thus, two statements are in the same scope if they are in the same Stored Procedure, function, or batch.
- The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.
What IDENT_CURRENT is
IDENT_CURRENT is:
- IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
- IDENT_CURRENT is not limited by scope and session; it is limited to a specified table.
What @@IDENTITY is
@@IDENTITY is:
- @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
- After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement.
- If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.
- If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated.
- The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.
Differences
The differences between them are:
- SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.
- SCOPE_IDENTITY and @@IDENTITY will return the last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope. A scope is a module; a Stored Procedure, trigger, function, or batch.
Monday, April 9, 2012
Magic of the OUTPUT Clause with INSERT, UPDATE, DELETE
SQL Server
2005 has new OUTPUT clause, which is quite useful. OUTPUT clause has accesses
to inserted and deleted tables (virtual tables) just like triggers. OUTPUT
clause can be used to return values to client clause. OUTPUT clause can be used
with INSERT, UPDATE, or DELETE to identify the actual rows affected by these
statements.
OUTPUT
clause can generate table variable, a permanent table, or temporary table. Even
though, @@Identity will still work in SQL Server 2005, however I find OUTPUT
clause very easy and powerful to use. Let us understand OUTPUT clause using
example.
Example 1 : OUTPUT clause into Table with INSERT statement
USE AdventureWorks;GO--------Creating the
table which will store permanent tableCREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))----Creating temp table
to store ovalues of OUTPUT clauseDECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))----Insert values in
real table as well use OUTPUT clause to insert----values
in the temp table.INSERT TestTable (ID, TEXTVal)OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTableVALUES (1,'FirstVal')INSERT TestTable (ID, TEXTVal)OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTableVALUES (2,'SecondVal')----Check the values in
the temp table and real table----The
values in both the tables will be sameSELECT * FROM @TmpTableSELECT * FROM TestTable----Clean up timeDROP TABLE TestTableGOResultSet 1:
ID TextVal
——————— ————————
1 FirstVal
2 SecondVal
ID TextVal
——————— ———————
1 FirstVal
2 SecondVal
Example 2 :
OUTPUT clause with INSERT statement
USE AdventureWorks;GO----Creating the table
which will store permanent tableCREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))----Insert values in
real table as well use OUTPUT clause to insert----values
in the temp table.INSERT TestTable (ID, TEXTVal)OUTPUT Inserted.ID, Inserted.TEXTValVALUES (1,'FirstVal')INSERT TestTable (ID, TEXTVal)OUTPUT Inserted.ID, Inserted.TEXTValVALUES (2,'SecondVal')----Clean up timeDROP TABLE TestTableGOResultSet 2:
ID TextVal
——————— ———————
1 FirstVal
(1 row(s) affected)
ID TextVal
——————— ———————
2 SecondVal
Example 3 :
OUTPUT clause into Table with UPDATE statement
USE AdventureWorks;GO----Creating the table
which will store permanent tableCREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))----Creating temp table
to store ovalues of OUTPUT clauseDECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),ID_Old
INT, TEXTVal_Old VARCHAR(100))----Insert values in
real tableINSERT TestTable (ID, TEXTVal)VALUES (1,'FirstVal')INSERT TestTable (ID, TEXTVal)VALUES (2,'SecondVal')----Update the table and
insert values in temp table using Output clauseUPDATE TestTableSET TEXTVal = 'NewValue'OUTPUT Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTVal INTO @TmpTableWHERE ID IN (1,2)----Check the values in
the temp table and real table----The
values in both the tables will be sameSELECT * FROM @TmpTableSELECT * FROM TestTable----Clean up timeDROP TABLE TestTableGOResultSet 3:
ID_New TextVal_New ID_Old TextVal_Old
——————— ——————— ——————— ———————
1 NewValue 1 FirstVal
2 NewValue 2 SecondVal
ID TextVal
——————— ———————
1 NewValue
2 NewValue
Example 4 : OUTPUT clause into Table with DELETE statement
USE AdventureWorks;GO----Creating the table
which will store permanent tableCREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))----Creating temp table
to store ovalues of OUTPUT clauseDECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))----Insert values in
real tableINSERT TestTable (ID, TEXTVal)VALUES (1,'FirstVal')INSERT TestTable (ID, TEXTVal)VALUES (2,'SecondVal')----Update the table and
insert values in temp table using Output clauseDELETEFROM TestTableOUTPUT
Deleted.ID,
Deleted.TEXTVal INTO @TmpTableWHERE ID IN (1,2)----Check the values in
the temp table and real table----The
values in both the tables will be sameSELECT * FROM @TmpTableSELECT * FROM TestTable----Clean up timeDROP TABLE TestTableGOResultSet 4:
ID TextVal
——————— ———————
1 FirstVal
2 SecondVal
ID TextVal
——————— ———————
If you run all the above four example, you will find that OUTPUT clause is very useful.
Subscribe to:
Posts (Atom)