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 declare a table variable?

Following is a Microsoft SQL Server T-SQL script to declare and use a table variable:

-- Table Variable usage is similar to table

DECLARE @OrderShipperXref TABLE(

  ShipperID SMALLINT IDENTITY ( 1 , 1 ) PRIMARY KEY,

  ShipperName VARCHAR(64),

  PurchaseOrderID INT,

  ShipDate SMALLDATETIME DEFAULT (CURRENT_TIMESTAMP),

  FreightCost SMALLMONEY )

 

INSERT @OrderShipperXref

      (shippername,

       purchaseorderid,

       freightcost)

VALUES('ABC Distributor',34433,80.23)

 

SELECT *

FROM   @OrderShipperXref

GO

/*

ShipperID   ShipperName PurchaseOrderID   ShipDate             FreightCost

1         ABC Distributor     34433       2011-08-26 09:37:00     80.23

*/

 

Table variables can be used instead of temporary tables. They may perform better especially if the tables are small. They are not exact replacement for temporary tables on the other hand since they don't exist outside a batch (scope of a table variable is a batch).


Related arcticles:

http://www.sqlusa.com/bestpractices/whilelooptablevariable/

INF: Frequently Asked Questions - SQL Server 2000 - Table Variables

 

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