Category Archives: Differences

Differences Between Sql Server TEXT and VARCHAR(MAX) Data Type

Differences, Sql Server, , , , , , ,

The objective of this article is to compare the legacy Large OBject (LOB) data type TEXT and the Sql Server 2005 introduced VARCHAR(MAX) LOB data type.

[ALSO READ] Difference Between Sql Server VARCHAR and VARCHAR(MAX)

TEXT VarChar(MAX)
Basic Definition

It is a Non-Unicode large Variable Length character data type, which can store maximum of 2147483647 Non-Unicode characters (i.e. maximum storage capacity is: 2GB).

It is a Non-Unicode large Variable Length character data type, which can store maximum of 2147483647 Non-Unicode characters (i.e. maximum storage capacity is: 2GB).
Version of the Sql Server in which it is introduced?

Text data type was present from the very old versions of Sql Server. If I remember correctly it was present even in Sql Server 6.5 days.

VarChar(Max) data type was introduced in Sql Server 2005.
Which one to Use?

As per MSDN link Microfost is suggesting to avoid using the Text datatype and it will be removed in a future versions of Sql Server.

Varchar(Max) is the suggested data type for storing the large string values instead of Text data type.
In-Row or Out-of-Row Storage?

Data of a Text type column is stored out-of-row in a separate LOB data pages. The row in the table data page will only have a 16 byte pointer to the LOB data page where the actual data is present.

Data of a Varchar(max) type column is stored in-row if it is less than or equal to 8000 byte. If Varchar(max) column value is crossing the 8000 bytes then the Varchar(max) column value is stored in a separate LOB data pages and row will only have a 16 byte pointer to the LOB data page where the actual data is present.

When LOB column value is less than 8000 bytes or available space in the row, then whether LOB column value is stored in-row or out-of-row?

Execute the following script to create a demo database SqlHintsLOBDemo if it doesn’t exists already. In the demo data base it creates a table TextTable with a Text LOB type column LargeString. Finally it inserts 100 records, where LargeString column value in each row is 4000 B characters (i.e. 4000 bytes).

--Create a Demo Database
IF DB_ID('SqlHintsLOBDemo')
     IS NULL
CREATE DATABASE SqlHintsLOBDemo
GO
USE SqlHintsLOBDemo
GO
--Create a table with Text type 
--column
CREATE TABLE dbo.TextTable
(
	Id INT IDENTITY(1,1),
	LargeString TEXT
)
GO
--INSERT 100 records, where  
--LargeString column value in 
--each row is 4000 B characters
-- (i.e. 4000 bytes)
INSERT INTO dbo.TextTable
(LargeString)
VALUES(REPLICATE('B', 4000))
GO 100 --Loop 100 times

[ALSO READ] GO Statement can also be used to excute batch of T-Sql statement multiple times

Execute the following statement to check whether the Text type column value is stored in-row or out-of-row:

SELECT alloc_unit_type_desc, 
 page_count
FROM
 sys.dm_db_index_physical_stats 
   (DB_ID('SqlHintsLOBDemo'),
    OBJECT_ID('dbo.TextTable'),
    NULL, NULL , 'DETAILED')

RESULT:
TextData Typ Page Allocation Column Value Less Than 8000 Bytes

From the above result we can see that even though one row (4 Byte for Integer Id column value + 4000 Bytes for Text type column value) can fit into one 8KB data page, but still as per design Sql Server always stores the Text type column value in the LOB data pages. Whether we are storing 1 byte or 2GB data in a Text type column Sql Server always stores the Text type column value out-of-row in the LOB data pages and the row will have a 16 byte pointer pointing to the LOB data pages where the data is stored.

Execute the following script to create a demo database SqlHintsLOBDemo if it doesn’t exists already. In the demo data base it creates a table VarMaxTable with a VarChar(Max) LOB type column LargeString. Finally it inserts 100 records, where LargeString column value in each row is 4000 B characters (i.e. 4000 bytes).

--Create a Demo Database
IF DB_ID('SqlHintsLOBDemo')
   IS NULL
CREATE DATABASE SqlHintsLOBDemo
GO
USE SqlHintsLOBDemo
GO
--Create a table with a 
--Varchar(Max) type column
CREATE TABLE dbo.VarMaxTable
(
	Id INT IDENTITY(1,1),
	LargeString VARCHAR(MAX)
)
GO
--INSERT 100 records, where
--LargeString column value in 
--each row is 4000 B characters 
--(i.e. 4000 bytes)
INSERT INTO dbo.VarMaxTable
 (LargeString)
VALUES(REPLICATE('B', 4000))
GO 100

Execute the following statement to check whether the VarChar(Max) type column value is stored in-row or out-of-row:

SELECT alloc_unit_type_desc, 
 page_count
FROM
 sys.dm_db_index_physical_stats 
   (DB_ID('SqlHintsLOBDemo'),
    OBJECT_ID('dbo.VarMaxTable'),
    NULL, NULL , 'DETAILED')

RESULT:
VarcharMax Type Page Allocation Column Value Less Than 8000 Bytes

From the above result we can see that LOB VarChar(MAX) type column value is stored in-row. For VarChar(MAX) type column Sql Server by default always tries to store the data in-row. Only if it is exceeding 8000 bytes or available space in the row, then only it stores out-of-row in a LOB data pages and in-row it will have 16 byte pointer to the LOB data pages where actual column value is stored.
When LOB column value is more than 8000 bytes or available space in the row, then whether LOB column value is stored in-row or out-of-row?

Execute the following script to remove the previously inserted records and insert 100 records where LargeString column value in each row is 10,000 B characters (i.e. 10,000 bytes).

--TRUNCATE the table to
--remove all the previously
--Inserted records
TRUNCATE TABLE dbo.TextTable
GO
INSERT INTO dbo.TextTable
 (LargeString)
VALUES(REPLICATE(
 CAST('B' AS VARCHAR(MAX)), 
 10000))
GO 100

Execute the following statement to check whether the Text type column value is stored in-row or out-of-row:

RESULT:
TextData Typ Page Allocation Column Value more Than 8000 Bytes

The above result further re-affirms that: whether we are storing 1 byte or 2GB data in a Text type column Sql Server always stores the Text type column value out-of-row in the LOB data pages and the row will have a 16 byte pointer pointing to the LOB data pages where the data is stored.

Execute the following script to remove the previously inserted records and insert 100 records where LargeString column value in each row is 10,000 B characters (i.e. 10,000 bytes).

--TRUNCATE the table to
--remove all the previously
--Inserted records
TRUNCATE TABLE dbo.VarMaxTable
GO
INSERT INTO dbo.VarMaxTable
 (LargeString)
VALUES(REPLICATE(
 CAST('B' AS VARCHAR(MAX)), 
 10000))
GO 100

Execute the following statement to check whether the VarChar(Max) type column value is stored in-row or out-of-row:

RESULT:
VarcharMax Type Page Allocation Column Value more Than 8000 Bytes

From the above result we can see that LOB VarChar(MAX) type column value is stored out-of-row in a LOB data pages. For VarChar(MAX) type column Sql Server by default always tries to store the data in-row. Only if it is exceeding 8000 bytes or available space in the row, then only it stores out-of-row in a LOB data pages having 16 byte pointer in-row pointing to LOB data pages where actual column value is stored.
Do we have an option to change default In-Row and Out-Of-Row Storage behavior?

As we have already seen above whether we are storing 1 byte or 2GB data in a Text type column Sql Server always stores it out-of-row in the LOB data pages and the row will have a 16 byte pointer pointing to the LOB data pages where the data is stored.

Sql Server provides a mechanism where we can change this default behavior of storing the data out-of-row even when we have a sufficient free space in the row to accommodate the Text type column value, by means of sp_tableoption system stored procedure with the option ‘text in row’.

Execute the below statement to store the Text Type Column value in Row if Text Type column value is less than 7000 bytes or enough space is available in the row.

EXEC sp_tableoption 
 @TableNamePattern =
      'dbo.TextTable',
 @OptionName = 'text in row', 
 @OptionValue = 7000

The @OptionValue parameter value can be:
0/OFF (Default Value): Text type column value is stored out-of-row
ON: Text Type Column value is stored in-row as long as the Text type column value is less than or equal to 256 bytes
integer value from 24 through 7000: specifies the number bytes up to which the text type column value is stored in-row.

Execute the following script to remove the previously inserted records and insert 100 records where LargeString column value in each row is 4,000 ‘B’ characters (i.e. 4,000 bytes).

TRUNCATE TABLE dbo.TextTable
GO
INSERT INTO dbo.TextTable
(LargeString)
VALUES(REPLICATE('B', 4000))
GO 100

Execute the following statement to check whether the Text type column value is stored in-row or out-of-row:

SELECT alloc_unit_type_desc, 
 page_count
FROM
 sys.dm_db_index_physical_stats 
   (DB_ID('SqlHintsLOBDemo'),
    OBJECT_ID('dbo.TextTable'),
    NULL, NULL , 'DETAILED')

RESULT:
Changing Text Data Type Default Storgae Behaviour

From the above result now we can see that the Text column values are stored in-row. So we can use sp_tableoption system stored procedures option ‘text in row’ to change the text data types default storage behavior of always storing out-of-row. With this option we an force text data type column value to store in-row up-to 7000 bytes or till the enough space is available in the row.

Execute the following statement to change back the Text type columns storage behavior to the default behavior where Text type columns values are always stored out-of-row even we have sufficient space in the row.

EXEC sp_tableoption
 @TableNamePattern =
      'dbo.TextTable',
 @OptionName = 'text in row',
 @OptionValue = 'OFF'

As we have already seen above for VarChar(MAX) type column Sql Server by default always tries to store the data in-row. Only if it is exceeding 8000 bytes or available space in the row, then only it stores out-of-row in a LOB data pages and in-row it will have 16 byte pointer to the LOB data pages where actual column value is stored.

Sql Server provides a mechanism where we can change this default behavior of storing the data for VarChar(Max) type column, by means of sp_tableoption system stored procedure with the option ‘large value types out of row’.

Execute the below statement to always store Varchar(Max) column value out-of-Row whether it is 1 byte or 2GB even when enough space is available in the row.

EXEC sp_tableoption 
 @TableNamePattern =
  'dbo.VarMaxTable',
 @OptionName = 
  'large value types out of row',
 @OptionValue = 1

The @OptionValue parameter value can be:
0 (Default) : Varchar(Max) column values are stored in-row as long as the value length is <= 8000 bytes and enough space is available in the row. 1 : VarChar(Max) column values are always stored out-of-row even when enough space is available in the row.

Execute the following script to remove the previously inserted records and insert 100 records where LargeString column value in each row is 4,000 ‘B’ characters (i.e. 4,000 bytes).

TRUNCATE TABLE dbo.VarMaxTable
GO
INSERT INTO dbo.VarMaxTable
 (LargeString)
VALUES(REPLICATE('B', 4000))
GO 100

Execute the following statement to check whether the VarChar(Max) type column value is stored in-row or out-of-row:

SELECT alloc_unit_type_desc, 
 page_count
FROM
 sys.dm_db_index_physical_stats 
   (DB_ID('SqlHintsLOBDemo'),
    OBJECT_ID('dbo.VarMaxTable'),
    NULL, NULL , 'DETAILED')

RESULT:
Changing VarcharMax column Default Storgae Behaviour

From the above result we can see that VarChar(Max) type column values are stored out-of-row even when there was a sufficient space available in the row. So we can use the sp_tableoption system stored procedures option ‘large value types out of row’ to change the Varchar(Max) data type columns default storage behavior.

Execute the following statement to change back the Varchar(Max) type columns storage behavior to the default behavior where Sql Server by default always tries to store the data in-row. Only if it is exceeding 8000 bytes or available space in the row, then only it stores out-of-row in a LOB data pages.

EXEC sp_tableoption 
 @TableNamePattern =
  'dbo.VarMaxTable',
 @OptionName = 
  'large value types out of row',
 @OptionValue = 0
Supported/Unsupported Functionalities
Some of the string functions, operators or the constructs which work on VarChar(Max) type column may not work on the Text type column.
Below are two such example functions, operators or constructs:
1. = Operator on Text type column

SELECT *
FROM TextTable WITH(NOLOCK)
WHERE LargeString = 'test string'

RESULT:

Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the equal to operator.

2. Group by clause on Text type column

SELECT LargeString, COUNT(1)
FROM VarMaxTable WITH(NOLOCK)
GROUP BY LargeString

RESULT:

Msg 306, Level 16, State 2, Line 3
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

From the above examples we can see that we can’t use ‘=’ operator on a Text type column and also the Group By clause on the Text type column.

Some of the string functions, operators or the constructs which doesn’t work on the Text type column, but they do work on VarChar(Max) type column.
Below are two such example functions, operators or constructs:
1. = Operator on VarChar(Max) type column

SELECT *
FROM VarMaxTable WITH(NOLOCK)
WHERE LargeString = 'test string'

RESULT:
Equal to Operator on a VarCharMax type column
2. Group by clause on VarChar(Max) type column

SELECT LargeString, COUNT(1)
FROM VarMaxTable WITH(NOLOCK)
GROUP BY LargeString

RESULT:
Group By Clause on VarCharMax Type Column
From the above examples we can see that we use ‘=’ operator and Group By clause on the VarChar(Max) type column, but not on the Text type column. If data stored in the Varchar(Max) column is a very large strings, then using these functions may lead to performance issues.

System IO Considerations

As we know that the Text type column values are always stored out-of-row in LOB data pages and in-row it will have a 16 byte pointer pointing to the root LOB data page. So if the query doesn’t include the LOB columns then the number of pages required to read to retrieve the data will be less as the column data is out-of-row. But if the query includes the LOB columns, then the number of pages required to retrieve the data will be more.

As we know that the VarChar(Max) type column values are stored out-of-row only if the length of the value to be stored in it is greater than 8000 bytes or there is not enough space in the row, otherwise it will store it in-row. So if most of the values stored in the VarChar(Max) column are large and stored out-of-row, the data retrieval behavior will almost similar to the one that of the Text type column.

But if most of the values stored in VarChar(Max) type columns are small enough to store in-row. Then retrieval of the data where LOB columns are not included requires the more number of data pages to read as the LOB column value is stored in-row in the same data page where the non-LOB column values are stored. But if the select query includes LOB column then it requires less number of pages to read for the data retrieval compared to the Text type columns.

ALSO READ

Difference between Sequence and Identity in Sql Server

Differences, Sql Server, , , , , , , , , , , , ,

Below table lists out the major difference between SEQUENCE and IDENTITY in Sql Server

[ALSO READ] Introduction to Sequence

SEQUENCE IDENTITY
Sequence object is introduced in Sql Server 2012 Identity Column property is introduced in Sql Server 6.0
Sequence is a user-defined database object and as name suggests it generates sequence of numeric values according to the properties with which it is created Identity property is a table column property. It is also used to generate a sequence of numbers according to the properties with which it is created
Sequence object can be shared across multiple tables Identity property is tied to a Table
Sequence object can be used to generate database-wide sequential number across multiple tables. Identity property can be used to generate a sequence numbers at a table level
A sequence is created independently of the tables by using the CREATE SEQUENCE statement Identity property can be specified for a table column in CREATE TABLE or ALTER TABLE statement
Syntax:

CREATE SEQUENCE 
  [schema_name.]sequence_name
[ AS [built_in_integer_type 
    | user-defined_integer_type]]
[ START WITH <constant>]
[ INCREMENT BY <constant>]
[ { MINVALUE [<constant>] } 
    | { NO MINVALUE } ]
[ { MAXVALUE [<constant>] }
    | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [<constant>] } 
   | { NO CACHE } ]
[ ; ]

Where:
Start with: Initial value to start with sequence.
Increment by: Step by which the values will get incremented or decremented.
Minvalue: Minimum value of the sequence.
Maxvalue: Maximum value of the sequence.
Cycle / No Cycle: To recycle the sequence once it reaches to the maximum or minimum (if increment by is a negative number).
Cache / No Cache: To pre-allocate the number of sequences specified by the given value.

 Syntax:

IDENTITY [ (seed , increment) ]

Where:
Seed: Initial value to start with
Increment: Step by which the values will get incremented or decremented each time.
Below is simple example of creating a sequence with Initial value 1 and Increment by 1

CREATE SEQUENCE 
  [DBO].[SequenceExample] AS INT
 START WITH 1
 INCREMENT BY 1
GO
 Below is an example of creating a customer table with identity column Id with initial value as 1 and increment by 1

CREATE TABLE dbo.Customer
( Id INT IDENTITY(1,1),
  Name	NVARCHAR(50) )
GO
We can get the next sequence value by using NEXT VALUE FOR function without needing to insert a record to the table
Example: Getting Next Sequence Value in A SELECT Statement without inserting a record

SELECT (NEXT VALUE FOR
 DBO.SequenceExample) AS SeqValue

RESULT:
Sequence in Sql Server

Only way to generate the next identity value is by inserting a record to the table in which the identity column is defined.
Example: Insert a record to get the next identity value

INSERT INTO dbo.Customer (Name)
VALUES('Basavaraj Biradar')
GO
SELECT SCOPE_IDENTITY()
GO
SELECT * FROM dbo.Customer

RESULT:
Sequence in Sql Server 1

We can use a script like below to get the sequence object’s current value:

SELECT Current_Value 
FROM Sys.Sequences 
WHERE name='SequenceExample'

Sequence current value

We can use a script like below to get the identity columns current value (i.e. last identity value generated as a result of insert):

SELECT IDENT_CURRENT('Customer') 
 AS 'Identity Current value'

Identity Column Current value

Sequence object provides an option to reset the current sequence value as-well as the increment step size

ALTER SEQUENCE 
 dbo.SequenceExample
RESTART WITH 20
INCREMENT BY 2
GO

Sequence reseeding example 2

Column identity property current value can be reseeded but not it’s increment step size
Example: The following script resets the Customer tables current identity value to 20.

DBCC 
CHECKIDENT('Customer', RESEED,20)

Sequence reseeding example 1

Sequence object provides an option to define the maximum sequence value. If it is not specified, by default it takes the maximum value of the Sequence object data type.

Example: Below script creates a sequence object with maximum value as 2

CREATE SEQUENCE
  [dbo].[SequenceMax] AS INT
 START WITH 1
 INCREMENT BY 1
 MAXVALUE 2
GO

Once the Sequence maximum value is reached the request for the next sequence value results in the following error message:
Msg 11728, Level 16, State 1, Line 2
The sequence object ‘SequenceMax’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

 Identity column property doesn’t provide an option to define the maximum identity value, it always to takes maximum value corresponding to the identity columns data type
Sequence object provides an option of automatic restarting of the Sequence values.If during Sequence object creation the CYCLE option is specified, then once the sequence object reaches maximum/minimum value it will restarts from the specified minimum/maximum value.

Example: Create  a sequence object with CYCLE option which starts with 1 and when Sequence max value 2 is reached it will restart with minimum value 1.

CREATE SEQUENCE [dbo].[SeqCycle]
 AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2
CYCLE
GO

Let us check how the Sequence values are recycled by calling the NEXT VALUE FOR function for the 3 times as below:

SELECT (NEXT VALUE FOR
  dbo.SeqCycle) AS SeqValue
GO 3

RESULT:
Sequence Cycle example Sql Server

Identity property doesn’t provides an option for the automatic restarting of the identity values
Sequence object provides sp_sequence_get_range to get multiple sequence numbers at once. Identity column property doesn’t provide an option to get multiple values.
Sequence object provides an option to enable caching, which greatly increases the performance by reducing the disk IO required to generate the next sequence value. CACHE property allows to define cache size, by default caching is disabled.

To Sequence CACHE management and internals you may like to go through the article: Sequence Cache management and Internals

 Identity property doesn’t provide an option to enable/diable the cache management and also to define the cache size.

[ALSO READ] Sequence related articles

Introduction to Sequence
Sequence limitations and restrictions
Sequence Cache management and Internals

[ALSO READ] Differences

Comparative Analysis of Temporary Table and Table Variable based on the aspects like User Defined Functions, Indexes and Scope in Sql Server

Differences, Sql Server, , , , , , ,

This is the third 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 User Defined Functions, Indexes and Scope 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 User Defined Functions, Indexes and Scope

1. USER DEFINED FUNCTION

Temporary Tables are not allowed in User Defined Functions, whereas Table Variables can be used in User Defined Functions.

Table Variable

DEMO 1: User defined Functions allow Table Variables in it.

--Create a function which is using a Table Variable
CREATE FUNCTION TableVariableWithinAFunction()
RETURNS INT
AS
BEGIN
	--Declare Table Variable
	DECLARE @TableVariable TABLE(Name VARCHAR(50)) 
	IF EXISTS(SELECT 1 FROM @TableVariable)
		RETURN 1

	RETURN 0
END
GO
--Call the function
PRINT dbo.TableVariableWithinAFunction()
GO

RESULT:
FUNCTION Table Variable

Temporary Table

DEMO 1: User defined Functions doesn’t allow Temporary Table in it.

CREATE FUNCTION TemporaryTableWithinAFunction()
RETURNS INT
AS
BEGIN
	CREATE TABLE #TempTable (Name VARCHAR(50)) 
	IF EXISTS(SELECT 1 FROM #TempTable)
		RETURN 1

	RETURN 0
END

RESULT:
Msg 2772, Level 16, State 1, Procedure TemporaryTableWithinAFunction, Line 5
Cannot access temporary tables from within a function.
Msg 2772, Level 16, State 1, Procedure TemporaryTableWithinAFunction, Line 6
Cannot access temporary tables from within a function.

2. INDEXES

Table Variables doesn’t allow the explicit addition of Indexes after it’s declaration, the only means is the implicit indexes which are added as a result of the Primary Key or Unique Key constraint defined during Table Variable declaration. On the other hand 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.

Temporary Table

Demo 1: You can add Indexes to the Temporary Table both explicitly and implicitly. Below script creates a Temporary Table #Employee which has Primary Key with Clustered option, it means it will implicitly create a Clustered Index on the Id column. After the creation of the Temporary Table, explicitly adds a Non-Clustered Index IX_#Employee_FirstName on the FirstName column.

--Create Temporary Table
CREATE TABLE #Employee
(
  Id INT PRIMARY KEY CLUSTERED,
  FirstName NVARCHAR(50),
  LastName NVARCHAR(50)
)
--Add Non-Clustered Index
CREATE NONCLUSTERED INDEX IX_#Employee_FirstName 
      ON #Employee(FirstName)
GO

Table Variable

You can’t add Indexes explicitly to the Table Variable. But as Table variables support Primary and Unique Key constraints, so specifying these constraints during Table variable declaration internally creates Indexes on the Table Variable columns. But as we know both Primary and Unique Key doesn’t allow duplicate values in it (i.e. enforces unique constraint), so they provide means to create only Implicit Clustered or Non-Clustered Unique indexes.  It implies Tables Variables doesn’t support adding a  Non-Unique Non-Clustered indexes either implicitly or explicitly.

Demo 1:  Below script creates a Table variable @Employee which has Primary Key with NonClustered option, this means it will implicitly create a Unique Non-Clustered Index on the Id column. And it also has Unique Clustered Constraint on the Name column, so it will create an Unique Clustered Index on the Name column.

DECLARE @Employee TABLE
(
  Id INT PRIMARY KEY NONCLUSTERED,
  Name NVARCHAR(50) UNIQUE CLUSTERED(Name)
)

3. SCOPE

Table Variable

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.

Temporary Table

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.

Below DEMOS explain the above explained scoping aspect of the Table Variable and Temporary Tables with extensive list of examples.

DEMO 1: This example demonstrates that a Table variables Scope is the Batch in which it is created, they are not accessible in the same session’s next batch of statement.

--Declare Table Variable 
DECLARE @TableVariableScopeBatchDemo TABLE (Name VARCHAR(50)) 
--Insert a Record in the Table Variable
INSERT INTO  @TableVariableScopeBatchDemo
VALUES ('Basavaraj Biradar')
--Try to Access Table Variable in the batch scope
SELECT * FROM @TableVariableScopeBatchDemo
GO -- GO Statement Signals the END of the BATCH
--Try to Access the Table Variable outside the batch 
--but within the same session scope
SELECT * FROM @TableVariableScopeBatchDemo
GO

RESULT:
Scope Of Table Variables Is The Batch1

DEMO 2: This example demonstrates that a Local Temporary Table scope is the session in which it is created. Here a local Temporary Table created within the first batch is accessible in the same session’s next batch of statements.

--Create Temporary Table 
CREATE TABLE #TemporaryTableScopeBatchDemo (Name VARCHAR(50)) 
--Insert a Record in the Temporary Table
INSERT INTO  #TemporaryTableScopeBatchDemo 
VALUES ('Basavaraj Biradar')
--Try to Access Temporary Table in the batch scope
SELECT * FROM #TemporaryTableScopeBatchDemo
GO -- GO Statement Signals the END of the BATCH
--Try to Access the Temporary Table outside the batch 
--but within the same session scope
SELECT * FROM #TemporaryTableScopeBatchDemo
GO

Scope Of Temporary Table Is The Session

DEMO 3: This example demonstrates that a Local Temporary Table created within a stored procedure can’t be accessed outside the stored procedure scope.

CREATE PROCEDURE dbo.SomeSPForTempTableDemo
AS
BEGIN
 --Create Temporary Table 
 CREATE TABLE #TemporaryTableScopeSPDemo(Name VARCHAR(50)) 
 --Insert a Record in the Temporary Table
 INSERT INTO #TemporaryTableScopeSPDemo
 VALUES ('Basavaraj Biradar')
 --Try to Access Temporary Table
 SELECT * FROM #TemporaryTableScopeSPDemo
END
GO
--Execute the Stored Procedure
EXEC dbo.SomeSPForTempTableDemo
--Try to Access the Temporary Table created during the SP 
--SomeSPForTempTableDemo execution by previous statement
SELECT * FROM #TemporaryTableScopeSPDemo
GO

Scope of Temporary Table Created within Stored Procedure

DEMO 4: This example demonstrates that a Table Variable created within a stored procedure can’t be accessed outside the stored procedure scope.

CREATE PROCEDURE dbo.SomeSPForTableVariableDemo
AS
BEGIN
 --Declare Table Variable
 DECLARE @TableVariableScopeSPDemo TABLE(Name VARCHAR(50)) 
 --Insert a Record in the Temporary Table
 INSERT INTO @TableVariableScopeSPDemo
 VALUES ('Basavaraj Biradar')
 --Try to Access Table Variable
 SELECT * FROM @TableVariableScopeSPDemo
END
GO

Try to execute the above stored procedure which creates a Table Variable and a statement accessing the Table Variable immediately after the Stored Procedure within the same batch as shown in the below script. This results in a compilation error.

--Execute the Stored Procedure
EXEC dbo.SomeSPForTableVariableDemo
--Try to Access the Table Variable created during the SP
--SomeSPForTableVariableDemo execution by previous statement
SELECT * FROM @TableVariableScopeSPDemo
GO

Scope of Table Variable Created within Stored Procedure1

Try to execute the above stored procedure which creates a Table Variable and a statement accessing the Table Variable created in the SP in separate batches one after another but in the same session as shown below. In such scenario the SP execution succeeds, but the statement trying to access the Table variable outside SP in the next batch errors-out.

--Execute the Stored Procedure
EXEC dbo.SomeSPForTableVariableDemo
GO
--Try to Access the Table Variable created during the SP 
--SomeSPForTempTableDemo execution by previous statement
 SELECT * FROM @TableVariableScopeSPDemo
GO

Scope of Table Variable Created within Stored Procedure2

DEMO 5: This example demonstrates that a Local Temporary Table created in the Parent Stored Procedure can be accessed by it’s child stored procedures.

-------------Create ParentSP----------------
CREATE PROCEDURE dbo.ParentSPForTempTableDemo
AS
BEGIN
 --Create Temporary Table 
 CREATE TABLE #TemporaryTableScopeSPDemo(Name VARCHAR(50)) 
 --Insert a Record in the Temporary Table
 INSERT INTO #TemporaryTableScopeSPDemo
 VALUES ('Basavaraj Biradar')
 --CALL Child SP
 EXEC dbo.ChildSPForTempTableDemo
END
GO
-------------Create ChildSP----------------
CREATE PROCEDURE dbo.ChildSPForTempTableDemo
AS
BEGIN
 --Try to Access Temp Table created in the Parent SP
 SELECT * FROM #TemporaryTableScopeSPDemo
END
GO
----------Execute the ParentSP----------
EXEC dbo.ParentSPForTempTableDemo

RESULT:
Scope of Temporary Table in Nested Stored Procedure

DEMO 6: This example demonstrates that a Table Variable Created in the Parent SP is not accessible by it’s child stored procedures. Basically, Sql Server doesn’t allow us to Create a Child Stored Procedure accessing the Table Variable Created in the Parent SP.

-------------Create ParentSP----------------
CREATE PROCEDURE dbo.ParentSPForTableVariableDemo
AS
BEGIN
 --Create Temporary Table 
 CREATE TABLE @TableVariableScopeSPDemo(Name VARCHAR(50)) 
 --Insert a Record in the Temporary Table
 INSERT INTO @TableVariableScopeSPDemo
 VALUES ('Basavaraj Biradar')
 --CALL Child SP
 EXEC dbo.ChildSPForTableVariableDemo
END
GO
-------------Create ChildSP----------------
CREATE PROCEDURE dbo.ChildSPForTableVariableDemo
AS
BEGIN
 --Try to Access the Table variable Created in Parent SP
 SELECT * FROM @TableVariableScopeSPDemo
END
GO

RESULT:
Scope of Table Variable in Nested Stored Procedure

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