Stack Overflow
Stack Overflow

Questions tagged [table-variable]

A SQL Server local variable that can store rows.

From MSDN:

Is a special data type that can be used to store a result set for processing at a later time. table is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function.

A table variable differs from a "temporary table" in that it has

  • no statistics
  • limited index options
  • execution plans often assume single row
  • does not participate in user transactions
  • for the batch/proc scope only
170 questions
30
votes
2 answers

How to use a record type variable in plpgsql?

How can I use query result stored into a record type variable for another query within the same stored function? I use Postgres 9.4.4. With a table like this: create table test (id int, tags text[]); insert into test values (1,'{a,b,c}'), …
SG. Nihonbashi
  • 471
  • 1
  • 4
  • 7
27
votes
3 answers

How to take table name as an input parameter to the stored procedure?

I have a small stored procedure below. I am taking the table name as an input parameter to the stored procedure so that I'm planning to insert the data into the temp table and display the same. This is just a tiny code block of my project stored…
chandra sekhar
  • 461
  • 2
  • 11
  • 18
20
votes
4 answers

How to view data in table variables during debugging session in MS SQL Management Studio 2012?

I would like to debug a complex T-SQL script using SSMS 2012. I can run the script in debug mode and place breakpoints, as well as step through my script, but I can't see the values stored in my table variables. In the Locals window I see all these…
Alexander Galkin
  • 12,086
  • 12
  • 63
  • 115
17
votes
5 answers

TSQL table variable initialization

I have the following TSQL table variable: declare @NumDaysMonth table ( month_id smallint, num_days smallint ) I just want a quick look-up for the number of days in each month. How can I initialize this table like a C array: int…
CodeKingPlusPlus
  • 15,383
  • 51
  • 135
  • 216
16
votes
3 answers

How to check if a table variable is empty in SQL Server?

This is a section of one of my stored procedure: @dataInTable dbo.Table_Variable readonly, .... AND ( ( @dataInTable IS NULL ) OR ( item IN ( SELECT T FROM @dataInTable ) ) ) @dataInTable IS NULL is wrong in syntax, error is Must…
Eric Yin
  • 8,737
  • 19
  • 77
  • 118
15
votes
7 answers

Very slow DELETE query

I have problems with SQL performance. For sudden reason the following queries are very slow: I have two lists which contains Id's of a certain table. I need to delete all records from the first list if the Id's already exists in the second…
hwcverwe
  • 5,287
  • 7
  • 35
  • 63
14
votes
3 answers

CTE (Common Table Expression) vs Temp tables or Table variables, which is faster?

CTE (Common Table Expression) vs Temp tables or Table variables, which is faster?
Numan
  • 3,918
  • 4
  • 27
  • 44
12
votes
3 answers

table variables created and held in memory or in tempdb?

Are table variables created in memory or in tempdb? Same for short temp tables?
Pradeep Gaur
  • 563
  • 1
  • 8
  • 14
11
votes
4 answers

Table variable poor performance on insert in SQL Server Stored Procedure

We are experiencing performance problems using a table variable in a Stored Procedure. Here is what actually happens : DECLARE @tblTemp TABLE(iId_company INT) INSERT INTO @tblTemp(iId_company) SELECT id FROM ..... The SELECT returns 138 results,…
MaxiWheat
  • 6,133
  • 6
  • 47
  • 76
11
votes
3 answers

Using a table variable inside of a exists statement

I am trying to update a column inside of a table variable based on a condition, the condition being that the ID of the table variable does not exist in a different table: DECLARE @BugRep TABLE(BugCode VARCHAR(50),DevFirstName VARCHAR(50),…
Developer
  • 17,809
  • 26
  • 66
  • 92
10
votes
1 answer

Structured types must have at least one field c#

What I actually want to do is I want to send a string array to SQL Server. I am using SQL Server 2008. This can be marked as duplicate but I am facing a whole other problem when implementing a solution from stactoverflow LINK: How to pass an array…
Sheraz Ahmed
  • 405
  • 1
  • 4
  • 20
10
votes
2 answers

Inserting multiple rows into a SQL Server table using a table variable

I am currently using SQL Server 2008, and I am trying to create a statement using a table variable to insert multiple rows into the table. As it stands right now, I have to insert the information being added in 4 different spots(2 select…
CM_Heroman
  • 378
  • 1
  • 7
  • 21
9
votes
1 answer

How do I declare two table variables with identical structures?

I have the following table variable declaration: DECLARE @MyTable TABLE ( --ten columns declared here ) and I want to declare another table variable with identical structure (so that I insert-from-select into the first one and then copy the…
sharptooth
  • 167,383
  • 100
  • 513
  • 979
8
votes
3 answers

Query SQL Server with IN (NULL) not working

When I define a "User-Defined Table Type", as: CREATE TYPE [dbo].[BitType] AS TABLE( [B] [bit] NULL ) I place 0 and null in this table-variable. Then I do this query: SELECT something FROM theTable WHERE item IN @theBitTypeTable Will only get…
Eric Yin
  • 8,737
  • 19
  • 77
  • 118
8
votes
4 answers

Table variables inside while loop not initializing everytime : SQL Server

I am wondering why the table variables inside while loop does not behave like other variables. Table variables created only once and will be used across through out whole looping. but other variables getting initialized every time when loop…
RameshVel
  • 64,778
  • 30
  • 169
  • 213
1
2
3
…
11 12