SQL Server Identity Primary Key vs no Identity performance - Using Formula in Primary Key

Are there performance differences between Identity

primary key and normal primary keys?

Actually, I want to create a table that can fill over 5 million rows. The table should return queries with a filter condition of more than 4 columns in less than half a second.

Since these 4 columns (all primary keys from other tables) are numeric and their range is limited, I decided to mix all 4 columns in the primary key.

Column ranges can be, for example, col1=500 | col2=500 | col3=900,000 | col4=9,000,000

and range bigint

is 9,223,372,036,854,775,807, so if I would like to concatenate all 4 columns into a primary key bigint

, it can provide it.

Are there any problems with this solution?

+3


source to share


2 answers


Both solutions are the same:

1) Component PK

with 4 columns

2) Column A BIGINT

(regardless of identity or not), whichPK



The Sql back-end mechanism for indexing is the same for both: the data is sorted and stored in place. You can test both ways with this Statistics

:

SET STATISTICS IO ON
SET STATISTICS TIME ON

      

I tested it and those were the same.

0


source


Plunging my finger into deep waters:

Constraints (such as primary and foreign keys) affect results more than performance; it is the layout and structure of your underlying indexes, which will usually have a larger impact on how well a given query will perform. Of course, applying a PRIMARY KEY to a table will create a UNIQUE index on that table, but that index may or may not be clustered (depending on how and when it was created).



If the PRIMARY KEY is built as a clustered index on an IDENTITY column that is monotonically incremented by definition, then your clustered key will have minimal fragmentation resulting from standard INSERT operations; if you plot it on the other four columns and the data is inserted non-monotonically, then you may see significant fragmentation over time, which can lead to performance issues. However, if your data will always be inserted in sequential order, this may not be a problem.

You mentioned filtering, but what about joining?

+3


source







All Articles