when you need to temporarily store data in tables , suppose we might need the data to be visible only to the current Section, we will generally go for Temp tables.
SQL Server supports three kind of temporary tables . Local Temporary tables, global temporary tables, and table variables. All the temporary tables are created in the tempDb database.
1) Local temporary table. like as #T1. This type of temp table is visible only to the session that created it, in the creating level and all inner levels in the call stack. A local Temporary table is destroyed automatically by SQL Server when the creating level in the call stack goes out of scope. we might wonder how SQL Server prevents name conflicts when two sessions create local temporary tables with the same name. SQL Server internally adds a suffix to the table name that makes it unique in tempdb.
2) Global temporary Tables . like as ##T1
when we create a global temporary table, it is visible to all other sessions. Global temporary tables are destroyed automatically by SQLServer when the creating session disconnects and there are no active references to the table.
3) Table Variables.
Table variables are similar to local temporary tables in some ways and different n others. You declare table variables much like you declare other variables, by using DECLARE statement.
As with local temporary tables, table variables have a physical presence as a table in the tempdb database, contrary to the common misconception that they exist only in memory.Like local temporary tables, table variables are visible only to the current session, but they have a more limited scope: only to the current batch. Table variables are visible neither to inner batches n the call stack nor to subsequent batches in the session.
DECLARE @MYDat TABLE
(
ID INT NOTNUll Primary key,
Firstname varchar(10)
)
K Battula 