Designing and Tuning the Indexes - Pro SQL Server Internals - page 130

The execution time on my computer and number of reads are shown in Table 6-1 . Figure 6-3 shows execution
plans for both queries.

Table 6-1. Inserting data into the tables: Execution statistics

Number of Reads Execution Time (ms)

Identity 158,438 173 ms

Uniqueidentifier 181,879 256 ms

Figure 6-3. Inserting data into the tables: Execution plans

As you can see, there is another sort operator in the case of the index on the uniqueidentifier column. SQL Server
sorts randomly generated uniqueidentifier values before the insert, which decreases the performance of the query.
Let's insert another batch of rows in the table and check index fragmentation. The code for doing this is shown in
Listing 6-5. Figure 6-4 shows the results of the queries.

Listing 6-5. Uniqueidentifiers: Inserting rows and checking fragmentation

;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
,IDs(ID) as (select row_number() over (order by (select NULL)) from N5)
insert into dbo.IdentityCI(Val)
select ID from IDs;