Linked Questions
26 questions linked to/from What's the difference between a temp table and table variable in SQL Server?
-1
votes
2
answers
2k
views
What are the differences between a table type and a table variable in MS SQL [duplicate]
Are there differences between a table variable and a table type in Microsoft SQL Server? Or is a table type just a table variable that is defined once for broad usage?
- 109
1
vote
1
answer
1k
views
Table variable for large amount of data [duplicate]
One of our stored procedure was using Cursor to update rows. Total rows to be updated varies from 1000 to 2000. In our scenario, this stored procedure was taking 50 minutes to complete the update.
I ...
- 1,425
189
votes
7
answers
213k
views
What's the difference between a CTE and a Temp Table?
What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other?
CTE
WITH cte (Column1, Column2, Column3)
AS
(
SELECT Column1, Column2, ...
- 8,437
17
votes
5
answers
4k
views
Inclusion of ORDER BY on query that returns no rows drastically affects performance
Given a simple three table join, query performance changes drastically when ORDER BY is included even with no rows returned. Actual problem scenario take 30 seconds to return zero rows but is instant ...
- 273
38
votes
2
answers
16k
views
Why is using a table variable more than twice as fast as a #temp table in this specific case?
I was looking at the article here
Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for ...
- 83.2k
11
votes
2
answers
3k
views
Dodgy T-SQL Query Execution driving me crazy
SQL Server 2019. Here's a link to a gist with the xml plan.
Hello, I am having a heck of a time understanding why this query is taking 0.02 seconds to execute when it can't find a record with one of ...
- 111
8
votes
3
answers
16k
views
Using a CTE in IF EXISTS Query
Is it possible to do something similar to the following in SQL Server 2012?
IF EXISTS (
WITH DATA AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY column ORDER BY Column) AS rn
...
- 245
18
votes
2
answers
6k
views
Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
I am trying to understand why using a table variable is preventing the optimizer from using an index seek and then bookmark lookup versus an index scan.
Populating the table:
CREATE TABLE dbo.Test
...
- 2,619
12
votes
2
answers
601
views
@table_variable or #temp_table
I have a big user defined table type variable that has 129 columns. I will store around 2000-3000 records in this table variable at a time and pass it to various stored procedures and functions to get ...
- 223
2
votes
2
answers
8k
views
Why is using a CTE so much faster than using a #Temp Table in this query?
I have 2 different queries for identifying dups (below). The only difference between the 2 queries is that one uses a CTE and the other uses a #Temp table.
Does anyone know why the CTE is so much ...
- 3,245
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
9k
views
Massive joins Vs Updating temp table
I'm working on a query that essentially aggregates a slew of tables together.
The first method I tried was to inner join all of the tables into one nice big query that pretty much looked like this:
...
- 207
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
4
votes
1
answer
4k
views
Efficiently bulk upsert unrelated rows
As part of a manual replication process between databases with different but related schemas, for each table, we identify new and modified records in the source database and feed them via a table ...
- 143
8
votes
2
answers
344
views
Most efficient way to generate a diff
I have a table in SQL server that looks like this:
Id |Version |Name |date |fieldA |fieldB ..|fieldZ
1 |1 |Foo |20120101|23 | ..|25334123
2 |2 |Foo ...
- 83