--- PDF PAGE 267 ---
250
Listing 8-1.  Creating a Clustered Index
--Create Chapter8 Database
CREATE DATABASE Chapter8
 ON  PRIMARY
( NAME = N'Chapter8', FILENAME =
    N'F:\Program Files\Microsoft SQL Server\MSSQL15.PROSQLADMIN\MSSQL\DATAChapter8.mdf'),
 FILEGROUP [MEM] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
( NAME = N'MEM', FILENAME = N'H:\DATA\CH08')
 LOG ON
( NAME = N'Chapter8_log', FILENAME =
    N'E:\Program Files\Microsoft SQL Server\MSSQL15.PROSQLADMIN\MSSQL\DATAChapter8_log.ldf') ;
GO
USE Chapter8
GO
--Create CIDemo table
CREATE TABLE dbo.CIDemo
(
    ID       INT       IDENTITY,
    DummyText     VARCHAR(30)
) ;
GO
--Create clustered index
CREATE UNIQUE CLUSTERED INDEX CI_CIDemo ON dbo.CIDemo([ID]) ;
GO
When creating an index, you have a number of WITH options that you can specify. 
These options are outlined in Table 8-1.
Chapter 8  Indexes and Statistics

--- PDF PAGE 268 ---
251
Table 8-1.  Clustered Index WITH Options
Option
Description
MAXDOP
Specifies how many cores are used to build the index. Each core that is 
used builds its own portion of the index. The trade-off is that a higher 
MAXOP builds the index faster, but a lower MAXDOP means the index is 
built with less fragmentation.
FILLFACTOR
Specifies how much free space should be left in each page of the leaf 
level of the index. This can help reduce fragmentation caused by inserts 
at the expense of having a wider index, which requires more IO to read. 
For a clustered index, with a nonchanging, ever-increasing key, always 
set this to 0, which means 100% full minus enough space for one row.
PAD_INDEX
Applies the fill factor percentage to the intermediate levels of the B-tree.
STATISTICS_
NORECOMPUTE
Turns on or off the automatic updating of distribution statistics. Statistics 
are discussed later in this chapter.
SORT_IN_TEMPDB
Specifies that the intermediate sort results of the index should be stored 
in TempDB. When you use this option, you can offload IO to the spindles 
hosting TempDB, but this is at the expense of using more disk space. 
Cannot be ON if RESUMABLE is ON.
STATISTICS_
INCREMENTAL
Specifies if statistics should be created per partition. Limitations to this 
are discussed later in this chapter.
DROP_EXISTING
Used to drop and rebuild the existing index with the same name.
IGNORE_DUP_KEY
When you enable this option, an INSERT statement that tries to insert a 
duplicate key value into a unique index will not fail. Instead, a warning is 
generated and only the rows that break the unique constraint fail.
ONLINE
Can be set as ON or OFF, with a default of OFF. Specifies if the entire 
table and indexes should be locked for the duration of the index build or 
rebuild. If ON, then queries are still able to access the table during the 
operation. This is at the expense of the time it takes to build the index. 
For clustered indexes, this option is not available if the table contains 
LOB data.*
(continued)
Chapter 8  Indexes and Statistics

--- PDF PAGE 269 ---
252
As mentioned earlier in this chapter, if you create a primary key on a table, then 
unless you specify the NONCLUSTERED keyword, or a clustered index already exists, a 
clustered index is created automatically to cover the column(s) of the primary key. Also, 
remember that at times you may wish to move the clustered index to a more suitable 
column if the primary key is wide or if it is not ever-increasing.
In order to achieve this, you need to drop the primary key constraint and then re-­
create it using the NONCLUSTERED keyword. This forces SQL Server to cover the primary 
key with a unique nonclustered index. Once this is complete, you are able to create the 
clustered index on the column of your choosing.
If you need to remove a clustered index that is not covering a primary key, you can 
do so by using the DROP INDEX statement, as demonstrated in Listing 8-2, which drops 
the clustered index that we created in the previous example.
Listing 8-2.  Dropping the Index
DROP INDEX CI_CIDemo ON dbo.CIDemo ;
Table 8-1.  (continued)
Option
Description
OPTIMIZE_FOR_
SEQUENTIAL_KEY
Optimizes high concurrency inserts, where the index key is sequential. 
Introduced in SQL Server 2019, this feature is designed for indexes that 
suffer from last-page insert contention.
RESUMABLE
Can be set as ON or OFF, with a default of OFF. Specifies if the index 
creation or build can be paused and resumed or can be resumed after a 
failure. Can only be set to ON if ONLINE is set to ON.
MAX_DURATION
Specifies, in minutes, the maximum duration that an index rebuild or 
rebuild will execute for, before pausing. Can only be specified if ONLINE 
is set to ON and RESUMABLE is set to ON.
ALLOW_ROW_LOCKS
Specifies that you can take row locks out when accessing the table. This 
does not mean that they definitely will be taken.
ALLOW_PAGE_LOCKS
Specifies that you can take page locks out when accessing the table. 
This does not mean that they definitely will be taken.
*Spatial data is regarded as LOB data.
Chapter 8  Indexes and Statistics