Friday, May 25, 2012

Table Variables Vs Temporary Tables

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.

Tuesday, May 22, 2012

Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT

What SCOPE_IDENTITY is

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 table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Creating temp table to store ovalues of OUTPUT clause
DECLARE @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 @TmpTable
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (2,'SecondVal')
----Check the values in the temp table and real table
----The values in both the tables will be same
SELECT * FROM @TmpTable
SELECT * FROM TestTable
----Clean up time
DROP TABLE TestTable
GO

ResultSet 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 table
CREATE 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.TEXTVal
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal
VALUES (2,'SecondVal')
----Clean up time
DROP TABLE TestTable
GO

ResultSet 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 table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Creating temp table to store ovalues of OUTPUT clause
DECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),ID_Old INT, TEXTVal_Old VARCHAR(100))
----Insert values in real table
INSERT TestTable (ID, TEXTVal)
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
VALUES (2,'SecondVal')
----Update the table and insert values in temp table using Output clause
UPDATE TestTable
SET TEXTVal = 'NewValue'
OUTPUT Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTVal INTO @TmpTable
WHERE ID IN (1,2)
----Check the values in the temp table and real table
----The values in both the tables will be same
SELECT * FROM @TmpTable
SELECT * FROM TestTable
----Clean up time
DROP TABLE TestTable
GO

ResultSet 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 table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Creating temp table to store ovalues of OUTPUT clause
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
----Insert values in real table
INSERT TestTable (ID, TEXTVal)
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
VALUES (2,'SecondVal')
----Update the table and insert values in temp table using Output clause
DELETE
FROM
TestTable
OUTPUT Deleted.ID
, Deleted.TEXTVal INTO @TmpTable
WHERE ID IN (1,2)
----Check the values in the temp table and real table
----The values in both the tables will be same
SELECT * FROM @TmpTable
SELECT * FROM TestTable
----Clean up time
DROP TABLE TestTable
GO

ResultSet 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.