-- Table creation logic
CREATE TABLE #temptable ([col1] [int] NOT NULL primary key,[col2] [int] NULL,[col3] [int] NULL,[col4] [varchar](50) NULL)
DECLARE @tablevariable TABLE ([col1] [int] NOT NULL primary key,[col2] [int] NULL,[col3] [int] NULL,[col4] [varchar](50) NULL)
CREATE TABLE #bigtemptable ([col1] [int] NOT NULL primary key,[col2] [int] NULL,[col3] [int] NULL,[col4] [varchar](50) NULL)
DECLARE @bigtablevariable TABLE ([col1] [int] NOT NULL primary key,[col2] [int] NULL,[col3] [int] NULL,[col4] [varchar](50) NULL)

CREATE NONCLUSTERED INDEX [IX_temptable] ON #temptable ([col2] ASC)
CREATE NONCLUSTERED INDEX [IX_bigtemptable] ON #bigtemptable ([col2] ASC)
 
-- Populate tables
DECLARE @val INT
SELECT @val=1
WHILE @val <= 2000
BEGIN  
   INSERT INTO #temptable (col1, col2, col3, col4) VALUES (@val,@val,@val,'TEST')
   INSERT INTO @tablevariable (col1, col2, col3, col4) VALUES (@val,@val,@val,'TEST')
   INSERT INTO #bigtemptable (col1, col2, col3, col4) VALUES (@val,@val,@val,'TEST')
   INSERT INTO @bigtablevariable (col1, col2, col3, col4) VALUES (@val,@val,@val,'TEST')
   SELECT @val=@val+1
END

WHILE @val <= 1000000
BEGIN  
   INSERT INTO #bigtemptable (col1, col2, col3, col4) VALUES (@val,@val,@val,'TEST')
   INSERT INTO @bigtablevariable (col1, col2, col3, col4) VALUES (@val,@val,@val,'TEST')
   SELECT @val=@val+1
END

-- simple insert
INSERT INTO #temptable (col1, col2, col3, col4) VALUES (2005,2005,2005,'TEST')
INSERT INTO @tablevariable (col1, col2, col3, col4) VALUES (2005,2005,2005,'TEST')
INSERT INTO #bigtemptable (col1, col2, col3, col4) VALUES (1000005,1000005,1000005,'TEST')
INSERT INTO @bigtablevariable (col1, col2, col3, col4) VALUES (1000005,1000005,1000005,'TEST')

-- select single row where primary key equals value
SELECT * FROM #temptable where col1=1000
SELECT * FROM @tablevariable where col1=1000
SELECT * FROM #bigtemptable where col1=600000
SELECT * FROM @bigtablevariable where col1=600000

-- select range of rows where primary key between values
SELECT * FROM #temptable where col1 between 450 and 500
SELECT * FROM @tablevariable where col1 between 450 and 500
SELECT * FROM #bigtemptable where col1 between 100000 and 100500
SELECT * FROM @bigtablevariable where col1 between 100000 and 100500

-- select single row where other indexed column equals value
SELECT * FROM #temptable where col2=1050
SELECT * FROM @tablevariable where col2=1050
SELECT * FROM #bigtemptable where col2=650000
SELECT * FROM @bigtablevariable where col2=650000

-- select range of rows where other indexed column between values
SELECT * FROM #temptable where col2 between 500 and 550
SELECT * FROM @tablevariable where col2 between 500 and 550
SELECT * FROM #bigtemptable where col2 between 150000 and 150500
SELECT * FROM @bigtablevariable where col2 between 150000 and 150500

-- select single row where other column equals value
SELECT * FROM #temptable where col3=1100
SELECT * FROM @tablevariable where col3=1100
SELECT * FROM #bigtemptable where col3=700000
SELECT * FROM @bigtablevariable where col3=700000

-- select range of rows where other column between values
SELECT * FROM #temptable where col3 between 550 and 600
SELECT * FROM @tablevariable where col3 between 550 and 600
SELECT * FROM #bigtemptable where col3 between 200000 and 200500
SELECT * FROM @bigtablevariable where col3 between 200000 and 200500

-- update single row where primary key equals value
UPDATE #temptable set col4='TESTUPDATE' where col1=1150
UPDATE @tablevariable set col4='TESTUPDATE' where col1=1150
UPDATE #bigtemptable set col4='TESTUPDATE' where col1=750000
UPDATE @bigtablevariable set col4='TESTUPDATE' where col1=750000

-- update range of rows where primary key between values
UPDATE #temptable set col4='TESTUPDATE' where col1 between 600 and 650
UPDATE @tablevariable set col4='TESTUPDATE' where col1 between 600 and 650
UPDATE #bigtemptable set col4='TESTUPDATE' where col1 between 250000 and 250500
UPDATE @bigtablevariable set col4='TESTUPDATE' where col1 between 250000 and 250500

-- update single row where other indexed column equals value
UPDATE #temptable set col4='TESTUPDATE' where col2=1200
UPDATE @tablevariable set col4='TESTUPDATE' where col2=1200
UPDATE #bigtemptable set col4='TESTUPDATE' where col2=800000
UPDATE @bigtablevariable set col4='TESTUPDATE' where col2=800000

-- update range of rows where other indexed column between values
UPDATE #temptable set col4='TESTUPDATE' where col2 between 650 and 700
UPDATE @tablevariable set col4='TESTUPDATE' where col2 between 650 and 700
UPDATE #bigtemptable set col4='TESTUPDATE' where col2 between 300000 and 300500
UPDATE @bigtablevariable set col4='TESTUPDATE' where col2 between 300000 and 300500

-- update single row where other column equals value
UPDATE #temptable set col4='TESTUPDATE' where col3=1250
UPDATE @tablevariable set col4='TESTUPDATE' where col3=1250
UPDATE #bigtemptable set col4='TESTUPDATE' where col3=850000
UPDATE @bigtablevariable set col4='TESTUPDATE' where col3=850000

-- update range of rows where other column between values
UPDATE #temptable set col4='TESTUPDATE' where col3 between 700 and 750
UPDATE @tablevariable set col4='TESTUPDATE' where col3 between 700 and 750
UPDATE #bigtemptable set col4='TESTUPDATE' where col3 between 350000 and 350500
UPDATE @bigtablevariable set col4='TESTUPDATE' where col3 between 350000 and 350500

-- delete single row where primary key equals value
DELETE FROM #temptable where col1=1300
DELETE FROM @tablevariable where col1=1300
DELETE FROM #bigtemptable where col1=900000
DELETE FROM @bigtablevariable where col1=900000

-- delete range rows where primary key between values
DELETE FROM #temptable where col1 between 750 and 800
DELETE FROM @tablevariable where col1 between 750 and 800
DELETE FROM #bigtemptable where col1 between 400000 and 400500
DELETE FROM @bigtablevariable where col1 between 400000 and 400500

-- delete single row where other indexed column equals value
DELETE FROM #temptable where col2=1350
DELETE FROM @tablevariable where col2=1350
DELETE FROM #bigtemptable where col2=950000
DELETE FROM @bigtablevariable where col2=950000

-- delete range of rows where other indexed column between values
DELETE FROM #temptable where col2 between 800 and 850
DELETE FROM @tablevariable where col2 between 800 and 850
DELETE FROM #bigtemptable where col2 between 450000 and 450500
DELETE FROM @bigtablevariable where col2 between 450000 and 450500

-- delete single row where other column equals value
DELETE FROM #temptable where col3=1400
DELETE FROM @tablevariable where col3=1400
DELETE FROM #bigtemptable where col3=1000000
DELETE FROM @bigtablevariable where col3=1000000

-- delete range of rows where other column values
DELETE FROM #temptable where col3 between 900 and 950
DELETE FROM @tablevariable where col3 between 900 and 950
DELETE FROM #bigtemptable where col3 between 500000 and 500500
DELETE FROM @bigtablevariable where col3 between 500000 and 500500

GO