Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [table-variable]

22 questions
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,722
  • 4
  • 23
  • 56
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 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
  • 14,544
  • 46
  • 126
  • 249
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
  • 3,559
  • 9
  • 40
  • 72
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
  • 14,544
  • 46
  • 126
  • 249
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
  • 65,256
  • 6
  • 135
  • 267
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
  • 14,544
  • 46
  • 126
  • 249
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
  • 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
3 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
  • 474
  • 3
  • 15
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
  • 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
1 answer

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 support it for insert-operation (What's the difference…
deemon
  • 33
  • 3
2
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
  • 245
  • 1
  • 2
  • 10
1
vote
1 answer

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 strange results on my VPS (Linux + MariaDB…
azeós
  • 113
  • 3
1
2