Creating Different Type of Tables in SQL Server 2008
This article demonstrates how to create different types of tables in SQL.
Introduction
In SQL Server we can create different types of table, which can be created for different purposes.
Regular Tables
Regular tables are the tables which holds data of the user for later use. Regular tables are physically stored at the hard drive. DROP command is used to delete these tables from hard drive. If you use truncate command, it will not delete the structure from hard disc, only the rows will be deleted.
Example
Local Temporary Tables
Local Temporary tables are the tables created under the tempdb database. Local Temporary Tables are denoted with the prefix # of table name. The scope of Local Temporary Table is to the current connection of the current user. Once the procedure or session terminates, these tables are automatically destroyed. For example, for creating local temporary table named Employee, this will be specified with the prefix #, like #Employee.
Example
Global Temporary Tables
Global Temporary tables are the tables created under the tempdb database. Global Temporary Tables are denoted with the prefix ## of table name. Global temporary tables are just like simple temporary table but they can be accessed by anyone who logs into the database as long as the creator of the global temporary tables is still logged in. It will be automatically dropped when the session that created it terminates and when other processes which were referencing it, stopped referencing it. Therefore, if the process that created the table closed, then also it remains active if other process is referencing it.
Example
Creation of Duplicate Table from Existing Table
Copying a table with a new name using a select statement. It takes a lots of time to create new empty table and defining all field names again and again. Cloning of table is the best way to do this.
Example
Suppose you create a table named testRecord table shown below.
To create a new table, exactly same in structure to existing one.
Table Variable
Table variables are alternatives of temporary tables hold records for intermediate results of different quires.
Example