Creating Various Types of Tables in SQL Server

Introduction

In this article, we will learn about 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.

We 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 storedin 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,
      NULL,
    [EmpSalary] [int] NULL
)

Now press F5 to execute it.

Output

Physical-table-in-Sql-Server

You can see this table fromObject Explorer -> Database - Tables

Physical-table-storage-in-Sql-Server

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 a 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
)

Command completed successfully

3. Global temporary tables

Global temporary tablesare also stored in tempdb. Global temporary tables are temporary tables that are available to all sessions and all users. They have dropped automatically when the last session using the temporary table has been 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
)

Creating global temporary

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

Temporary tables VS Regular table

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

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

4. Creation of a 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

Now we want to create a table that 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

Creating-table-using-table-in-Sql-Server

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.

Declare @Employee Table
(
        [EmpID] [int] NULL,
          NULL,
        [EmpSalary] [int] NULL
)

Inserting a value into the table variable.

Declare @Employee Table
(
    [EmpID] [int] NULL,
      NULL,
    [EmpSalary] [int] NULL
)
INSERT INTO @Employee (EmpID, EmpName, EmpSalary) VALUES (1, 'Rohatash', 1000)
Select * from @Employee

Output

Conclusion

In this article, we learned about creating Various Types of Tables in SQL Server with its various types.

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