Geek Answers Handbook

Compound clustered index versus non-unique clustered index. What is better / worse in this case?

I have a database where all tables include column Site( char(4)) and column PrimaryId( int).

Currently, a clustered index for all tables is a combination of these two columns. Many customers have only one site, so in those cases, I think it definitely makes sense to change the clustered index to include only PrimaryId.

In cases where there are several sites, I wonder if it will be useful to use it PrimaryIdonly as a clustered index? Could having a smaller clustered index get better performance than with a unique one?

In case it matters, there will usually not be more than a few sites. 10 sites will be many.

+4
source share
2 answers

The answer is simple. A UNIQUE index is always better than NON-UNIQUE. There is math behind this, but the greater uniqueness lies in the fact that a faster server can search for a record from the index.

The CLUSTERED index is excellent, as they physically organize disk writes, and it is always useful to use CLUSTERED INDEX for UNIQUE keys.

CLUSTER INDEX with PRIMARY KEY gives very good performance with big data. If your data is not high in the column, this is not a big deal.

+6
source

, . , , , .

( ):

  • -
  • -

RIP, :

file number - page number - row number

:

enter image description here

, RIP .

, ; , . -: , , . , , ; .

, :

( ):

SQL Server , . , , SQL Server , , " " . SQL Server . , :

  • - . SQL Server , , ; , , uniquifier

  • Uniquifier - ; , . .

.

+2

More articles:


All Articles