Tables and Indexes: Internal Structure and Access Methods - Pro SQL Server Internals - page 45

■ this is a very important point to remember. nonclustered indexes do not store information about physical row
location when a table has a clustered index. they store the value of the clustered index key instead.

Note

Like clustered indexes, the intermediate and root levels of nonclustered indexes store one row per page from the
level they reference. That row consists of the physical address and the minimum value of the key from the page. In
addition, for non-unique indexes, it also stores the row-id of such a row.

■ It is important to define a nonclustered index as unique when the data is unique. Intermediate and root
level rows of unique indexes are more compact because sQl server does not maintain the row-id there. Moreover,
the uniqueness of the index helps Query optimizer generate more efficient execution plans.

Note

The size of the nonclustered index key cannot exceed 900 bytes. SQL Server allows creating indexes with a key
size that can potentially exceed this limit due to variable-length columns, although you would not be able to insert
such rows into a table. Listing 2-13 shows an example of this.

Listing 2-13. 900 bytes limitation on the index key size

create table dbo.LargeKeys
(
Col1 varchar(500) not null,
Col2 varchar(1000) not null
);

-- Success with the warining
create nonclustered index IDX_NCI on dbo.LargeKeys(Col1,Col2);

Warning:
Warning! The maximum key length is 900 bytes. The index 'IDX_NCI' has maximum length of 1500 bytes.
For some combination of large values, the insert/update operation will fail.

-- Success:
insert into dbo.LargeKeys(Col1, Col2) values('Small','Small');

-- Failure:
insert into dbo.LargeKeys(Col1, Col2) values(replicate('A',500),replicate('B',500));

Error:
Msg 1946, Level 16, State 3, Line 4
Operation failed. The index entry of length 1000 bytes for the index 'IDX_NCI' exceeds the maximum
length of 900 bytes.

Let's look at how SQL Server uses nonclustered indexes assuming that you run the select shown in Listing 2-14.

Listing 2-14. Selecting customers with Name equal Boris

select * from dbo.Customers where Name = 'Boris'

As shown in the first step in Figure 2-18 , SQL Server starts with the root page of the nonclustered index.
The key value Boris is less than Dan, and SQL Server goes to the intermediate page referenced from the first row
in the root-level page.