aspx Tutorial

NET Articles,jQuery demo, asp.net with jQuery, online tutorial,Jquery, SilverLight, Javascript, asp.net,JSON, MVC,.NET Articles,demo, Web Services,
Advertise Here

Toolbar

Get our toolbar!

Advertize



Posted by Haseeb Kayani   on Thursday, June 6, 2013 Total Views:  

Table Variables:

Table Variables was introduced in MS SQL Server 2000, the syntax for creating a Table Variables is @.

There are some characteristic of Table variable 

  • Table variable will generally use less resource
  • Table variables cannot have Non-Clustered Indexes
  • Can’t  create constraints in table variables
  • Can’t  create default values on table variable columns
  • The table variables exist only in the same scope as variables.
  • Table variables are good in performance as compared to temporary tables. 

Step 1:

First, Declare a table named @EMP

DECLARE @EMP TABLE(
EMP_ID INTPRIMARYKEY,
EMPNAME VARCHAR(150),
DEPARMENT VARCHAR(150))

Step 2:

Insert some values in @EMP Table variable

INSERT INTO @EMP VALUES ( 1, 'HASEEB','COMPUTER-SCIENCE');
INSERT INTO @EMP VALUES ( 2, 'AAMIR','PHYSICS');
INSERT INTO @EMP VALUES ( 3, 'MOHIB','BUSINESS ADMINISTRATION');

Step 3:

SELECT * FROM @EMP

 

 Output

Temporary Tables

It acts as physical tables, indexes (Non-Clustered/ clustered) and statistics are also created on temporary tables.  It has (DDL) statements which acts as temp tables to add constraints, referential integrity, and defaults such as PK and FK.

There are some characteristic of Temporary Tables

  • Temporary table will generally use higher resource
  • Temporary table can have Non-Clustered Indexes
  • Can  create constraints in Temporary table
  • Can  create default values on Temporary table
  • Temporary tables are not visible in inner stored procedures and in EXEC statements.
  • Temporary table are bad in performance as compared to Table variables. 

Let’s, Create a Temporary table named as #EMP.

CREATE TABLE #EMP  (
EMP_ID INTPRIMARYKEY,
EMPNAME VARCHAR(50),
DEPARMENT VARCHAR(50)

)

Step 2:

Insert some values in #EMP Table variable

INSERT INTO #EMP VALUES ( 1, 'HASEEB','COMPUTER-SCIENCE');
INSERT INTO #EMP VALUES ( 2, 'AAMIR','PHYSICS');
INSERT INTO #EMP VALUES ( 3, 'MOHIB','BUSINESS ADMINISTRATION');

 Step 3:

SELECT * FROM #EMP

 Output

Note:  You must have to drop temporary table otherwise you cannot create a temporary with same name again

   

Tags:
Category: All | SQL 2008 | SQL 2012
Protected by Copyscape Online Plagiarism Tool

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Advertizement 1
Advertizement 2
Advertizement 3
Advertizement 4
Advertizement 5