how many types of table available in SQL server?

Question

Yogesh Ghorpade · 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