Category Archives: Differences

Comparative Analysis of Temporary Table and Table Variable based on the aspects like Syntax, Storage Location and Transactions in Sql Server

February 10, 2014Differences, Sql ServerTable Variable Syntax, Table Variables are Created in TempDB, Table Variables Doesn't Participate in Transaction, Table Variables Doesn't Support ALTER, Table Variables Doesn't Support DDL, Temporary Table Syntax, Temporary Tables are Created in TempDB, Temporary Tables Participate in TransactionsBasavaraj Biradar

This is the second article in the series of articles on Difference Between Temporary Table and Table Variable. This article provides the Comparative Analysis of Temporary Table and Table Variable based on the aspects like Syntax, Storage Location and Transactions with extensive list of examples.

Below is the complete list of articles in this series:

Comparative Analysis of Temporary Table and Table Variable based on the aspects like Syntax, Storage Location and Transactions

1. SYNTAX

TEMPORARY TABLE

Demo 1: Below is the sample example of Creating a Temporary Table, Inserting records into it, retrieving the rows from it and then finally dropping the created Temporary Table.

-- Create Temporary Table
CREATE TABLE #Customer ( Id INT, Name VARCHAR(50))
--Insert Two records
INSERT INTO #Customer VALUES(1,'Basavaraj') 
INSERT INTO #Customer VALUES(2,'Kalpana')
--Reterive the records
SELECT * FROM #Customer
--DROP Temporary Table
DROP TABLE #Customer
GO

RESULT:

Demo 2: Temporary Table structure can be changed after it’s creation.Below script creates a Temporary Table #Customer and then after it’s creation adds Address column to it

--Create Temporary Table
CREATE TABLE #Customer ( Id INT, Name VARCHAR(50))
--Add Address Column to the #Customer
ALTER TABLE #Customer ADD Address VARCHAR(400)
GO

TABLE VARIABLE

Demo 1: Below is the sample example of Declaring a Table Variable, Inserting records into it and retrieving the rows from it. Table Variable doesn’t support DDL statements like ALTER, CREATE, DROP etc, implies we can’t modify the structure of Table variable nor we can drop it explicitly.

-- Create Table Variable
DECLARE @Customer TABLE
(
 Id INT,
 Name VARCHAR(50)   
)
--Insert Two records
INSERT INTO @Customer VALUES(1,'Basavaraj') 
INSERT INTO @Customer VALUES(2,'Kalpana')
--Reterive the records
SELECT * FROM @Customer
GO

RESULT:

2. STORAGE LOCATION

One of the most common MYTH about Temporary Table & Table Variable is that: Temporary Tables are created in TempDB and Table Variables are created In-Memory. Fact is that both are created in TempDB, below Demos prove this reality.

Table Variable

Demo 1: Sys.Tables can be used to prove that Table Variables are created in TempDB.

USE MASTER
GO
--Below query lists all the Tables in TempDB
SELECT * From TEMPDB.sys.tables
GO
--Declare a Table Variable
DECLARE @TableVariableLocationDemo TABLE(Name VARCHAR(50))
--Verify whether Table Variable is created in TempDB
SELECT * From TEMPDB.sys.tables
GO

RESULT:

From the above result it is clear that the Table Variables are created in TempDb as we are seeing an entry getting into the Sys.Tables of TempDB.

Demo 2: In this demo we will see that the data in the Table Variable is stored in the data pages of the TempDB data file. To prove this I will be using some of the Sql Server Undocumented and Unsupported Sql Server features, so use at your own risk.

USE MASTER
GO
--Create Table Variable and insert one record in to it
DECLARE @TableVariable TABLE (Name VARCHAR(50)) 
INSERT INTO  @TableVariable VALUES ('Basavaraj Biradar')
--Get the Physical location of the row in the Table Variable
DECLARE @file_id INT, @page_id INT
SELECT @file_id = CAST(CONVERT(binary (2), REVERSE(
			SUBSTRING(%%physloc%%, 5, 2))) AS INT),
	   @page_id = CAST(CONVERT(binary (4), REVERSE(
			SUBSTRING (%%physloc%%, 1, 4))) AS INT)
FROM @TableVariable
--Check whether the Physical location of the Table Variable 
--Row is in the TEMPDB database by looking into the Page dump 
DBCC TRACEON(3604)
--Check FileId and Page ID in TEMPDB
DBCC PAGE('TEMPDB', @file_id, @page_id, 3)
DBCC TRACEOFF(3604)

RESULT:

From the above result it is clear that the physical location of the Table Variable row is in the TempDB database’s data file.

The Undocumented DBCC PAGE statements first parameter passed in the above script is TempDB, the second parameter is the physical file_id of the Table variable row and third parameter is the page_id of the Table Variable row within the data file. Fourth parameter is the level of detailed information required in the output.

Temporary Table

Demo 1: Sys.Tables can be used to prove that Temporary Tables are created in TempDB.

USE MASTER
GO
--Below query lists all the Tables in TempDB
SELECT * From TEMPDB.sys.tables
GO
--Create a Temporary Table
CREATE TABLE #TemporaryTableLocationDemo(Name VARCHAR(50))
--Verify whether Temporary Table is created in TempDB
SELECT * From TEMPDB.sys.tables
GO

RESULT:

Demo 2: In this demo we will see that the data in the Temporary Table is stored in the data page of the TempDB data file. To prove this I will be using some of the Sql Server Undocumented and Unsupported Sql Server features, so use at your own risk.

USE MASTER
GO
--Create Temporary Table and insert one record in it
Create table #TempTable (Name VARCHAR(50)) 
INSERT INTO  #TempTable VALUES ('Basavaraj Biradar')
--Get the Physical location of the row in the Temporary Table
DECLARE @file_id INT, @page_id INT
SELECT @file_id = CAST(CONVERT(binary (2), REVERSE(
			SUBSTRING(%%physloc%%, 5, 2))) AS INT),
	   @page_id = CAST(CONVERT(binary (4), REVERSE(
			SUBSTRING (%%physloc%%, 1, 4))) AS INT)
FROM #TempTable
--Check whether the Physical location of the Temporary Table 
--Row is in the TEMPDB database by looking into the Page dump 
DBCC TRACEON(3604)
--Check FileId and Page ID in TEMPDB
DBCC PAGE('TEMPDB', @file_id, @page_id, 3)
DBCC TRACEOFF(3604)

RESULT:

From the above result it is clear that the physical location of the Temporary Table row is also in the TempDB database’s data file.

3. TRANSACTION

Table variables doesn’t participate in the explicit transactions defined by the user, where as Temporary Tables honor the Transactions.

Table Variable

Demo 1: Table variables doesn’t participate in the explicit transactions defined by the user.

--Declare Table Variable 
DECLARE @TableVariableTransactionDemo TABLE (Name VARCHAR(50)) 
--Start the Transaction
BEGIN TRAN
   --Insert a Record in the Table Variable
   INSERT INTO  @TableVariableTransactionDemo
   VALUES ('Basavaraj Biradar')
   --Rollback the Transaction
ROLLBACK TRAN
--Verify the records in the Table Variable
SELECT * FROM @TableVariableTransactionDemo
GO

RESULT: ROLLBACK TRAN statement didn’t revert back the record inserted in the Table variable within a Transaction.

Temporary Table

Demo 1: Table variables doesn’t participate in the explicit transactions defined by the user.

--Create Temporary Table 
CREATE TABLE #TemporaryTableTransactionDemo (Name VARCHAR(50)) 
--Start the Transaction
BEGIN TRAN
   --Insert a Record in the Temporary Table
   INSERT INTO  #TemporaryTableTransactionDemo
   VALUES ('Basavaraj Biradar')
  --Rollback the Transaction
ROLLBACK TRAN
--Verify the records in the Temporary Table
SELECT * FROM #TemporaryTableTransactionDemo
GO

RESULT: ROLLBACK TRAN statement reverted back the record which is inserted in the Temporary Table within a Transaction.

You may like to read the other articles in this Series of articles on Difference Between Temporary Table and Table Variable:

Difference Between Temporary Table and Table Variable in Sql Server

February 10, 2014Differences, Sql ServerDifference Between Table Variable and Temporary Table, Difference Between Temporary Table and Table Variable, Myths and Facts about Table Variable and Temporary Table., Sql, Sql Server, Table Variable, Table Variable Vs Temporary Table, Temporary Table, Temporary Table Vs Table VariableBasavaraj Biradar

This is the first article in the series of articles on Difference Between Temporary Table and Table Variable. This article lists out difference between Temporary Table and Table Variable.

Below is the complete list of articles in this series:

Difference Between Temporary Table and Table Variable – Summary

Both Temporary Tables (a.k.a # Tables) and Table Variables (a.k.a @ Tables) in Sql Server provide a mechanism for Temporary holding/storage of the result-set for further processing.

Below table lists out some of the major difference between Temporary Table and Table Variable. Each of these differences are explained in-detail with extensive list of examples in the next articles in this series which are listed above.

1. SYNTAX
Below is the sample example of Creating a Temporary Table, Inserting records into it, retrieving the rows from it and then finally dropping the created Temporary Table. -- Create Temporary Table CREATE TABLE #Customer (Id INT, Name VARCHAR(50)) --Insert Two records INSERT INTO #Customer VALUES(1,'Basavaraj') INSERT INTO #Customer VALUES(2,'Kalpana') --Reterive the records SELECT * FROM #Customer --DROP Temporary Table DROP TABLE #Customer GO	Below is the sample example of Declaring a Table Variable, Inserting records into it and retrieving the rows from it. -- Create Table Variable DECLARE @Customer TABLE ( Id INT, Name VARCHAR(50) ) --Insert Two records INSERT INTO @Customer VALUES(1,'Basavaraj') INSERT INTO @Customer VALUES(2,'Kalpana') --Reterive the records SELECT * FROM @Customer GO RESULT:
2. MODIFYING STRUCTURE
Temporary Table structure can be changed after it’s creation it implies we can use DDL statements ALTER, CREATE, DROP. Below script creates a Temporary Table #Customer, adds Address column to it and finally the Temporary Table is dropped. --Create Temporary Table CREATE TABLE #Customer (Id INT, Name VARCHAR(50)) GO --Add Address Column ALTER TABLE #Customer ADD Address VARCHAR(400) GO --DROP Temporary Table DROP TABLE #Customer GO	Table Variables doesn’t support DDL statements like ALTER, CREATE, DROP etc, implies we can’t modify the structure of Table variable nor we can drop it explicitly.
3. STORAGE LOCATION
One of the most common MYTH about Temporary Table & Table Variable is that: Temporary Tables are created in TempDB and Table Variables are created In-Memory. Fact is that both are created in TempDB, below Demos prove this reality.
4. TRANSACTIONS
Temporary Tables honor the explicit transactions defined by the user.	Table variables doesn’t participate in the explicit transactions defined by the user.
5. USER DEFINED FUNCTION
Temporary Tables are not allowed in User Defined Functions.	Table Variables can be used in User Defined Functions.
6. INDEXES
Temporary table supports adding Indexes explicitly after Temporary Table creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint.	Table Variables doesn’t allow the explicit addition of Indexes after it’s declaration, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined during Table Variable declaration.
7. SCOPE
There are two types of Temporary Tables, one Local Temporary Tables whose name starts with single # sign and other one is Global Temporary Tables whose name starts with two # signs.Scope of the Local Temporary Table is the session in which it is created and they are dropped automatically once the session ends and we can also drop them explicitly. If a Temporary Table is created within a batch, then it can be accessed within the next batch of the same session. Whereas if a Local Temporary Table is created within a stored procedure then it can be accessed in it’s child stored procedures, but it can’t be accessed outside the stored procedure.Scope of Global Temporary Table is not only to the session which created, but they will visible to all other sessions. They can be dropped explicitly or they will get dropped automatically when the session which created it terminates and none of the other sessions are using it.	Scope of the Table variable is the Batch or Stored Procedure in which it is declared. And they can’t be dropped explicitly, they are dropped automatically when batch execution completes or the Stored Procedure execution completes.

The above listed differences are discussed in-detail with extensive list of examples in the below articles:

Differences Between RAISERROR and THROW in Sql Server

June 30, 2013Differences, Exception Handling, Sql Server, Sql Server 2012Difference Between RAISERROR and THROW, Difference Between THROW and RAISERROR, Exception Handling, Exception Handling Enhancements in Sql Server 2012, New Feature in Sql Server 2012, RAISEERROR, RAISERROR, RAISERROR Vs THROW, Sql Server, Sql Server 2005, SQL SERVER 2012, THROW, THROW Vs RAISERROR, TRY CATCHBasavaraj Biradar

Both RAISERROR and THROW statements are used to raise an error in Sql Server. The journey of RAISERROR started from Sql Server 7.0, where as the journey of THROW statement has just began with Sql Server 2012. obviously, Microsoft suggesting us to start using THROW statement instead of RAISERROR. THROW statement seems to be simple and easy to use than RAISERROR.

This is the third article in the series of articles on Exception Handling in Sql Server. Below is the complete list of articles in this series.

Part I: Exception Handling Basics – MUST Read Article
Part II: TRY…CATCH (Introduced in Sql Server 2005)
Part III: RAISERROR Vs THROW (Throw: Introduced in Sql Server 2012)
Part IV: Exception Handling Template

Raiserror Vs Throw

Below table lists-out 10 major difference between RAISERROR and THROW with examples:

RAISERROR	THROW
Version of the Sql Server in which it is introduced?
Introduced in SQL SERVER 7.0. And as per BOL, Microsoft is suggesting to start using THROW statement instead of RAISERROR in New Applications. RAISERROR can’t be used in the Sql Server 2014’s Natively compiled Stored Procedures.	Introduced in SQL SERVER 2012. THROW statement seems to be simple and easy to use than RAISERROR. THROW statement can be used in the Sql Server 2014’s Natively Compiled Stored Procedure.
SYNTAX
RAISERROR ( { error_number \| message \| @local_variable } { ,severity ,state } [ ,argument [ ,...n ] ] ) [ WITH option [ ,...n ] ]	THROW [ { error_number \| @local_variable }, { message \| @local_variable }, { state \| @local_variable } ] [ ; ]
Can re-throw the original exception that invoked the CATCH block?
NO. It always generates new exception and results in the loss of the original exception details. Below example demonstrates this: BEGIN TRY DECLARE @result INT --Generate divide-by-zero error SET @result = 55/0 END TRY BEGIN CATCH --Get the details of the error --that invoked the CATCH block DECLARE @ErMessage NVARCHAR(2048), @ErSeverity INT, @ErState INT SELECT @ErMessage = ERROR_MESSAGE(), @ErSeverity = ERROR_SEVERITY(), @ErState = ERROR_STATE() RAISERROR (@ErMessage, @ErSeverity, @ErState ) END CATCH RESULT: Msg 50000, Level 16, State 1, Line 19 Divide by zero error encountered. NOTE:The actual line number of the code which generated Divided By Zero error here is 4, but the exception message returned by RAISERROR is showiung it as 19. Also the error number corresponding to divide by zero error is 8134 in the SYS.Messages table, but the one returned by RAISERROR is 50000.	YES. To Re-THROW the original exception caught in the TRY Block, we can just specify the THROW statement without any parameters in the CATCH block. Below example demonstrates this: BEGIN TRY DECLARE @result INT --Generate divide-by-zero error SET @result = 55/0 END TRY BEGIN CATCH THROW END CATCH RESULT: Msg 8134, Level 16, State 1, Line 4 Divide by zero error encountered. With above example it is clear that THROW statement is very simple for RE-THROWING the exception. And also it returns correct error number and line number.
Causes the statement batch to be ended?
Example 1: In the below Batch of statements the PRINT statement after RAISERROR statement will be executed. BEGIN PRINT 'BEFORE RAISERROR' RAISERROR('RAISERROR TEST',16,1) PRINT 'AFTER RAISERROR' END RESULT: BEFORE RAISERROR Msg 50000, Level 16, State 1, Line 3 RAISERROR TEST AFTER RAISERROR Example 2: In the below example all the statement’s after RAISERROR statement are executed. BEGIN TRY DECLARE @RESULT INT = 55/0 END TRY BEGIN CATCH PRINT 'BEFORE RAISERROR'; --Get the details of the error --that invoked the CATCH block DECLARE @ErMessage NVARCHAR(2048), @ErSeverity INT, @ErState INT SELECT @ErMessage = ERROR_MESSAGE(), @ErSeverity = ERROR_SEVERITY(), @ErState = ERROR_STATE() RAISERROR (@ErMessage, @ErSeverity, @ErState ) PRINT 'AFTER RAISERROR' END CATCH PRINT 'AFTER CATCH' RESULT: BEFORE RAISERROR Msg 50000, Level 16, State 1, Line 19 Divide by zero error encountered. AFTER RAISERROR AFTER CATCH	Example 1: In the below Batch of statements the PRINT statement after THROW statement will not executed. BEGIN PRINT 'BEFORE THROW'; THROW 50000,'THROW TEST',1 PRINT 'AFTER THROW' END RESULT: BEFORE THROW Msg 50000, Level 16, State 1, Line 3 THROW TEST Example 2: In the below example no PRINT statement’s after THROW statement are executed. BEGIN TRY DECLARE @RESULT INT = 55/0 END TRY BEGIN CATCH PRINT 'BEFORE THROW'; THROW; PRINT 'AFTER THROW' END CATCH PRINT 'AFTER CATCH' RESULT: BEFORE THROW Msg 8134, Level 16, State 1, Line 2 Divide by zero error encountered.
CAN SET SEVERITY LEVEL?
YES. The severity parameter specifies the severity of the exception.	NO. There is no severity parameter. The exception severity is always set to 16. (unless re-throwing in a CATCH block)
Requires preceding statement to end with semicolon (;) statement terminator?
NO.	YES. The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
CAN RAISE SYSTEM ERROR MESSAGE? The SYS.MESSAGES Table will have both system-defined and user-defined messages. Message IDs less than 50000 are system messages.
YES. With RAISERROR we can raise the System Exception. Example: RAISERROR (40655,16,1)RESULT: Msg 40655, Level 16, State 1, Line 1 Database ‘master’ cannot be restored.	NO. With THROW we can’t raise the System Exception. But when it used in CATCH BLOCK it can Re-THROW the system exception.Example: Trying to raise system exception (i.e. exception with ErrorNumber less than 50000). THROW 40655, ‘Database master cannot be restored.’, 1 RESULT: Msg 35100, Level 16, State 10, Line 1 Error number 40655 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647
CAN RAISE user-defined message with message_id greater than 50000 which is not defined in SYS.MESSAGES table?
NO. If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.Example: RAISERROR (60000, 16, 1) RESULT: Msg 18054, Level 16, State 1, Line 1 Error 60000, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage. Now add the Message to SYS.MESSAGES Table by using the below statement: EXEC sys.sp_addmessage 60000, 16, ‘Test User Defined Message’ Now try to Raise the Error: RAISERROR (60000, 16, 1) RESULT: Msg 60000, Level 16, State 1, Line 1 Test User Defined Message	YES. The error_number parameter does not have to be defined in sys.messages.Example: THROW 60000, ‘Test User Defined Message’, 1RESULT: Msg 60000, Level 16, State 1, Line 1 Test User Defined Message
Allows substitution parameters in the message parameter? By using the below statement add a sample test message with parameteres to the SYS.Messages Table: EXEC sp_addmessage 70000,16,‘Message with Parameter 1: %d and Parameter 2:%s’
YES.The msg_str parameter can contain printf formatting styles.Example 1: RAISERROR (70000, 16, 1, 505,‘Basavaraj’ ) RESULT: Msg 70000, Level 16, State 1, Line 1 Message with Parameter 1: 505 and Parameter 2:Basavaraj	NO.The message parameter does not accept printf style formatting.Example 1: THROW 70000, ‘Message with Parameter 1: %d and Parameter 2:%s’, 1, 505,’Basavaraj’ RESULT: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ‘,’. Alternative Way of doing this is: DECLARE @ErrorMsg NVARCHAR(2048) = FORMATMESSAGE(70000, 505, ‘Basavaraj’ ); THROW 70000, @ErrorMsg, 1 Example 2: Message manipulation is not allowed in the THROW statement Below statement will fail THROW 58000,‘String1’ + ‘ String2’,1 RESULT: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ‘+’. We can solve such problems, we can prepare the message prior to the THROW statement and then pass it to throw statement as a variable. Below example illustrates this. DECLARE @message NVARCHAR(2048) SET @message = ‘String1’ + ‘ String2’; THROW 58000, @message, 1 RESULT: Msg 58000, Level 16, State 1, Line 3 String1 String2