Differences Between Table Variables And Temporary Tables

Transactional Differences

Table variables are independent of Transactions. Like other SQL Server variables data is not recorded in transaction logs for them. Thus Rollback or Commit transactions do not affect the data in Table variables.

Temporary tables are dependent of Transactions. They behave like ordinary tables when they are placed in a transaction. Rollback and Commit transactions affect the data in Table variables.

[sql]
SET NOCOUNT ON
IF OBJECT_ID(‘tempdb.dbo.#TempTable1’) IS NOT NULL
DROP TABLE #TempTable1

DECLARE @TableVariable1 TABLE (VALUE VARCHAR(50))
CREATE TABLE #TempTable1 (VALUE VARCHAR(50))

INSERT INTO @TableVariable1(VALUE)
SELECT ‘INSERTED AFTER CREATION’

INSERT INTO #TempTable1(VALUE)
SELECT ‘INSERTED AFTER CREATION’

BEGIN TRAN

INSERT INTO @TableVariable1(VALUE)
SELECT ‘INSERTED IN TRANSACTION’

INSERT INTO #TempTable1(VALUE)
SELECT ‘INSERTED IN TRANSACTION’

DECLARE @TableVariable2 TABLE (VALUE VARCHAR(50))

CREATE TABLE #TempTable2 (VALUE VARCHAR(50))

INSERT INTO @TableVariable2(VALUE)
SELECT ‘INSERTED AFTER CREATION’

INSERT INTO #TempTable2(VALUE)
SELECT ‘INSERTED AFTER CREATION’

DROP TABLE #TempTable1

ROLLBACK TRAN

SELECT ‘@TableVariable1’ AS TableName,* FROM @TableVariable1
SELECT ‘#TempTable1’ AS TableName,* FROM #TempTable1
SELECT ‘@TableVariable2’ AS TableName,* FROM @TableVariable2
SELECT ‘#TempTable2’ AS TableName,* FROM #TempTable2
[/sql]

Output:

TableName       VALUE
--------------- --------------------------------------------------
@TableVariable1 INSERTED AFTER CREATION
@TableVariable1 INSERTED IN TRANSACTION

TableName   VALUE
----------- --------------------------------------------------
#TempTable1 INSERTED AFTER CREATION

TableName       VALUE
--------------- --------------------------------------------------
@TableVariable2 INSERTED AFTER CREATION


Msg 208, Level 16, State 0, Line 37
Invalid object name '#TempTable2'.

From the above output we can come up with the following notes:

  • Data in the temporary tables is directly bound to transactions
    i.e. If the transaction is rolledback then the data inserted/Modified in the transaction is lost
  • Data in the table variables is not bound to transactions.
    i.e. If the transaction is rolledback then the data inserted/Modified in the transaction is not lost.
  • Creation/Deletion of temp tables is also bound to transactions.
    i.e. Temporary tables created in a transaction does not exist further if the transaction is rolled back, Temporary tables dropped in a transaction exist further if the transaction is rolled back.
  • If a Table variable is created in a transaction it exists further even if the transaction is rolled back.

Usage Differences

Temporary Tables cannot be created within a function where as table variables can be created within a function.
[sql]
CREATE FUNCTION dbo.TestFn( @i int)
RETURNS INT
AS
BEGIN
DECLARE @A TABLE ( i INT)

RETURN 1
END
[/sql]
Executes succesfully.

[sql]
CREATE FUNCTION dbo.TestFn( @i int)
RETURNS INT
AS
BEGIN
CREATE TABLE #A( I INT)

RETURN 1
END
[/sql]
Fails with an error:

Msg 2772, Level 16, State 1, Procedure TestFn, Line 5
Cannot access temporary tables from within a function.

Table variables should be referenced with an alias when used in joins etc where as alias is not mandatory for Table Variables

[sql]
CREATE TABLE #Values1 (ID INT,VALUE VARCHAR(100))
CREATE TABLE #Values2 (ID INT,VALUE VARCHAR(100))

INSERT INTO #Values1 (ID, VALUE)
SELECT 1,’A’
UNION
SELECT 2,’B’

INSERT INTO #Values2 (ID, VALUE)
SELECT 1,’A’
UNION
SELECT 3,’C’

SELECT * FROM #Values1 JOIN #Values2
ON #Values1.ID = #Values2.ID
[/sql]
Executes Successfully!!

[sql]
DECLARE @Values1 TABLE (ID INT,VALUE VARCHAR(100))
DECLARE @Values2 TABLE (ID INT,VALUE VARCHAR(100))

INSERT INTO @Values1 (ID, VALUE)
SELECT 1,’A’
UNION
SELECT 2,’B’

INSERT INTO @Values2 (ID, VALUE)
SELECT 1,’A’
UNION
SELECT 3,’C’

SELECT * FROM @Values1 JOIN @Values2
ON @Values1.ID = @Values2.ID
[/sql]
Fails with an error:

Msg 137, Level 16, State 1, Line 15
Must declare the scalar variable “@Values1”.
Msg 137, Level 16, State 1, Line 15
Must declare the scalar variable “@Values2”.

Temporary tables can be dropped explicitly where as dropping table variables is not possible
[sql]
DECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100))
DROP TABLE @TableVaraiabe
[/sql]
Fails with error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘@TableVaraiabe’.

[sql]
CREATE TABLE #TableVaraiabe (ID INT,VALUE VARCHAR(100))
DROP TABLE #TableVaraiabe
[/sql]
Executes Successfully!!

DDL Statements can be applied on temporary tables to add/modify and delete the columns but table variables cannot be altered after they are declaration.
[sql]
SET NOCOUNT ON
IF OBJECT_ID(‘tempdb.dbo.#TempTable1’) IS NOT NULL
DROP TABLE #TempTable1

CREATE TABLE #TempTable1 (VALUE VARCHAR(50))
ALTER TABLE #TempTable1 ADD VALUE1 VARCHAR(50)
[/sql]
Executes Successfully!!

[sql]
DECLARE @TableVariable1 TABLE (VALUE VARCHAR(50))
ALTER TABLE @TableVariable1 ADD VALUE1 VARCHAR(50)
[/sql]
Gives Error!!

Temporary Tables can be truncated where as table variables cannot.

Scope or Lifespan Differences

Scope of the temporary table is the session in which it is created.
Table variables scope is the batch of execution similar to other variables.

[sql]
CREATE TABLE #TableVaraiabe (ID INT,VALUE VARCHAR(100));
SELECT * FROM #TableVaraiabe
GO
SELECT * FROM #TableVaraiabe
[/sql]
Executes Successfully!!

[sql]
DECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100));
SELECT * FROM @TableVaraiabe
GO
SELECT * FROM @TableVaraiabe
[/sql]
The statement after go fails as the table variables scope ends with the first batch.
Error:

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable “@TableVaraiabe”.

In nested procedures temporary tables are visible to the procedures called where as table variables are not.
[sql]
CREATE TABLE #Values1 (ID INT,VALUE VARCHAR(100))

INSERT INTO #Values1 (ID, VALUE)
SELECT 1,’A’
UNION
SELECT 2,’B’

EXEC sp_executesql @statement = N’SELECT * FROM #Values1′
[/sql]
Executes Successfully!!
[sql]
DECLARE @Values1 TABLE (ID INT,VALUE VARCHAR(100))

INSERT INTO @Values1 (ID, VALUE)
SELECT 1,’A’
UNION
SELECT 2,’B’

EXEC sp_executesql @statement = N’SELECT * FROM @Values1′
[/sql]
Fails as the table variable @Values1 is not visible to sp_executesql Procedure.
Error:

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable “@Values1”.

Indexing and Statistics

Indexes and Statistics can be created on temporary table, where as its not possible on table variables.

Pre-Compiling Procedures

Procedures that use table variables can be pre-compliled where are those that use Temporary tables cannot be pre-compiled.

Creation Differences:

Table variables are created in the same way similar to creating other variables.
[sql]
DECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100))
[/sql]

Temporary tables are created in the same way similar to creating permanent tables.
[sql]
DECLARE @TableVaraiabe TABLE (ID INT,VALUE VARCHAR(100))
[/sql]

Temporary tables can be created using SELECT * INTO clause where as its not possible for Table variables.
[sql]
SELECT * INTO #TempTable FROM (
SELECT 1 AS I) AS A
[/sql]
Executes Successfully!!

[sql]
SELECT * INTO @TempTable FROM (
SELECT 1 AS I) AS A
[/sql]
Fails with the error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘@TempTable’.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘AS’.

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

While using transactions in SQL Server sometimes you get the following errors:

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Cause For this error:
There is no transaction in progress but the COMMIT/ROLLBACK TRANSACTION request is issued.

Replication of the scenario:
[sql]
BEGIN TRAN
COMMIT TRAN
COMMIT TRAN
[/sql]

In the above query the first COMMIT TRAN statement has commited the transaction started by BEGIN TRAN, When the second COMMIT TRAN statement is executed the error The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION occurs.

Same is the case for ROLLBACK TRAN in the below query.
[sql]
BEGIN TRAN
ROLLBACK TRAN
ROLLBACK TRAN
[/sql]

Fix For this error:

As a best practice always check for open transactions before issuing COMMIT TRAN or ROLLBACK TRAN statement.
@@TRANCOUNT can be used to find the number of open transactions.

[sql]
IF(@@TRANCOUNT>0)
COMMIT TRAN
[/sql]

[sql]
IF(@@TRANCOUNT>0)
ROLLBACK TRAN
[/sql]

This way the commit/rollback tansaction error can be avoided.

Other Such Scenarios:

[sql]
BEGIN TRAN
COMMIT
COMMIT
[/sql]

[sql]
BEGIN TRAN
COMMIT WORK
COMMIT WORK
[/sql]

[sql]
BEGIN TRAN
ROLLBACK WORK
ROLLBACK WORK
[/sql]

Identify SQL Server Database Backup Version

table.hovertable {
font-family: verdana,arial,sans-serif;
font-size:11px;
color:#333333;
border-width: 1px;
border-color: #999999;
border-collapse: collapse;
}
table.hovertable th {
background-color:#c3dde0;
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}
table.hovertable tr {
background-color:#d4e3e5;
}
table.hovertable td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}

How to Identify SQL Server Database Backup Version.

Following is the query:
[sql]
RESTORE HEADERONLY FROM DISK=’C:\DatabaseBackups\DatabaseBackup.bak’
[/sql]

Specify the correct path of the backup file.

When this query is executed read the values of the columns SoftwareVersionMajor,SoftwareVersionMinor and SoftwareVersionBuild.

The version of the SQL Server backup can be found by concatinating the values in three columns.
version of backup = SoftwareVersionMajor.SoftwareVersionMinor.SoftwareVersionBuild

If the output is
SoftwareVersionMajor = 10
SoftwareVersionMinor = 50
SoftwareVersionBuild = 2500

Then the version of database backup is 10.50.2500

SoftwareVersionMajor also gives an idea of the major version of SQL Server.
It helps to determine if the database backup is of SQL Server 2005 or SQL Server 2008.

Identify Database Backup Version
SoftwareVersionMajor = 9 backup is of SQL Server 2005
SoftwareVersionMajor = 8 backup is of SQL Server 2000
SoftwareVersionMajor = 10 and SoftwareVersionMinor = 0 backup is of SQL Server 2008
SoftwareVersionMajor = 10 and SoftwareVersionMinor = 50 backup is of SQL Server 2008 R2

Related posts:
Verify SQL Server Database Backup
restore higher version database backup on lower version sql server – restore problem
The media family on device is incorrectly formed. SQL Server cannot process this media family Error: 3241

Causes For Network Related Errors in SQL Server

Following are the main reasons for network related errors in SQL Server.

1) Server/Instance name is wrong
Fix: Check for the correct name of the Server/Instance and try to connect again

2) SQL Server Service is not running on the Server Machine.
Fix: Start the SQL Server service on the Server Machine.
Steps to start the SQL Server Service:
– Run -> Services.msc
– RightClick on SQL Server (MSSQLSERVER)
– Click on Start

Start SQL Server Service
Start SQL Server Service

3) SQL Server instance Service is not running on the Server Machine.
Fix: Start the SQL Server instance service on the Server Machine.
Steps to start the SQL Server instance Service:
– Run -> Services.msc
– RightClick on SQL Server (instancename)
– Click on Start

4) SQL Server Browser Service is not running on the Server Machine.
Fix: Start the SQL Server Browser service on the Server Machine.
Steps to start the SQL Server Browser Service:
– Run -> Services.msc
– RightClick on SQL Server Browser
– Click on Start

5) TCP/IP is disabled on the Server Machine.
Fix: Enable TCP/IP
Steps to enable TCP IP On SQL Server
Open SQL Server Configuration Manager
– Allprograms ->( Microsoft SQL Server 2008 R2 (or) Microsoft SQL Server 2008 (or) Microsoft SQL Server 2005 ) -> SQL Server Configuration Manager

Open SQL Server Configuration Manager
Open SQL Server Configuration Manager

– Expand SQL Server Network configuration
– Click on protocols for MSSQLSERVER (If its an instance click on the respective instance name)
– RightClick on TCP/IP and click on enable

Enable TCP IP
Enable TCP IP

6) Firewall does not allow connections to SQL Server Service.
Fix: Add exception to Firewall to allow connections to SQL Server Service.

7) Firewall does not allow connections to SQL Browser Service.
Fix: Add exception to Firewall to allow connections to SQL Browser Service.

8 ) Remote connections are disabled on SQL Server
Fix: Allow remote connections on sql server
Steps:
– Connect to SQL Server on SSMS
– RightClick on Server
– Click On Properties
– In the Server Properties Popup Select Connections
– Check the Allow remote connections to Server and click on OK

Allow Remote Connections to Server
Allow Remote Connections to Server

9) Time out errors due to network latency
Fix: Increase Remote Query Time out
Steps:
– Connect to SQL Server on SSMS
– RightClick on Server
– Click On Properties
– In the Server Properties Popup Select Connections
– Increase the Remote query timeout value and click on OK

Remote Query Time Out
Remote Query Time Out

10) Invalid Credentials
Fix: Fetch the latest creadentials to login into the server or ask the administrator to give your credentials necessary oermissions on the server.

TSQL Code Beautifier

Following link helps you format and beautify SQL Code: http://sqlserverlearner.com/online-tools/tsql-code-formatter

SHRINK Database

Following are the Steps to SHRINK Database in SQL Server.

1) Connect to the SQL Server in SSMS

2) Expand Databases

3) RightClick on the Database to be shrinked, Click on Tasks then on Shrink.

4) Here you will have two options:

Shrink Database
Shrink Files

Using Database option you can shrink the database as a whole
Files option lets you shrink each file associated with the database seperately.

Shrink Database
Shrink Database

5) Select Database

6) Now in the Shrink Database Popup Click OK

Shrink Database Popup
Shrink Database Popup

Why is SQL Server consuming Lot of RAM

Why is SQL Server consuming Lot of RAM

There could be multiple reasons why SQL Server is consuming lot of physical memory (RAM).

Following are the few that I would like to list out:

1) Performance of the server could be bad as the queries that are hitting the sever are not optimal.
You can further fine tune such queries.

2) Maximum and Minimum server memory configuration is not set up properly.
You can set up maximum server memory configuration so that it doesnt eat up whole of the RAM.

Following are the Steps to configure sql server memory allocation:

  • Connect to the SQL Server in SQL Server management studio
  • Right click on the server and click on properties
  • Now the Server Properties window pops up.
    In the Server Properties window Select Memory.
    Now check the values of
    Minimum Server Memory(in MB) and Maximum Server Memory(in MB)

    Configure Maximum And Minimum SQL Server Memory
    Configure Maximum And Minimum SQL Server Memory
  • Adjust these values to opimal memory values as per your SQL Server’s memory requirement.

Note: Be aware that changing the memory configuration of SQL Server might give you very bad results. Hence be cautious before making such changes.
You might also get There is insufficient system memory in resource pool ‘default’ to run this query error if memory configuration is bad.

Verify SQL Server Database Backup

Today I wanted to share on this blog a simple but yet very powerfull SQL Server command RESTORE VERIFYONLY.

Syntax:
[sql]
RESTORE VERIFYONLY FROM DISK = ‘<<location of database backup>>’
[/sql]

This command can be used to check weather your database backup is corrupted.

In order to explain this, I have taken backup of one of my database.
I also made another copy of database backup and I intentionally corrupted it.

DatabaseBackup.BAK is the perfect database backup.
DatabaseBackupCorrupted.BAK is the corrupted database backup.

So First I issued RESTORE VERIFYONLY command on the perferct database backup.
Following is the Query:
[sql]
RESTORE VERIFYONLY FROM DISK = ‘C:\DatabaseBackups\DatabaseBackup.BAK’
[/sql]

Output:
The backup set on file 1 is valid.

There were no errors and this made me confirm that the database backup is perfect.

Now I issued the same command on the corrupted database backup.
[sql]
RESTORE VERIFYONLY FROM DISK = ‘C:\DatabaseBackups\DatabaseBackupCorrupted.BAK’
[/sql]

As we expect the command errored out with the following error.

Output:

Msg 3242, Level 16, State 2, Line 1
The file on device ‘C:\DatabaseBackups\DatabaseBackupCorrupted.BAK’ is not a valid Microsoft Tape Format backup set.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

There this error output confirms me that the database backup is corrupted.

When ever restoration of a database backup is failing we can use the RESTORE VERIFYONLY command on the back up set and verify it.

Note:
You get the error Operating system error 2(The system cannot find the file specified) when the file cannot be found on the file system. In order to fix it give the correct file name and file path.

Msg 3201, Level 16, State 2, Line 1 Cannot open backup device
‘C:\DatabaseBackups\DatabaseBackupCorrupted1.BAK’. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

I got the above error when I issued the following command:
[sql]
RESTORE VERIFYONLY FROM DISK = ‘C:\DatabaseBackup.BAK’
[/sql]
As the file C:\DatabaseBackup.BAK does not exists.

Hope this posts helps!!!

Related post:
The media family on device is incorrectly formed. SQL Server cannot process this media family Error: 3241

How to Call a stored procedure from other procedure

Following are the very basic questions on stored procedures.

Question: How can i call a stored procedure from another stored procedure?

Answer:
Use EXEC statement.
Following syntax to execute a stored procedure:

[sql]
EXEC STOREDPROCEDURENAME
[/sql]
Replace STOREDPROCEDURENAME with the name of the procedure.

You can add this piece of code in another stored procedure where ever required.

If the procedure has parameters, then below is the syntax.
[sql]
EXEC STOREDPROCEDURENAME @parameter1=value1,@parameter2=value2,..
[/sql]
Replace STOREDPROCEDURENAME with the name of the procedure, parameters with the name of parameters and values with values for the parameters.
If you have one procedure in schema1 which should call another procedure in schema2.

Following is the code that creates two stored procedures. One of the procedure calls the other procedure using EXEC.

[sql]
CREATE PROCEDURE Procedure1 –Creating Procedure1
AS
SELECT ‘1’
GO

CREATE PROCEDURE Procedure2 –Creating Procedure2 that calls Procedure1
AS
EXEC Procedure1 –CALLING PROCEDURE Procedure1
GO

EXEC Procedure2 –Executing Procedure2
[/sql]

Question: How can i call a stored procedure from another stored procedure, both of them are from different schemas?

Answer:
USE EXEC Schemaname.procedurename

[sql]
EXEC SchemaName.ProcedureName
[/sql]

Following is the simulation and solution for this question.

[sql]
CREATE SCHEMA Schema1 –Creating Schema1

GO

CREATE SCHEMA Schema2 –Creating Schema2
GO

CREATE PROCEDURE Schema1.Procedure1 –Creating Procedure1 in Schema1
AS
SELECT ‘1’
GO

–Creating Procedure2 in Schema2 to call Procedure1 in Schema1
CREATE PROCEDURE Schema2.Procedure2
AS
EXEC Schema1.Procedure1 –CALLING SCHEMA1 PROCEDURE FROM SCHEMA2 PROCEDURE
GO

–Executing Procedure2 in Schema2
EXEC Schema2.Procedure2
[/sql]

Now if you execute:

[sql]
EXEC Schema2.Procedure2
[/sql]

You can see that it is calling Schema1.Procedure1 and displaying 1 as the result.

Incorrect syntax was encountered while parsing GO.

When you try to execute the code which has multiline comment after GO statement you get the error:

A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.

Following piece of code can be used to reproduce this error:

Try parsing this SQL Code:
[sql]
SELECT ‘A’
GO /*Completed the batch*/
[/sql]

The code looks perfectly fine but when you parse it you get the fatal error shown above. 🙁

Now try parsing this piece of code:
[sql]
SELECT ‘A’
GO –Completed the batch
[/sql]

This code parses successfully. 🙂

You get same error when you parse the below code:
[sql]
SELECT ‘A’ SELECT ‘B’
GO SELECT ‘C’
[/sql]

The code given below works fine, it runs the select statements in the batch for 4 times.
[sql]
SELECT ‘A’ SELECT ‘B’
GO 4
[/sql]

But this code fails while parsing:
[sql]
SELECT ‘A’ SELECT ‘B’
GO 4 /*Execute the batch times%
[/sql]

So in general(not completely sure) SQL Parser is giving error when GO statement has some text following it(except — or a number)

Alternate Fix:

Use single line comments(–) instead of multiline comments(/**/) after the GO statement

This error is logged with microsoft in the following link: Microsoft link