Questions tagged [table-variable]
The table-variable tag has no summary.
29 questions
0 votes
0 answers
38 views
Kitchen Sink Pagination - How to avoid recompiles or bad estimates when materializing intermediate result sets (temp table vs table variable vs CTE)?
I have a high-traffic SQL Server stored procedure that runs thousands of times per hour. The procedure loads a filtered set of “projects” for a user, identifies the “active task” for each project, ...
- 821
1 vote
0 answers
81 views
Under what circumstances will a memory optimized table variable with a hash index outperform other temporary tabular structures?
Context Aside from victims of latch contention, everybody gave up on memory-optimized table variables pretty quickly. Probably around the time they realised that they still don't have statistics. Yet, ...
- 1,342
7 votes
2 answers
417 views
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 ...
- 1,342
0 votes
0 answers
62 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 ...
- 1,342
0 votes
3 answers
1k 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 ...
- 1,342
0 votes
1 answer
711 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-...
- 1,342
1 vote
1 answer
248 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 ...
- 113
0 votes
1 answer
307 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 ...
0 votes
1 answer
650 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 ...
- 27
8 votes
3 answers
2k 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 ...
- 183
-1 votes
1 answer
292 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 ...
- 21
7 votes
2 answers
1k 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 ...
- 17.3k
3 votes
1 answer
593 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 ...
- 307
3 votes
2 answers
71k 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), ...
- 255
0 votes
1 answer
41 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 = '...