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
8
votes
2 answers

T-SQL Table variable with case sensitive columns - collate SQL_Latin1_General_CP1_CS_AS

Is it possible to have collate SQL_Latin1_General_CP1_CS_AS in table variable columns' definitions? The reason I want to do this is because I have case sensitive information in my source table but when I insert it in the table variable there is a…
gotqn
  • 42,737
  • 46
  • 157
  • 243
7
votes
2 answers

Why are temporary tables faster than table variables for joins?

Why are temporary tables faster than table variables for joins in SQL Server? NOTE: In both scenarios the tables have PK, and the tables are joined with other "physical" tables through the PK.
Guillermo Gutiérrez
  • 17,273
  • 17
  • 89
  • 116
6
votes
3 answers

Dapper: inserting rows into table variables

i have lately been using dapper and all in all had no problems with it except when using table variables. For demonstration, I use a modified example from this thread. This code works without problems: int tally = connection.Execute( "create…
Kc_
  • 201
  • 4
  • 11
6
votes
2 answers

Get definitive names for columns from table variable

I can declare a table variable as such: DECLARE @tv_source TABLE(c1 int, providerName varchar(50), providerSMS varchar(50)) If I then execute the following, I see the table name similar to: "#468862B0" select top 1 * from tempdb.sys.tables where…
Mark Schultheiss
  • 32,614
  • 12
  • 69
  • 100
6
votes
2 answers

Scope of table variable within SQL cursor

If I run the below in MS SQL 2008 R2 I get an unexpected result. create table #DataTable (someID varchar(5)) insert into #DataTable values ('ID1'),('ID2'),('ID3'),('ID4'),('ID5') declare @data varchar(8); declare myCursor cursor for select…
zmaster
  • 301
  • 2
  • 6
  • 15
5
votes
4 answers

Is using Table variables faster than temp tables

Am I safe to assume that where I have stored procedures using the tempdb to write a temporary table, I'd be better off switching these to table variables to get better performance?
Neil Knight
  • 47,437
  • 25
  • 129
  • 188
5
votes
1 answer

How to use a table variable in an "update from select" query?

I have this table variable declaration followed by a query: DECLARE @CurrentItems TABLE ( ItemId uniqueidentifier, ItemUnits int ) UPDATE U SET U.Units = U.Units + [@CurrentItems].ItemUnits FROM @CurrentItems CI INNER JOIN U ON…
sharptooth
  • 167,383
  • 100
  • 513
  • 979
5
votes
1 answer

Why can't I run INSERT EXEC on a table variable in a T-SQL function?

My function looks like this: CREATE FUNCTION fn_FileSys_DirExists(@dirName AS nvarchar(260)) RETURNS bit AS BEGIN DECLARE @dirExists int DECLARE @fileResults TABLE ( file_exists int, file_is_a_directory int, …
ProfK
  • 49,207
  • 121
  • 399
  • 775
5
votes
1 answer

Poor performance of SQL query with Table Variable or User Defined Type

I have a SELECT query on a view, that contains 500.000+ rows. Let's keep it simple: SELECT * FROM dbo.Document WHERE MemberID = 578310 The query runs fast, ~0s Let's rewrite it to work with the set of values, which reflects my needs more: SELECT *…
mikus
  • 3,042
  • 1
  • 30
  • 40
5
votes
2 answers

RODBC command 'sqlQuery' has problems with table variables in t-SQL

I am using the RODBC package which I am applying on a Microsoft SQL Server 2012. Now I have discovered a phenomenon that puzzles me. If I run the following query with the RODBC command sqlQuery, then, in R, I will get back an empty data frame with…
Andro
  • 53
  • 1
  • 4
5
votes
1 answer

CTE,Temp table and table variable

Can any one please tell me where to use CTE, temp table and table variable? I read about their differences but i'm confused with their usage. please help. Thanks.
SQLnewbe
  • 63
  • 1
  • 4
5
votes
4 answers

Can queries that read table variables generate parallel exection plans in SQL Server 2008?

First, from BOL: Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider…
Peter Radocchia
  • 10,710
  • 2
  • 34
  • 56
4
votes
0 answers

Return table from a user defined function, which is best?

I was wondering what is the better practice to return a table from a function? Should I define the table structure or use RETURNS TABLE? What are the scenarios of using both flavors? For example: ALTER FUNCTION [dbo].[fnSplitIDs] ( @List…
zig
  • 4,524
  • 1
  • 24
  • 68
4
votes
2 answers

How to save a queried CTE output into a temporary table or table variable

I have a CTE and query this one ;With CTE_Table as (SELECT ...) Select * from CTE_Table Now I try to save this result into a table variable or temporary table. If I try Declare @Table table (...) INSERT INTO @Table (...) HER I PUT THE CODE ABOVE I…
ruedi
  • 5,365
  • 15
  • 52
  • 88
4
votes
3 answers

Use the result of a system stored procedure as a queryable table

Note: the highest linked question does not solve the problem for system stored procedures, but it's close. With help of the commenters, I came to a working answer. Trying to use statements such as the following for sp_spaceused, throws an…
Abel
  • 56,041
  • 24
  • 146
  • 247
1 2
3
…
11 12