Monday 15 July 2013

Differences between Temp Tables and Temp Table Variables in SQL Server

Some facts that i have noticed while working with Temp Tables and Temp Table Variables in SQL Server

-- Create Temp Table
-- It gets Stored in TempDB ---> Temporary Tables
CREATE TABLE #Test(
ID INT IDENTITY PRIMARY KEY,
NAME VARCHAR(20)
)

--Skips FK. FOREIGN KEY constraints are not enforced on local or global temporary tables.
CREATE TABLE #Test1(
ID INT REFERENCES #Test(ID) ,
NAME VARCHAR(20)
)

--Declare Table Variable
--It also gets stored in TempDB
DECLARE @Test TABLE(
ID INT IDENTITY PRIMARY KEY,
NAME VARCHAR(20) UNIQUE NONCLUSTERED
)
--NON Clustered Indexing
--CREATE NONCLUSTERED INDEX [IX_Test_Name] ON @Test(Name) -- Not possible, however we can create PK and UNIQUE constraint

--CREATE NONCLUSTERED INDEX [IX_Test_Name] ON #Test(Name) -- possible

--Insert Records in #Test and @Test
DECLARE @i int = 0;
DECLARE @j VARCHAR(MAX);
WHILE @i < 1000000
BEGIN
    SET @i += 1;
SET @j = CAST(@i AS VARCHAR(MAX));
INSERT INTO @Test VALUES ('NAME'+@j);
END;
--SELECT * FROM @Test WHERE NAME='NAME3'
--EXEC sp_Test @Test

--SELECT Records
;With CTE1 AS(
SELECT * FROM @Test)
SELECT * FROM CTE1

SELECT * FROM @Test WHERE ID = 10  -- Fetching records from @Test Takes double time than #Test

SELECT * FROM #Test

;With CTE1 AS(
SELECT * FROM #Test)
SELECT * FROM CTE1

--Join Table Variables and Temp Tables
SELECT T.ID,T.NAME
FROM @Test T
JOIN #Test S ON S.ID = T.ID

--Table Variable with FK NOT ALLOWED
DECLARE @Test1 TABLE(
ID INT, --REFERENCES #Test(ID),
NAME VARCHAR(20)
)

--Connot Create Triggers on temp
CREATE TRIGGER trgAfterInsert ON #Test
FOR INSERT
AS
PRINT 'Records Inserted'
Go

--TABLE Valued Parameters
CREATE TYPE Testing AS TABLE(
ID int,
NAME VARCHAR(20))

CREATE PROCEDURE sp_Test
(
@T AS Testing READONLY
)
AS
BEGIN
SELECT * FROM @T
END




1 comment:

  1. What are the best slots in Vegas for $25,000 | DRMCD
    The best 창원 출장샵 slot machines with progressive 천안 출장샵 jackpot slots. 부산광역 출장샵 Best Slot Machines with progressive jackpot 제천 출장샵 slots. Slots with progressive jackpot slots. Top Slots 나주 출장샵

    ReplyDelete