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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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