Temporary Tables vs Table Variables

I see lot of people debating on usage of Temporary Tables & Table Variables. And everyone cites their own definition and examples, and most of them conflicts with each other. So I thought to put the differences & points that are valid and tested:
 

–> Temporary Tables:

1. Syntax: CREATE TABLE #T (..)

2. A Temporary Table or Temp-Table is created on disk in the tempDB system database. The name of this Temp-Table is suffixed with a session-specific ID so that it can be differentiated with other similar named tables created in other sessions. The name is limited to 116 chars.

3. The Scope of this Temp-Table is limited to its session, like a Stored Procedure, or a set of nested Stored Procedures.

4. The Temp-Table gets Dropped automatically when the session ends or the Stored Procedure execution ends or goes out of scope.

5. One of the main benefits of using a #temp table, as opposed to a permanent table, is the reduction in the amount of locking required (since the current user is the only user accessing the table), and also there is much less logging involved.

6. Global Temporary Tables (##) operate much like Local Temporary Tables; they are also created in tempdb and cause less locking and logging than permanent tables. However, they are visible to all the sessions, until the creating session goes out of scope.

7. One can create desired Indexes on Temporary Tables (like permanent tables) and these make use of Statistics, thus resulting in better query plan compared to Table variables.
 

–> Table Variables:

1. Syntax: DECLARE @T TABLE (…)

2. A Table Variable is also created on disk in the tempDB system database. But the name of this Table Variable is generated completely by the SQL engine and it also differs with other similar named tables created in same or other sessions.

3. The Scope of Table Variables is limited to its BATCH only like other variables. Contrary to the temporary tables, they are not visible in nested stored procedures and in EXEC(@SQLstring) statements.

4. The Table Variable gets Dropped automatically when the BATCH ends (after the GO batch separator) or the Stored Procedure or Function execution ends or goes out of scope.

5. A Table Variable is created in memory, this is a myth. They are also treated as Temp-Tables and created in tempdb, but they performs slightly better than Temp-Tables because there is even less locking and logging in a Table Variable.

6. Table variables are the only way you can use DML statements (INSERT, UPDATE, DELETE) on temporary data within a UDF (User Defined Function). You can create a Table Variable within a UDF, and modify the data using one of the DML statements, this is not possible with Temp-Tables.

7. A Table Variable will always have a cardinality of 1, thus statistics are not tracked for them and may result in bad query plan.
 

–> Limitations with Table variables:

8. Table Variables do not participate in TRANSACTIONS and locking.

9. You cannot use a Table Variable in either of the following situations:
a. INSERT @table EXEC spSomeProcedure (Starting in SQL Server 2005, this limitation was removed and table variables can now be used as the destination for INSERT EXEC commands.)
b. SELECT * INTO @table FROM someTable

10. You cannot Truncate a Table Variable.

11. Table Variables cannot be Altered after they have been declared.

12. You cannot explicitly add an index to a Table Variable, however you can create an inline index through a PRIMARY KEY CONSTRAINT, and multiple indexes via UNIQUE CONSTRAINTs.

13. You cannot create a named Constraint on Table Variables. You cannot use a user-defined function (UDF) in a CHECK CONSTRAINT, computed column or DEFAULT CONSTRAINT.

14. You cannot use a user-defined type (UDT) in a column definition.

15. Unlike a #temp table, you cannot DROP a Table Variable when it is no longer necessary, you just need to let it go out of scope.

16. You can’t build the Table Variable inside Dynamic SQL. This is because the rest of the script knows nothing about the temporary objects created within the dynamic SQL. Like other local variables, table variables declared inside of a dynamic SQL block (EXEC or sp_executeSQL) cannot be referenced from outside, and vice-versa. So you would have to write the whole set of statements to create and operate on the table variable, and perform it with a single call to EXEC or sp_executeSQL.

17. Table variables are not visible to the calling procedure in the case of nested Stored Procs. It is possible with temp tables.

18. You cannot insert explicit values into an IDENTITY column of a Table variable (the Table Variables does not support the SET IDENTITY_INSERT ON).
 

–> Now the question is when to use either of them?

– Temporary Tables: When you are dealing with large volume of data sets use Temp-Tables, as you can create Indexes on them and they use Statistics for accurate cardinality estimations, thus providing a better query plan.

– Table Variables: When you are dealing with smaller data sets, use Table Variables, as they would not acquire locks and are Transaction free, and may not be affected by the absence of Indexes and Stats.

Move master Database to another drive – in simple steps

Well, there are times when you want to move your master database from the default location to some other drive. Now this activity cannot be done with the normal ALTER DATABASEstatement with MODIFY FILE option. And you need a spacial handling for this case of master DB.
 

–> Let’s first check the location of master DB:

1 2 3 4

USE master GO   SELECT * FROM sys.database_files

 

–> Now leave SSMS, and open SSCM i.e. SQL Server Configuration Manager. Here select “SQL Server Service”, and Rigth Click on the instance of SQL Server, and choose Properties. Now select the Startup Parameters tab.

Here you will see 3 line items:

1. -d is the path of the master data file.

2. -e is the path of the SQL error log file.

3. -l is the path of the master log file.

So, you need to update the 1st and 3rd ones. As I want to move my files toE:\SystemDatabases\Master\ location, so you just need to replace the existing path with following:

1. master data File:
-dC:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf
-dE:\SystemDatabases\Master\master.mdf

2. master Log File:
-lC:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
-lE:\SystemDatabases\Master\mastlog.ldf
 

–> Now Stop the SQL Server services, by going to: RUN –> services.msc

–> Manually Copy the master.mdf & mastlog.ldf files to the new location

–> Start the SQL Server services.

–> To confirm the new location, just execute following query and check the path:

1 2 3 4

USE master GO   SELECT * FROM sys.database_files

 

Making a Table READ ONLY

Generally in Production environment or in some cases there may be a requirement to make a SQL Server table as a read only. In SQL Server, there are many ways to do this. Some of few techniques are as below,

To demonstrate this, here DB name is VIRENDRATEST and Table name is TBLTEST

CREATE DATABASE [VIRENDRATEST]CONTAINMENT = NONEON PRIMARY( NAME = N’VIRENDRATEST’, FILENAME = N’D:\TestDataBases\VIRENDRATEST.mdf’ , SIZE =3072KB , FILEGROWTH = 1024KB )LOG ON (NAME=N’VIRENDRATEST_log’,FILENAME=N’D:\TestDataBases\VIRENDRATEST_log.ldf’,SIZE=1024KB , FILEGROWTH =10%)GO

 And a table TBLTEST as

CREATE TABLE TBLTEST    ( ID int NULL,Name varchar(50) NULL )
ON [PRIMARY]GO

To making table as a read only, following techniques may be used

1. Trigger – Insert, Update, Delete
2. Put the Table in a Read Only File Group
3. Create a View
4. DENY Object Level Permission
5. Make the Database as Read Only

1)  Trigger – Insert, Update, Delete 

Insert, Update and Delete trigger may be implemented on table as

CREATE TRIGGER TrgReadOnly_TblTest ON TblTestINSTEAD OF INSERT, UPDATE, DELETEASBEGINRAISERROR(‘Table is Read Only’, 16, 1 )ROLLBACK TRANSACTIONEND

2)  Put the Table in a Read Only File Group

We can put Table on a Read only file group.

USE [Master]GO

ALTER DATABASE [VIRENDRATEST] ADD FILEGROUP [READONLYTABLES]GO

ALTER DATABASE [VIRENDRATEST] ADD FILE ( NAME = N’READONLYTABLES’, FILENAME =N’D:\TestDataBases\VIRENDRATEST.ndf’ ,
SIZE= 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [READONLYTABLES]GO

CREATE TABLE TBLTEST( ID int NULL,
  Name varchar(50) NULL )
ON [READONLYTABLES]
GO

ALTER DATABASE [VIRENDRATEST] MODIFY FILEGROUP [READONLYTABLES] READONLY

3) Create a View
The easiest solution for making a table as read only is VIEWs. As per below views creations it will prevent DML operation on table.

Create View VwTBLTEST as
select ID, Name from TBLTESTunion all
select 0, ‘0’ where 1=0 

• 4) DENY Object Level Permission

We can deny user level permissions as

DENY INSERT, UPDATE, DELETE ON TBLTEST TO AnyUserName

DENY INSERT, UPDATE, DELETE ON TBLTEST TO Public

5) Make the Database as Read Only

Making a Database as a Read Only, it will not allow to anyone to perform any DDL or DML operation on Database. (be sure where is it recommended as per your work environment)

USE [Master]GO

ALTER DATABASE [VIRENDRATEST] SET READ_ONLY WITH NO_WAITGO