sql server - SQL: Add Primary Key to Non-Unique Index -

sql server - SQL: Add Primary Key to Non-Unique Index -

let's query filtering on 2 fields , returning primary key values.

select rowidentifier table qualifiera = 'examplea' , qualifierb = 'exampleb'

assuming clustered index not primarykey non-unique index contains qualifiera , qualiferb best served via add-on of rowidentifier(scenario & scenario b). or more appropriate include it(scenario c)?

scenario a: non-unique, non-clustered

create nonclustered index ix_table_qualifiera on [dbo].[table] ([qualifiera],[qualifierb],[rowidentifier])

scenario b: unique, non-clustered

create unique nonclustered index ix_table_qualifiera on [dbo].[table] ([qualifiera],[qualifierb],[rowidentifier])

scenario c:

create nonclustered index ix_table_qualifiera on [dbo].[table] ([qualifiera],[qualifierb]) include ([rowidentifier])

finally i'm assuming if primarykey clustered index neither necessary, accurate?

if there clustered index, automatically included in indexes on table. can explicitly include not required.

the unique index enforces uniqueness. pk should have constraint. not need re-enforce in every index.

if including pk in clause, utilize pk index find row because guaranteed homecoming fewest results, including in index gains nil lookups. potentially skew cardinality engine , create sql think index more distinct is.

for above reasons, select alternative c

create nonclustered index ix_table_qualifiera on [dbo].[table] ([qualifiera],[qualifierb]) include ([rowidentifier])

i utilize regardless of column clustered. give performance, insure index go on perform regardless of clustered index, , create explicit index used for.

sql sql-server indexing