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 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:
  1. Create TABLE [dbo].[Employee]  
  2. (  
  3.             [EmpID] [intNULL,  
  4.             [EmpName] [varchar](30) NULL,  
  5.             [EmpSalary] [intNULL  
  6. )  
Now press F5 to execute it.
 
Output
 
Physical-table-in-Sql-Server.jpg
 
You can see this table from: Object Explorer -> Database - Tables
 
Physical-table-storage-in-Sql-Server.jpg
 

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:
  1. create table #table_name  
  2. (  
  3. column_name varchar(20),  
  4. column_no int  
  5. )
Create-Local-Temporary-Tables-in-sqlserver.jpg
 

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: 
  1. create table ##GlobalTemporaryTable  
  2. (  
  3. column_name varchar(20),  
  4. column_no int  
  5. )
Create-Local-Temporary-Tables-in-sqlserver.jpg
 
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.
 
Globall-table-storage-in-Sql-Server.jpg
 
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:
 
Employee-table-in-Sql-Server.jpg
 
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:
  1. SELECT * into studentCopy FROM employee  
Now using a select statement with the table named studentCopy:
  1. SELECT *  FROM studentCopy  
Output
 
Creating-table-using-table-in-Sql-Server.jpg
 

5. Table Variable

 
Table variables are alternatives to temporary tables which store a set of records. The syntax of a table variable is shown below: 
  1. Declare @Employee Table  
  2. (  
  3.             [EmpID] [intNULL,  
  4.             [EmpName] [varchar](30) NULL,  
  5.             [EmpSalary] [intNULL  
  6. )  
Inserting a value into the table variable:
  1. Declare @Employee Table  
  2. (  
  3.             [EmpID] [intNULL,  
  4.             [EmpName] [varchar](30) NULL,  
  5.             [EmpSalary] [intNULL  
  6. )  
  7. INSERT INTO @Employee (EmpID, EmpName, EmpSalary) values (1, 'Rohatash', 1000)  
  8. Select * from @Employee  
Output
 
Table-variable-with-insert-data-in-Sql-Server.jpg
 
Table Variable VS Temporary table
 
To see the difference between a Table Variable and a Temporary Table, click on the following Link: