SQLSERVER: 04/22/13

Monday, 22 April 2013

Different Types Of Tables In Sql Server

How to create permanent table in MicroSoft SQL Server database?

CREATE TABLE dbo.Table_Name
(
Column_Name1 DATATYPE,
Column_Name2 DATATYPE)
)

How to create temporary table in MicroSoft SQL Server database?

In Microsoft SQL Server database, temporary tables are available in two types. Based on requirement user can create either one.
Following are two types of temporary tables available in Microsoft SQL Server database.

Local Temporary Table
Global Temporary Table

Local Temporary Table
How to create Local temporary table object in Microsoft SQL Server Database?
Using single pound sign (#) along with the name of the table, Local temporary table can be created. As shown in below example how to create a local temporary table.

Example For Temporary Table:

CREATE TABLE #emp_hist
(
empno INT,
empname VARCHAR(40)
)

To whom Local temporary table object are accessible in Microsoft SQL Server database?
Local temporary table object are accessible only in current session which created it, once session got terminated the Local temporary table object and its records are got deleted. So same user can’t access it in different session. Life span of Local temporary table object is equal to the life span of session which created it.

What is the life span for Local temporary table objects and its records in Microsoft SQL Server database?
The Local temporary table objects definition and its records got removed from database once the session which created it disconnected from Microsoft SQL Server database.

How to create Global temporary table object in Microsoft SQL Server Database?

Using double pound sign (##) along with the name of the table, Global temporary table can be created. As shown in below example how to create a Global temporary table.

Example For Global Temporary Table:

CREATE TABLE ##emp_hist
(
empno INT,
empname VARCHAR(40)
)

To whom Global temporary table object are accessible in Microsoft SQL Server database? Global temporary table object are accessible all database users, once all the session accessing it got terminated the Global temporary table object and its records are got deleted from the database. So created user or different user can’t access it once it is removed. Life span of Global temporary table object is equal to the life span of all the session which currently accessing it.
What is the life span for Global temporary table objects and its records in Microsoft SQL Server database?
The Global temporary table objects definition and its records got removed from database once every user referencing this table object got disconnected from Microsoft SQL Server.