Creating Different Type of Tables in SQL Server 2008

This article demonstrates how to create different types of tables in SQL.
  • 1468

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

CreateTable-in-SQL.jpg

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

LocalTemporary_Table.jpg

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

GlobalTemporary_Table.jpg

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.

testRecord-table.jpg

To create a new table, exactly same in structure to existing one.

testRecordCopy-table.jpg

Table Variable

Table variables are alternatives of temporary tables hold records for intermediate results of different quires.

Example

TableVariable.jpg


© 2013 dotNetheaven. All rights reserved.