Creating Different Type of Tables in SQL Server 2008
This article demonstrates how to create different types of tables in SQL.
In SQL Server we can create different types of
table, which can be created for different purposes.
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.
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
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.
Creation of Duplicate Table from Existing
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.
Suppose you create a table named testRecord
table shown below.
To create a new table,
exactly same in structure to existing one.
Table variables are alternatives of temporary tables
hold records for
intermediate results of different quires.