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?
source to share
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.
source to share
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?
source to share