Article: Using TABLE Variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Using TABLE Variables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-07 : 17:06:28

Srinivas R writes "hi all,
How do i use table data type and what is the use ???
Let me know with a good sample.
Wallops!!!!"

Article Link.

dataphile
Yak Posting Veteran

71 Posts

Posted - 2002-06-10 : 03:57:37

Bill says:

table
A special data type that can be used to store a result set for later processing. Its primary use is for temporary storage of a set of rows, which are to be returned as the result set of a table-valued function.

Syntax

Note Use DECLARE @local_variable to declare variables of type table.

table_type_definition ::=
TABLE ( { column_definition | table_constraint } [ ,...n ] )

column_definition ::=
column_name scalar_data_type
[ COLLATE collation_definition ]
[ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ]
[ ROWGUIDCOL ]
[ column_constraint ] [ ...n ]

column_constraint ::=
{ [ NULL | NOT NULL ]
| [ PRIMARY KEY | UNIQUE ]
| CHECK ( logical_expression )
}

table_constraint ::=
{ { PRIMARY KEY | UNIQUE } ( column_name [ ,...n ] )
| CHECK ( search_condition )
}

Arguments
table_type_definition

Is the same subset of information used to define a table in CREATE TABLE. The table declaration includes column definitions, names, data types, and constraints. The only constraint types allowed are PRIMARY KEY, UNIQUE KEY, and NULL.

For more information about the syntax, see CREATE TABLE, CREATE FUNCTION, and DECLARE @local_variable.

collation_definition

Is the collation of the column consisting of a Microsoft® Windows™ locale and a comparison style, a Windows locale and the binary notation, or a Microsoft SQL Server™ collation.

Remarks
Functions and variables can be declared to be of type table. table variables can be used in functions, stored procedures, and batches.

Use table variables instead of temporary tables, whenever possible. table variables provide the following benefits:

A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.
Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure

SELECT select_list INTO table_variable statements.

table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.

table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.

Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.
Assignment operation between table variables is not supported. In addition, because table variables have limited scope and are not part of the persistent database, they are not impacted by transaction rollbacks.

See Also

COLLATE

CREATE FUNCTION

CREATE TABLE

DECLARE @local_variable

©1988-2000 Microsoft Corporation. All Rights Reserved.

When you need it, you'll know it.

Edited by - dataphile on 06/10/2002 04:00:25

gwhiz
Yak Posting Veteran

78 Posts

Posted - 2002-06-11 : 13:40:51

Has anyone else noticed an additional limitation to the table variable. I have discovered that it does not seem to work with the updatetext function. I have a table variable set up and try and run the following sql and I get an incorrect syntax error.

UpdateText @tbloutxml.outxml @strptr Null Null @tempstring

@tbloutxml has been created and I can insert into the table its just the updatetext function I am having trouble with.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-11 : 14:15:47

I'm amazed that you're even getting a valid text pointer from a table variable. Since a table variable is not stored on disk, there are no pages and therefore no pointers. If you need to do text operations like this you'll have to stick with temp or regular tables.

PCNuttall
Starting Member

2 Posts

Posted - 2002-06-11 : 15:20:17

You have to be careful using table variables. Remember, this is a memory structure only, not disk like a #temp table. If you have a wide table with lots of rows you are going to gobble up lots of memory. It will be faster yes, but unless your luck is better than mine, you won't get something for nothing.

I use table variables for small, short tables and regular temp tables for long, large tables.

mcp111
Starting Member

44 Posts

Posted - 2002-07-11 : 17:51:50

How do you use a table variable to avoid a left join?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-11 : 17:56:40

OK, you're gonna have to explain in more detail why you're trying to avoid LEFT JOINs:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=17335

What's wrong with LEFT JOINs?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-10-26 : 10:03:07

The following link provides info on table variables vs temp tables:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q305977&

furryfish
Starting Member

2 Posts

Posted - 2005-01-26 : 21:32:55

Perhaps he's going to use an IN in the where clause based on a table variable's data? This removes the need for a join and behaves like an inner.

IE

select * from TABLE
where ID in (select I from @tableVariable)

sohailansari
Starting Member

4 Posts

Posted - 2005-03-04 : 09:37:53

Is is possible to concatenate values from two different table variables? I seem to have problems with it. Any ideas?

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-04 : 10:02:57

quote:
Originally posted by sohailansari

Is is possible to concatenate values from two different table variables? I seem to have problems with it. Any ideas?

Thanks

??? A table variable is a table ... how would you like to concatenate two tables? You might need to give an example of what you are trying to accomplish.

- Jeff

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-04 : 10:32:50

I think he said values from tb vars rather than the tables themselves.
Just like you would actual tables:


set nocount on
declare @t1 table (tID int, col1 varchar(10))
declare @t2 table (tID int, col1 varchar(10))

insert @t1 values (1,'T')
insert @t2 values (1,'G')

SElect	a.col1 + b.col1 ConcatResult
From	@t1 a
join	@t2 b
		ON a.tID = b.tID

Be One with the Optimizer
TG

sohailansari
Starting Member

4 Posts

Posted - 2005-03-04 : 12:21:13

Thanks TG for the reply. It sure works with varchar....how about working with TEXT type table variables? Actually the value that finally comes after concatenation is more than 8000 characters.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-04 : 12:33:35

quote:
Originally posted by robvolk

I'm amazed that you're even getting a valid text pointer from a table variable. Since a table variable is not stored on disk, there are no pages and therefore no pointers. If you need to do text operations like this you'll have to stick with temp or regular tables.

I didn't realize you were talking about text columns in a table variable. Rob said it best.

Be One with the Optimizer
TG

sohailansari
Starting Member

4 Posts

Posted - 2005-03-04 : 12:40:26

Also let me give the situation here what I am trying to do here....this stored procedure is suppose to create a report. This report is converted to HTML and the whole value sits in a field or file. In this stored procudre, each time the loop generates about 5000 charaters which ultimately have to be concatenated and written to a file. For e.g. if the loop goes 10 times I am looking to store about 50K of data.

If you guys think that using table variable is not a good idea and this can be accomplished through some other method please let me know. Remember I dont want to write each chunk of 5000 characters in a temporary field or file as it will slow down the whole process.

Hope this will help!

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-04 : 12:51:14

quote:
This report is converted to HTML and the whole value sits in a field or file

Sorry, I'm not clear on whether you are storing this 50K html in a file system file or a sql text column. But in either case, a table variable is NOT the way to go. Are you ultimately taking database data and storing it on a file system (not db text column) as .html file?

Be One with the Optimizer
TG

sohailansari
Starting Member

4 Posts

Posted - 2005-03-04 : 13:38:54

either way....i am ok with file system file or a sql text column. No its not the whole database data going to store in a file, but there are couple of reports that exceeds this much. Right now i am calling these reports through ASP pages manually thats works fine but I have to put them in a stored procedure to execute them automatically and the generated report should be saved in some format for later use.

dekstrom
Starting Member

1 Post

Posted - 2006-11-30 : 11:44:14

I know this message is old, but what is the solution? I have a table that will be created from more than the 8000 max. Here is the code:

declare @results table (col1 varchar(255))
declare @htmltable varchar(8000)

INSERT INTO @results SELECT '<tr><td><strong>Closed Incidents</strong></td><td>'
+ convert(varchar(10),(select count(workitem_number) from
.
.
.
select @htmltable=COALESCE(@htmltable,'')+Col1 from @results

select @htmltable

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-11-30 : 11:59:21

You really should not be generating HTML in SQL Server; the raw data should be returned to your web application and it should handle all formatting. This will be more efficient, cleaner, and clearer and you won't run into limitations such as lengths of varchars.

- Jeff

pvten
Starting Member

1 Post

Posted - 2007-06-20 : 06:15:46

quote:
Originally posted by gwhiz

Has anyone else noticed an additional limitation to the table variable.

Hi all,
I heard that table variable can be used with Stored Procedures (SP), function.. but
When I'm trying to use table variable as a parameter (in both IN or OUTPUT type) for may SP, but I received an error..(??)

So my question is: CAN WE USE IT AS A PRAMETER IN SP ??

Ten PV

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-06-20 : 06:59:09

Currently a table variable can't be used as a parameter for a stored procedure. Other options include passing a CSV or XML.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources