Creating Various Types of Tables in SQL Server 2012

In this article, I am going to give a quick overview of the various Types of tables in SQL Server. In SQL we can create various types of tables (User table, Temporary table, Global table etc.) which can be used for various purposes in SQL Server. I will try to highlight all the major tables in this article. So let's have a look at a practical example of how to create various types of tables in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio. 

The following are the various types of tables in SQL Server.

1. User Tables (Regular Tables)

Regular tables are the most important tables. They contain data for later use. Regular tables are stored in the hard drive. If you want to delete these tables from hard drive, you can use a drop command in SQL Server. The following is a sample of creating a regular table:

Create TABLE [dbo].[Employee]

(

            [EmpID] [int] NULL,

            [EmpName] [varchar](30) NULL,

            [EmpSalary] [int] NULL
)

 

Now press F5 to execute it.

 

Output

 

You can see this table from: Object Explorer -> Database - Tables

2. Local Temporary Tables 

Local temporary tables are the tables stored in tempdb. Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session. They are specified with the prefix #,

For example #table_name and these temp tables can be created with the same name in multiple windows.

Creating local temporary table

The following is a sample of creating a local temporary table:

create table #table_name

(

column_name varchar(20),

column_no int

)

 

3. Global Temporary Tables  

Global temporary tables are also stored in tempdb. Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. They are specified with the prefix #, for example ##table_name.

Creating Global Temporary Table

The following is a sample of creating a Global Temporary table: 

create table ##GlobalTemporaryTable

(

column_name varchar(20),

column_no int

)

 

 

Both tables are stored in the tempdb system database.

 

Storage Location of a Temporary Table

Temporary tables are stored inside the Temporary Folder of tempdb. Whenever we create a temporary table, it goes to the Temporary folder of the tempdb database. tempdb -> temporary tables.

 

Temporary Tables VS Regular Table

A Temporary table differs in the following two ways from regular tables:

1. Each temporary table is implicitly dropped by the system.
2. Each temporary table is stored in the tempdb system database. The table variable doesn't.

4. Creation of Table with the Help of Another Table

Suppose you create a table named employee table (like the above table). The following is the sample data for the employee Table:

Now we want to create a table which is exactly the copy of a given table; then we can also use the following SQL Query:

SELECT * into studentCopy FROM employee

Now using a select statement with the table named studentCopy:

SELECT *  FROM studentCopy

Output

5. Table Variable

Table variables are alternatives of temporary tables which store a set of records. The syntax of a table variable is shown below:

Declare @Employee Table

(

            [EmpID] [int] NULL,

            [EmpName] [varchar](30) NULL,

            [EmpSalary] [int] NULL

)

 

Inserting a value into the table variable:

Declare @Employee Table

(

            [EmpID] [int] NULL,

            [EmpName] [varchar](30) NULL,

            [EmpSalary] [int] NULL

)

INSERT INTO @Employee (EmpID, EmpName, EmpSalary) values (1, 'Rohatash', 1000)

Select * from @Employee

 

Output

Table Variable VS Temporary table

To see the difference between a Table Variable and a Temporary Table, click on the following Link:

http://www.c-sharpcorner.com/Forums/Thread/194155/table-variable-vs-temporary-table.aspx