1.local temporary tables (#table_name),
2.global temporary tables (##table_name),
3.permanent tables (table_name), and
4.table variables (@table_name).
Local Temporary Tables :
| CREATE TABLE #people ( id INT, name VARCHAR(32) ) |
A temporary table is created and populated on disk, in the system database tempdb
The data in this #temp table (in fact, the table itself) is visible only to the current scope (usually a stored procedure, or a set of nested stored procedures). The table gets cleared up automatically when the current procedure goes out of scope, but you should manually clean up the data when you're done with it:
| DROP TABLE #people |
CREATE TABLE #TibetanYaks( YakID int, YakName char(30) ) INSERT INTO #TibetanYaks (YakID, YakName) SELECT YakID, YakName FROM dbo.Yaks WHERE YakType = 'Tibetan' -- Do some stuff with the table drop table #TibetanYaks
Table Variables :
A table variable is created in memory, and so performs slightly better than #temp tables
1.Table variables don't need to be dropped when you are done with them.
2.If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.
3.Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data (which can be a good thing or a bad thing; remember "release early"?).
4.Table variables are the only way you can use DML statements (INSERT, UPDATE, DELETE) on temporary data within a user-defined function. You can create a table variable within a UDF, and modify the data using one of the above statements. For example, you could do this:
5.we can use the temporary table with in function
| DECLARE @people TABLE ( id INT, name VARCHAR(32) ) |
| CREATE FUNCTION dbo.example1 ( ) RETURNS INT AS BEGIN DECLARE @t1 TABLE (i INT) INSERT @t1 VALUES(1) INSERT @t1 VALUES(2) UPDATE @t1 SET i = i + 5 DELETE @t1 WHERE i < 7 DECLARE @max INT SELECT @max = MAX(i) FROM @t1 RETURN @max END GO |
DECLARE @TibetanYaks TABLE (
YakID int,
YakName char(30) )
INSERT INTO @TibetanYaks (YakID, YakName)
SELECT YakID, YakName
FROM dbo.Yaks
WHERE YakType = 'Tibetan'
-- Do some stuff with the table DECLARE @TibetanYaks TABLE (
YakID int,
YakName char(30) )
INSERT INTO @TibetanYaks (YakID, YakName)
SELECT YakID, YakName
FROM dbo.Yaks
WHERE YakType = 'Tibetan'
UPDATE @TibetanYaks
SET YakName = UPPER(YakName)
SELECT *
FROM @TibetanYaks
