Questions tagged [table-variable]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
19
votes
2answers
8k views

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 ...
8
votes
3answers
480 views

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 ...
8
votes
1answer
201 views

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 ...
7
votes
2answers
687 views

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 ...
7
votes
1answer
844 views

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 ...
6
votes
1answer
165 views

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 ...
5
votes
6answers
1k views

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 ...
5
votes
1answer
829 views

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 ...
4
votes
3answers
977 views

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 ...
4
votes
2answers
3k views

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 ...
3
votes
1answer
1k views

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 ...
3
votes
1answer
134 views

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 ...
3
votes
1answer
1k views

Should I use tempdb or memory optimized table variable?

I would like to change some of my stored procs to use memory optimized tables. Before I do this, could you help me to clarify some things? parallel execution Here I can read, the tempdb doesn’t ...
1
vote
1answer
2k views

temp tables in tempdb are not cleaned up by the system

My question is similar to the one asked here: SQL Server tempdb rogue temporary tables Since I don't have enough points to comment and the answer there doesn't address my concern, I can only ask a ...
0
votes
2answers
23k views

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), ...
0
votes
1answer
782 views

Can a TVF use multiple CTE statements internally?

I'm trying to make a multi-statement table valued function and failing due to what I think is a limitation of functions and CTEs. The intended function defines an input parameter(@Param) and and ...
0
votes
2answers
761 views

improving performance - how to pass a table as parameter in sql server 2005?

I have been monitoring the execution of my stored procedures in my webservers in order to tune them for best performance. I will call the procedure that has most executions per day as myprocedure and ...
0
votes
1answer
21 views

How to use query result as REPLACE parameters

i'm trying to: 1- Find a string inside a lot of content 2- Replace that specific part of the string with a new one using REPLACE function like this: REPLACE(column_name, query#1, query#2) Is this ...
0
votes
1answer
17 views

Retrieving What variables mean when retrieving view definitions?

I'm working on a small script to transfer views over from one database to another. I can get the view definition by using select view_definition from information_schema.views where table_name = '...
-1
votes
1answer
37 views

use a table as a variable [duplicate]

I was wondering if there was a way to set a table name in SQL to be a variable. I have a list of queries that hit the same table and didn't want to have to update them all so was hoping to declare ...