SQL Server Basics: How to Create Different Type of Tables

Most of the time we forget to write for beginners or who occasionally use SQL Server. Possibility its because we just want to pressurize others with our knowledge and resultantly we don't like to write for simple topic targeting beginners.

Today we will discuss about different types of tables, which can be created for different purposes.

Regular User Tables
Regular user table is the actually table which holds data of user for later on processing and reporting purpose. These are also called physical tables at they physically resides at hard drive until you DROP them intentionally.

CREATE TABLE [dbo].[TestTable]
   (
     [TestTableID] [int] NOT NULL,
     [FirstCol] [varchar](200) NULL,
     [SecondCol] [int] NULL
   )
ON  [PRIMARY] --  This part indicates, where (Database FileGroup) table will be created physically

Temporary Tables
Temporary tables and created to hold temporary data regarding intermediate results of different quires. These tables will be drooped automatically once the store procedure is executed (if they are used in stored procedure) or once the session is over. But as good programming practice will must drop these tables once they are not required.

CREATE TABLE #Temp_TestTable
   (
     [TestTableID] [int] NOT NULL,
     [FirstCol] [varchar](200) NULL,
     [SecondCol] [int] NULL
   )
 
GO
-- DROP TABLE #Temp_TestTable --(Drop temporary table when not required)    
GO

Global Temporary Tables
These are just like simple temporary tables but are available to all sessions and will only be dropped automatically when last session of database will be closed. If single session is active, global temporary tables will remain available.

CREATE TABLE ##GTemp_TestTable
   (
     [TestTableID] [int] NOT NULL,
     [FirstCol] [varchar](200) NULL,
     [SecondCol] [int] NULL
   )

GO
-- DROP TABLE ##GTemp_TestTable      
--(Drop global temporary table when not required)

These were three types of tables that can be created in SQL Server. Lets talk about some tricks about tables.

Tables Cloning

Read more: Connect SQL

Posted via email from Jasper-net