Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [table-variable]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
60 views

Do Primary Keys On Table Variables Have No Statistics At All? [duplicate]

My confusion is down to an apparent contradiction in principles. Primary keys are indexes and those always have statistics, but table variables notoriously always don't have statistics. What follows ...
J. Mini's user avatar
  • 433
0 votes
3 answers
263 views

Why Do Primary Keys on Temp Tables Improve Performance Much More Than On Table Variables?

Many of my reports are essentially pivot tables. I take a set of big tables and transform each table individually so that a particular column (let's call it CaseID) that wasn't a primary key in the ...
J. Mini's user avatar
  • 433
0 votes
1 answer
216 views

Why use a memory-optimized table variable instead of a temp table or normal table variable?

Table variables have a bad reputation, mostly due to cardinality estimates. However, this reputation was earned before the introduction of memory-optimized table variables. Microsoft covers some use-...
J. Mini's user avatar
  • 433
1 vote
1 answer
187 views

Inconsistent results between MySQL and MariaDB when using variables and order

I'm building an accounting table and I need to show the current balance on each transaction. Everything is working fine in my local environment (Windows 10 + MySQL 5.7.19), but the same query gives me ...
azeós's user avatar
  • 113
0 votes
1 answer
230 views

Subquery with variable in where

I don't know why but if I do the subquery where in the first LEFT JOIN with the value of the variable @id it does not return any results, it is as if the value of @id was always null. But why not do ...
Woton Sampaio's user avatar
0 votes
1 answer
537 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 ...
gallo2000sv's user avatar
8 votes
3 answers
1k 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 ...
outjet's user avatar
  • 183
-1 votes
1 answer
249 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 ...
Newelle Horn's user avatar
7 votes
2 answers
891 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 ...
Marcello Miorelli's user avatar
3 votes
1 answer
547 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 ...
crucible's user avatar
  • 307
3 votes
2 answers
67k 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), ...
Gour Gopal's user avatar
0 votes
1 answer
31 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 = '...
daddy_raptor's user avatar
2 votes
1 answer
5k 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 ...
Weihui Guo's user avatar
4 votes
2 answers
6k 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 ...
Steve's user avatar
  • 43
3 votes
1 answer
2k 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 ...
deemon's user avatar
  • 33
4 votes
4 answers
2k 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 ...
sam.bishop's user avatar
2 votes
1 answer
2k 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 ...
Jeff Sacksteder's user avatar
5 votes
1 answer
2k 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 ...
Marcello Miorelli's user avatar
4 votes
1 answer
3k 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 ...
K. R. 's user avatar
  • 143
0 votes
2 answers
1k 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 ...
Marcello Miorelli's user avatar
5 votes
6 answers
3k 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 ...
Arkane's user avatar
  • 51
6 votes
1 answer
218 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 ...
Solomon Rutzky's user avatar
9 votes
1 answer
260 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 ...
Marcello Miorelli's user avatar
8 votes
1 answer
2k 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 ...
gotqn's user avatar
  • 4,026
21 votes
2 answers
14k 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 ...
Erik's user avatar
  • 4,782
487 votes
2 answers
258k views

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's user avatar
  • 84.8k