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

Existing regular, temporary or global temporary tables can be cloned (structure as well as their data). Following statement will create a new table, exactly same in structure to existing one.

SELECT  *

INTO    [dbo].[NewTestTable]

FROM    [dbo].[TestTable]

WHERE   1 = 2     -- Remove WHERE clause if you want to copy both structure and data

Inserting Data On Basis of Stored Procedure Result

Table can be populated with data from result set of stored procedure. Table variables can not be populated in this fashion.

 

INSERT INTO [YourTableName](CommaSeparatedColumnsName)

 EXECUTE YourStoredProcedureNameHere CommaSeparatedParameterValues

Table Variables

Table variables are just like scalar variables which possess structure of a table and can hold records for intermediate results of different quires. These are the best alternative for temporary tables as there is no need to worry about demolition of table variables after use.

DECLARE @VarTestTable TABLE

(

      [TestTableID] [int] NOT NULL,

      [FirstCol] [varchar](200) NULL,

      [SecondCol] [int] NULL

)