Lesson 1: Index Architecture
Quick Check
1. What type of structure does SQL Server use to construct an index?
2. What are the three types of pages within an index?
Quick Check Answers
1. SQL Server uses a B-tree structure for indexes.
2. An index can contain root, intermediate, and leaf pages. An index has a single
root page defi ned at the top of the index structure. An index can have one or
more levels of intermediate pages, but it is optional. The leaf pages are the
lowest-level page within an index.
Lesson Summary
SQL Server creates an index using a B-tree structure .
Each index has a single root-level page and if all the entries do not fi t on a single page,
the index can create pages at intermediate and leaf levels.
Lesson 2: Designing Indexes
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON <object> ( column [ ASC | DESC ] [ ,...n ] ) [ INCLUDE ( column_name [ ,...n ] ) ] [ WHERE <filter_predicate> ] [ WITH ( <relational_index_option> [ ,...n ] ) ] [ ON { partition_scheme_name ( column_name ) | filegroup_name | default } ] [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ][ ; ]
CREATE [ PRIMARY ] XML INDEX index_name ON <object> ( xml_column_name ) [ USING XML INDEX xml_index_name [ FOR { VALUE | PATH | PROPERTY } ] ] [ WITH ( <xml_index_option> [ ,...n ] ) ][ ; ]
CREATE SPATIAL INDEX index_name ON <object> ( spatial_column_name ) {[ USING <geometry_grid_tessellation> ] WITH ( <bounding_box> [ [,] <tessellation_parameters> [ ,...n ] ] [ [,] <spatial_index_option> [ ,...n ] ] ) | [ USING <geography_grid_tessellation> ] [ WITH ( [ <tessellation_parameters> [ ,...n ] ] [ [,] <spatial_index_option> [ ,...n ] ] ) ] } [ ON { filegroup_name | "default" } ];
Quick Check
1. What is the difference between a clustered and a nonclustered index?
2. How does the FILLFACTOR option affect the way an index is built?
Quick Check Answers
1. A clustered index imposes a sort order on the data pages in the table. A nonclustered index does not impose a sort order.
2. The FILLFACTOR option reserves space on the intermediate and leaf levels of the index.
CREATE NONCLUSTERED INDEX idx_city ON Person.Address(City) INCLUDE (AddressLine1)
CREATE NONCLUSTERED INDEX idx_city2 ON Person.Address(City) INCLUDE (AddressLine1, AddressLine2) WHERE AddressLine2 IS NOT NULL
CREATE SPATIAL INDEX sidx_spatiallocation ON Person.Address(SpatialLocation) USING GEOGRAPHY_GRID WITH (GRIDS = (MEDIUM, LOW, MEDIUM, HIGH ), CELLS_PER_OBJECT = 64)
Lesson Summary
Clustered indexes specify a sort order for data pages in a table.
You can create up to 1,000 nonclustered indexes on a table.
Nonclustered indexes can include columns in the leaf level of the index to cover more
queries.
You can specify a WHERE clause for a nonclustered index to limit the data set that the
index is built upon.
You can create three different types of XML indexes; PATH, VALUE, and PROPERTY.
Spatial indexes can be defi ned for either geography or geometry data types.
If you are indexing a geometry data type, the BOUNDING_BOX parameter is required
to provide limits to the two-dimensional plane.
Lesson 3: Maintaining Indexes
The FILLFACTOR option for an index determines the percentage of free space that is reserved
on each leaf-level page of the index when an index is created or rebuilt.
ALTER INDEX { index_name | ALL } ON <object> { REBUILD [ [ WITH ( <rebuild_index_option> [ ,...n ] ) ] | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> [ ,...n ] )] ] ] | DISABLE | REORGANIZE [ PARTITION = partition_number ] [ WITH ( LOB_COMPACTION = { ON | OFF } ) ] | SET ( <set_index_option> [ ,...n ] ) }[ ; ]
ALTER INDEX { index_name | ALL } ON <object> DISABLE [ ; ]
ALTER INDEX { index_name | ALL } ON <object> REBUILD [ ; ]
Quick Check
1. What is the difference between the REBUILD and REORGANIZE options of ALTER
2. What happens when an index is disabled?
Quick Check Answers
1. REBUILD defragments all levels of an index. REORGANIZE defragments only the
leaf level of the index.
2. An index that is disabled is no longer used by the optimizer. In addition, as data
changes in the table, any disabled index is not maintained.
ALTER INDEX ALL ON Person.Address REBUILD ALTER INDEX IX_Person_LastName_FirstName_MiddleName ON Person.Person REORGANIZE ALTER INDEX PK_Address_AddressID ON Person.Address DISABLE SELECT * FROM Person.Address ALTER INDEX PK_Address_AddressID ON Person.Address REBUILD SELECT * FROM Person.Address
Lesson Summary
You can defragment indexes using either the REBUILD or REORGANIZE options.
The REBUILD option defragments all levels of an index. Unless the ONLINE option is
specifi ed, a REBUILD acquires a shared table lock and block any data modifi cations.
The REORGANIZE option defragments only the leaf level of an index and does not
cause blocking.
You can disable an index to exclude the index from consideration by the optimizer
or any index maintenance due to data changes. If the clustered index is disabled, the
entire table becomes inaccessible.