DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to compare temporary table vs table variable speed?

The following performance measurement tests show that for this particular case temporary table performance is the same as table variable performance.

 

USE AdventureWorks2012;

GO

 

CREATE  TABLE #Product (

       [ProductID] [int]  NOT NULL PRIMARY KEY,

       [Name] nvarchar(64) NOT NULL,

       [ProductNumber] [nvarchar](25) NOT NULL,

       [MakeFlag] bit NOT NULL,

       [FinishedGoodsFlag] bit NOT NULL,

       [Color] [nvarchar](15) NULL,

       [SafetyStockLevel] [smallint] NOT NULL,

       [ReorderPoint] [smallint] NOT NULL,

       [StandardCost] [money] NOT NULL,

       [ListPrice] [money] NOT NULL,

       [Size] [nvarchar](5) NULL,

       [SizeUnitMeasureCode] [nchar](3) NULL,

       [WeightUnitMeasureCode] [nchar](3) NULL,

       [Weight] [decimal](8, 2) NULL,

       [DaysToManufacture] [int] NOT NULL,

       [ProductLine] [nchar](2) NULL,

       [Class] [nchar](2) NULL,

       [Style] [nchar](2) NULL,

       [ProductSubcategoryID] [int] NULL,

       [ProductModelID] [int] NULL,

       [SellStartDate] [datetime] NOT NULL,

       [SellEndDate] [datetime] NULL,

       [DiscontinuedDate] [datetime] NULL,

       [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,

       [ModifiedDate] [datetime] NOT NULL);

 

GO

 

 

 

DECLARE @Product TABLE (

       [ProductID] [int] NOT NULL PRIMARY KEY,

       [Name] nvarchar(64) NOT NULL,

       [ProductNumber] [nvarchar](25) NOT NULL,

       [MakeFlag] bit NOT NULL,

       [FinishedGoodsFlag] bit NOT NULL,

       [Color] [nvarchar](15) NULL,

       [SafetyStockLevel] [smallint] NOT NULL,

       [ReorderPoint] [smallint] NOT NULL,

       [StandardCost] [money] NOT NULL,

       [ListPrice] [money] NOT NULL,

       [Size] [nvarchar](5) NULL,

       [SizeUnitMeasureCode] [nchar](3) NULL,

       [WeightUnitMeasureCode] [nchar](3) NULL,

       [Weight] [decimal](8, 2) NULL,

       [DaysToManufacture] [int] NOT NULL,

       [ProductLine] [nchar](2) NULL,

       [Class] [nchar](2) NULL,

       [Style] [nchar](2) NULL,

       [ProductSubcategoryID] [int] NULL,

       [ProductModelID] [int] NULL,

       [SellStartDate] [datetime] NOT NULL,

       [SellEndDate] [datetime] NULL,

       [DiscontinuedDate] [datetime] NULL,

       [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,

       [ModifiedDate] [datetime] NOT NULL);

 

TRUNCATE TABLE #Product;

INSERT #Product SELECT * FROM Production.Product;

-- (504 row(s) affected)

 

INSERT @Product SELECT * FROM Production.Product;

-- (504 row(s) affected)

 

 

DECLARE  @ExecutionTime  TABLE(      Duration INT    )

 

DECLARE  @StartTime DATETIME,  @i INT = 1;

WHILE (@i <= 100)

  BEGIN

    DBCC DROPCLEANBUFFERS

    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate()

    /****************** measured process ***********************/

 

    SELECT * FROM @Product WHERE Name = 'Road-550-W Yellow, 48';

 

    /**************** end measured process *********************/

    INSERT @ExecutionTime

    SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP)

    SET @i +=  1

  END -- WHILE

 

SELECT DurationInMilliseconds = AVG(Duration) FROM   @ExecutionTime

-- 22 msec

GO

 

-- Temporary table

DECLARE  @ExecutionTime  TABLE(      Duration INT    )

DECLARE  @StartTime DATETIME,  @i INT = 1;

WHILE (@i <= 100)

  BEGIN

    DBCC DROPCLEANBUFFERS

    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate()

    /****************** measured process ***********************/

 

    SELECT * FROM #Product WHERE Name = 'Road-550-W Yellow, 48';

 

    /**************** end measured process *********************/

    INSERT @ExecutionTime

    SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP)

    SET @i +=  1

  END -- WHILE

 

SELECT DurationInMilliseconds = AVG(Duration) FROM   @ExecutionTime

GO

-- 23 msec

 

CREATE INDEX idxProdName on #Product(Name);

GO

 

-- Temporary table

DECLARE  @ExecutionTime  TABLE(      Duration INT    )

DECLARE  @StartTime DATETIME,  @i INT = 1;

WHILE (@i <= 100)

  BEGIN

    DBCC DROPCLEANBUFFERS

    SET @StartTime = CURRENT_TIMESTAMP -- alternate getdate()

    /****************** measured process ***********************/

 

    SELECT * FROM #Product WHERE Name = 'Road-550-W Yellow, 48';

 

    /**************** end measured process *********************/

    INSERT @ExecutionTime

    SELECT DurationInMilliseconds = datediff(ms,@StartTime,CURRENT_TIMESTAMP)

    SET @i +=  1

  END -- WHILE

 

SELECT DurationInMilliseconds = AVG(Duration) FROM   @ExecutionTime

-- 23 msec

GO

DROP TABLE  #Product

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE