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
2
votes
1 answer

Delete a number of records given by the number of records in a table

I have a table variable called @workorders, this table are filled by a query: DECLARE @workorders table (id_workorder VARCHAR(100)); INSERT INTO @workorders SELECT DISTINCT(id_workorder) FROM CampaingControl WHERE id_campaing = @id; --get each…
TimeToCode
  • 901
  • 2
  • 16
  • 34
2
votes
1 answer

Why does my CTE join update so much slower than my Table variable join?

I've seen a couple of similar threads, but they all seem to be about massive databases. I've created a dummy database to demonstrate the issue after seeing this crop up in a small live database this morning. The basis for this data is as follows: a…
DaveX
  • 745
  • 6
  • 16
2
votes
1 answer

Exec an SQL-Server 2008 stored-procedure from Access, passing a TABLE variable

I need to pass a table from Access to SQL-server and execute a stored-procedure. I'm using pass-through queries in Access to do this. My pass-through query: DECLARE @MyVar TABLE { ..... } INSERT INTO @MyVar SELECT * FROM…
MSpeed
  • 8,153
  • 7
  • 49
  • 61
2
votes
1 answer

Insert Values from Table Variable into already EXISTING Temp Table

I'm successfully inserting values from Table Variable into new (not yet existing table) Temp Table. Have not issues when inserting small number of rows (eg. 10,000), but when inserting into a Table Variable a lot of rows (eg. 30,000) is throws an…
Data Engineer
  • 795
  • 16
  • 41
2
votes
1 answer

Using Primary Key on Table Variable to improve seek performance

I have this table variable I use in my SP: DECLARE @t TABLE(ID uniqueidentifier) Then I insert some data into it (I later use): INSERT INTO @t(ID) SELECT ID FROM Categories WHERE ... And later I have a few SELECT and UPDATE based on @t IDs…
zig
  • 4,524
  • 1
  • 24
  • 68
2
votes
2 answers

Using table variables in stored procedures versus merely selecting from tables or a view?

I'm looking at sprocs right now that seem to follow the behavior demonstrated below DECLARE @tablevar TABLE ( FIELD1 int, FIELD2 int, FIELD3 varchar(50), -- etc ) INSERT INTO @tablevar ( FIELD1, FIELD2, FIELD3, …
Anthony Pegram
  • 123,721
  • 27
  • 225
  • 246
2
votes
1 answer

SQL error: String or binary data would be truncated

I got a table variable @RQ, I want it updated using a table-valued function. Now, I think I do the update wrong, because my function works... The function: ALTER FUNCTION [dbo].[usf_GetRecursiveFoobar] ( @para int, @para datetime, …
Joris
  • 731
  • 3
  • 13
  • 21
2
votes
2 answers

Can someone give me a real time example with the below temp table and tablevariable example that I found in stackexchange

Difference between temp table and table variable as stated: Operations on @table_variables are carried out as system transactions, independent of any outer user transaction, whereas the equivalent #temp table operations would be carried out as part…
Suj
  • 416
  • 1
  • 5
  • 11
2
votes
2 answers

SQL Server Updating from a table variable

Final Edit - Solved - Solution Below I can't believe that I didn't know this, but apparently the issue was that the table variable and the real table both had the same name: @CHECKERS and dbo.CHECKERS. The trigger was apparently confusing the two.…
Ben K
  • 23
  • 6
2
votes
2 answers

T-SQL copying a table variable

I'm trying to make a copy of a table variable: DECLARE @lt_Sections TABLE ( teamId SMALLINT NOT NULL ) DECLARE @lt_tempSections TABLE ( teamId SMALLINT NOT NULL ) -- populate some values in @lt_Sections -- take a copy of @lt_Sections SET…
Mark Cooper
  • 6,738
  • 5
  • 54
  • 92
2
votes
2 answers

Delete from table variable MS SQL

I'm trying to delete rows from a table variable one by one every loop in my stored procedure but there are times that it keeps looping and cannot delete the record. The record is there even when I tried to PRINT the value. I dont get any errors when…
user902098
  • 33
  • 1
  • 3
1
vote
3 answers

SQL Server 2008 - UDF Parameter Types And Return Types

I often stumble at the following while writing UDFs in SQL 2008. Please tell me whether my following assumptions are right or wrong. A UDF can return Data Table. But a UDF can't receive a Data Table as a parameter. A UDF can receive an…
Sakthivel S
  • 11
  • 1
1
vote
1 answer

SQL Server: Cardinality estimation for table variable

SQL Server does not maintain statistics for table variable and it always estimates 1 output row. Microsoft changed this behavior post release of SQL Server 2019. Though I am not able to find much information about those changes. Below is my sample…
KnowledgeSeeeker
  • 620
  • 1
  • 9
  • 14
1
vote
3 answers

Improve performance of deletes on a table variable

I have seen performance tweaks for delete on normal tables in t-sql. But are there performance tweaks on deletes on table variables to be done? EDIT Here's an example: The plot gets thicker, as UserExclusionsEvaluate is actually a CTE, but I'm…
Alex KeySmith
  • 16,657
  • 11
  • 74
  • 152
1
vote
3 answers

Deleting using left join and a table variable

Maybe I'm missing a bracket or something but I'm having a hard time deleting rows from a table variable where I'm left joining and looking for the key I'm joining on. If it has a value, then I get rid of it. The problem is that I can't get the query…
Josh Robinson
  • 452
  • 6
  • 21
1 2 3
…
11 12