New answers tagged

1 vote

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

In facts table variables are close to the concept of ARRAYs that does not exists in SQL Server. Temp table triggered a recompilation every time there is a DDL statement (CREATE, ALTER, DROP) and when ...
SQLpro's user avatar
  • 493
-2 votes

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

Generally many factors affects on your query performance and i try my best to mention some of that and link to source for more explanation : Primery Key when we create primery key we create ...
Sayadian's user avatar
0 votes

Inserting a Hyphen in between a composite primary key

You misunderstand how composite keys work. A composite key looks at each column individually, not concatenated. This is all you need to enforce integrity CREATE TABLE AssetPair ( older_asset_id ...
Charlieface's user avatar
  • 12.2k
0 votes

Inserting a Hyphen in between a composite primary key

You could use a computed column based on the two id fields. create table asset_pair ( oldid ...
jhatcher9999's user avatar
-4 votes

General approach for creating updated records with identical primary keys while preserving existing records

create table existing_table (id int primary key, val data) create table custom_table (id int primary key, val data) select e.id, case c.id when null then e.val else c.val end as val from ...
SergeyA's user avatar
  • 433
0 votes

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

Table Variable : Suitable for small dataset. Temp Table : Suitable big dataset. Table variable performance are better when there is small dataset.When dataset is big then table variable do not perform ...
KumarHarsh's user avatar
  • 1,628
1 vote

Effect on execution plans when a table variable has a primary key

revival I forget what I was doing in 2017 when this was originally asked, but I figured I'd throw a couple examples in of when indexing a table variable may provide some benefit. There are many ...
Erik Darling's user avatar
  • 40.5k
1 vote

SQL Server using table's Primary Key as part of Foreign Key

When you add the constraints required for identification purposes and row uniqueness, your model is something like this: If you had not shot yourself in the foot by using surrogates as the first ...
Giannis Tsimarakis's user avatar

Top 50 recent answers are included