Questions tagged [table-variable]
The table-variable tag has no usage guidance.
23
questions
1
vote
1
answer
163
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
181
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
460
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
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 ...
- 183
-1
votes
1
answer
223
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 ...
- 11
7
votes
2
answers
808
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 ...
- 15.8k
3
votes
1
answer
531
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
64k
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
30
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 = '...
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 ...
- 151
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 ...
- 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 ...
- 33
4
votes
3
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 ...
- 494
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 ...
- 1,307
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 ...
- 15.8k
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 ...
- 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 ...
- 15.8k
5
votes
6
answers
2k
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 ...
- 51
6
votes
1
answer
212
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 ...
- 68.3k
9
votes
1
answer
255
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 ...
- 15.8k
8
votes
1
answer
1k
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 ...
- 3,771
21
votes
2
answers
13k
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 ...
- 4,740
482
votes
2
answers
255k
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?
- 83.2k