How to use a Table Variable in T-SQL

Posted Date: 27 Oct 2009	Resource Type: Code Snippets	Category: SQL
Author: Ramaraj R	Member Level: Silver
Rating:	Points: 5

Table Variables are pretty simple to use in T-SQL. In most of our real time scenario we are using the temp table which is physically created in tempdb. This creates overhead, but when you create a table Variable it only resides in the memory which clearly shows that it will be much faster than Temp table.
A table variable goes out of scope immediately after the batch end. If we use Table Variable then we no need to explicitly drop it.

Here is the example code snippet


1. Code snippet which shows how to create a table using table variable


DECLARE @TableVariable table (
			EmployeeID int IDENTITY(1,1),
			Name VARCHAR(150) NOT NULL)
2. Insert values to the Temp table variable

INSERT INTO @ TableVariable (Name) VALUE  (‘Ram’)
INSERT INTO @ TableVariable (Name) VALUE  (‘Ravi’)
INSERT INTO @ TableVariable (Name) VALUE  (‘Ramya’)

3. Select values from the Table variable.

Select  * from @TableVariable

Responses

No responses found. Be the first to respond and make money from revenue sharing program.

Feedbacks

Popular Tags

What are tags ?

Search Tags

Table Variable in T-SQL .

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: Select nth Maximum value without using RANK / MAX
	Previous Resource: Finding a text in a Stored Procedure & User Defined Function
	Return to Discussion Resource Index
	Post New Resource
	Category: SQL

Post resources and earn money!

More Resources

How to use a Table Variable in T-SQL

Responses

Post Feedback

Difference between Rank() and Dense_Rank() in SQL

Using Dense_Rank in SQL server 2005