Database Reference
In-Depth Information
Indexes on uniqueidentifier columns generated with the
NEWSEQUENTIALID()
function are similar to indexes on
identity and sequence columns. They are
generally
ever-increasing, even though SQL Server resets their base value
from time to time.
Using
NEWSEQUENTIALID()
, however, defeats the randomness of the values. It is possible to guess the next value
returned by that function, and you should not use it when security is a concern.
Nonclustered Indexes Design Considerations
It is hard to find the tipping point when joining multiple nonclustered indexes is more efficient than single
nonclustered index seek and key lookup operations. When index selectivity is high and SQL Server estimates a small
number of rows returned by the Index Seek operation, the Key Lookup cost would be relatively low. In such cases,
there is no reason to use another nonclustered index. Alternatively, when index selectivity is low, Index Seek returns a
large number of rows and SQL Server would not use it because it is not efficient.
Let's look at an example where we will create a table and populate it with 1,048,576 rows.
Col1
column stores 50
different values in the column,
Col2
stores 150 values, and
Col3
stores 200 values respectively. Finally, we will create
three different nonclustered indexes on the table. The code for doing this is shown in Listing 6-8.
Listing 6-8.
Multiple nonclustered indexes: Table creation
create table dbo.IndexIntersection
(
Id int not null,
Placeholder char(100),
Col1 int not null,
Col2 int not null,
Col3 int not null
);
create unique clustered index IDX_IndexIntersection_ID
on dbo.IndexIntersection(ID);
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 CROSS JOIN N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 CROSS JOIN N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 CROSS JOIN N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 CROSS JOIN N4 as T2) -- 65,536 rows
,N6(C) as (select 0 from N3 as T1 CROSS JOIN N5 as T2) -- 1,048,576 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N6)
insert into dbo.IndexIntersection(ID, Col1, Col2, Col3)
select ID, ID % 50, ID % 150, ID % 200
from IDs;
create nonclustered index IDX_IndexIntersection_Col1
on dbo.IndexIntersection(Col1);
create nonclustered index IDX_IndexIntersection_Col2
on dbo.IndexIntersection(Col2);
create nonclustered index IDX_IndexIntersection_Col3
on dbo.IndexIntersection(Col3);