how many types of table available in SQL server?

Question

Yogesh Ghorpade · Accepted Answer

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    )

Yogesh Ghorpade · Answer

If You Like Answer Please Accept Answer