Statistics - Pro SQL Server Internals - page 56

■ in the case of composite indexes, when all columns from the index are used as predicates in all queries, it is
beneficial to define a column with lower density/higher selectivity as the leftmost column of the index. this will allow SQL
Server to better utilize the data distribution information from the statistics.

Tip

You should consider the SarGability of the predicates, however. For example, if all queries are using FirstName=@FirstName
and LastName=@LastName predicates in the where clause, it is better to have LastName as the leftmost column in the
index. Nonetheless, this is not the case for predicates like FirstName=@FirstName and LastName<>@LastName , where
LastName is not SarGable.

index selectivity is a metric, which is the opposite of density. it is calculated based on the formula 1 / density . the more
unique values a column has, the higher its selectivity.

■ We will talk about index design considerations in greater detail in Chapter 6, “Designing and tuning
the indexes.”

Note

Column-Level Statistics
In addition to index-level statistics, you can create separate column-level statistics. Moreover, in some cases,
SQL Server creates such statistics automatically.
Let's take a look at an example and create a table and populate it with the data shown in Listing 3-3.

Listing 3-3. Column-level statistics: Table creation

create table dbo.Customers
(
CustomerId int not null identity(1,1),
FirstName nvarchar(64) not null,
LastName nvarchar(128) not null,
Phone varchar(32) null,
Placeholder char(200) null
);

create unique clustered index IDX_Customers_CustomerId
on dbo.Customers(CustomerId)
go

-- Inserting cross-joined data for all first and last names 50 times
-- using GO 50 command in Management Studio
;with FirstNames(FirstName)
as
(
select Names.Name
from
(
values('Andrew'),('Andy'),('Anton'),('Ashley'),('Boris'),
('Brian'),('Cristopher'),('Cathy'),('Daniel'),('Donny'),