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 #Employee.
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 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.
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.