Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [table-variable]

27 questions
491
votes
2 answers

What's the difference between a temp table and table variable in SQL Server?

This seems to be an area with quite a few myths and conflicting views. So what is the difference between a table variable and a local temporary table in SQL Server?
Martin Smith
  • 87,941
  • 15
  • 255
  • 354
21
votes
2 answers

Why must TVPs be READONLY, and why can't parameters of other types be READONLY

According to this blog parameters to a function or a stored procedure are essentially pass-by-value if they aren't OUTPUT parameters, and essentially treated as a safer version of pass-by-reference if they are OUTPUT parameters. At first I thought…
Erik
  • 4,833
  • 4
  • 28
  • 57
9
votes
1 answer

how come a table variable is improving the performance of a query in this circumstance?

for this specific case, that I will try to explain below, using a table variable is performing better than not using a table variable. I would like to know why, and if possible, get rid of the table variable. this is the query using the table…
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
8
votes
3 answers

Using SPID in DB Tables (instead of Table Variable)

Transactional database used for booking things... Our vendor was asked to replace #temptables with @tablevariables (because of heavy compile locks) but instead they replaced with an actual table that adds SPID as a column to ensure the stored…
outjet
  • 183
  • 4
8
votes
1 answer

How to name table-variable function unique constraint?

I am renaming some unique constraints to match our database objects naming convention. Strangely, there are several multi-line table valued function which returned table has unique constraints as follows: CREATE FUNCTION [dbo].[fn_name]…
gotqn
  • 4,348
  • 11
  • 52
  • 91
7
votes
2 answers

Does OPTIMIZE FOR UNKNOWN do anything for table-valued variables/parameters?

I have a query that is both prone to parameter sensitivity and is suffering from its table-valued parameter. I'm lazy and just want to solve this with query hints. When I'm lazy, I can solve parameter sensitivity with OPTION (OPTIMIZE FOR UNKNOWN).…
J. Mini
  • 1,161
  • 8
  • 32
7
votes
2 answers

how to create a data type and make it available in all Databases?

If I create a stored procedure in the master database, and I want to execute it from any of my databases I just follow this link: Making a Procedure Available in all Databases that give me this code example: Just by following the example above, I…
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
6
votes
1 answer

Benefit or use-case for empty variable / parameter / temporary table / temporary procedure names?

I just discovered, through sheer brilliance accidentally, that SQL Server allows you to create variables, parameters, table variables, temporary tables (local and global), and temporary stored procedures (local and global) without any name! Well, at…
Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306
5
votes
1 answer

varchar and nvarchar in tuning a stored procedure - how to improve performance in this scenario?

I have the following procedure that is called over a million times a day, and I think it can be tuned for better resources usage. ALTER PROCEDURE [DenormV2].[udpProductTaxRateGet] ( @itemNo varchar ( 20 ), @calculateDate datetime, …
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
5
votes
6 answers

Alternative to xp_cmdshell for emailing a report as a CSV file

I've got an issue that I could do with some ideas as to how to achieve what's needed without using (or enabling) xp_cmdshell if possible. I know that xp_cmdshell itself poses risks, even with a proxy account, however - in our environment it's…
Arkane
  • 51
  • 1
  • 2
4
votes
2 answers

Stored Procedure returns different results in SSMS vs C# code

I have a stored procedure that when executed in SSMS returns different values than when the same SP is executed in code, even a very simple SP call and dump in Linqpad. We believe this started happening after migrating to a new server running SQL…
Steve
  • 43
  • 1
  • 4
4
votes
4 answers

Effect on execution plans when a table variable has a primary key

Having read a great deal about the differences between temporary tables and table variables in SQL Server, I am experimenting with switching from mostly using temporary tables to mostly using table variables. (They seem to be a better fit for the…
sam.bishop
  • 494
  • 3
  • 16
4
votes
1 answer

Parallelism with temp table but not table variable?

The first query (inserts into table variable) takes twice as long as the second one. It does not use parallelism in the execution plan. The second query (inserts into temp table) uses parallelism in its execution plan and is able to achieve the…
K. R.
  • 143
  • 1
  • 4
3
votes
1 answer

Reasons for not Globally enabling Trace Flag 2453 - Cardinality on Table Variables

I've been doing some reading on the improvements that Trace Flag 2453 can give on the performance of Table Variables by maintaining statistics and was wondering what would be the reasons you would advise to not turn this on globally. I understand…
crucible
  • 307
  • 1
  • 8
3
votes
2 answers

How to insert into TABLE Variable?

I want to store 2 coordinate points (latitude, longitude) in a table variable. I have tried: declare @coordinates table(latitude1 decimal(12,9), longitude1 decimal(12,9), latitude2…
Gour Gopal
  • 255
  • 1
  • 3
  • 10
1
2