Indexes Wizard (SQL Server)

Go Up to Microsoft SQL Server Object Wizards

Like an index in a book, a table index helps you get at the data you want without having to read through the whole table. Indexes can exist on single column or on multiple columns. Indexes appear in the form of B-trees. And, as for books, you can have multiple indexes for a single table. You can also create indexes for a view.

To create a new index using a wizard:

1. Open a creation wizard for an index. For details, see Opening an Object Wizard.
2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
   • Properties panel - Indexes (SQL Server) - Properties.
   • Columns panel - Indexes (SQL Server) - Columns.
   • Data Compression panel - Indexes (SQL Server) - Data Compression.
   • Spatial panel - Indexes (SQL Server) - Spatial.
   • DDL View panel - for details, see Previewing the DDL Generated to Create the New Object.
3. Finally, use the Execute button to create the object.

Indexes (SQL Server) - Properties

When creating or editing an index, this tab/panel lets you work with the following settings:

Setting	Description
Parent Type	Select TABLE or VIEW.
Parent Owner	Select the owner of the table or view.
Parent Name	Select the specific table or view containing the columns you want to index.
Name	Provide a name for the index.
Build Online	Enabling this feature specifies that the ONLINE=ON clause is used when creating this object and can subsequently be used when rebuilding or dropping this object.
Max degree of parallelism	Lets you specify a MAXDOP index operation value, limiting the number of processors used in parallel plan execution.
Index Type	Select: NONUNIQUE. UNIQUE. An index is unique when no two rows are permitted to have the same index value. A clustered index on a view must be unique. If an INSERT or UPDATE statement creates a duplicate value, the operation may fail. XML. A primary XML index indexes and stores all parts of the XML data from the chosen column. An XML index requires you to have a clustered index on your table, so that the XML index can tie in with the table rows. SPATIAL. A spatial index is a type of extended index that allows you to index a spatial column. A spatial column is a table column that contains data of a spatial data type, such as geometry or geography. Note that a spatial table with a clustered primary key along with spatial columns of the GEOMETRY or GEOGRAPHY type is mandatory in order to create a spatial index. COLUMNSTORE. A columnstore index is a technology for storing, retrieving, and managing data by using a columnar data format called a columnstore.
Clustered	A clustered index is one in which the physical order of rows matches the order of indexed rows. A table or view can only have one clustered index at a time. In a nonclustered index, the physical order of rows is independent of the indexed order of rows. For an indexed view, you can only create a nonclustered index if there is already a clustered index extant. With this check box enabled, subsequent rebuild and drop operations offer an online option.
Ignore Duplicate Key	This option controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index. If the option is selected and an INSERT statement that creates a duplicate key is executed, SQL Server issues a warning and ignores the duplicate row. If not selected, SQL Server issues an error message and rolls back the entire INSERT statement.
Statistics Recompute	Enabling this feature means queries involving the table run at the optimal level as distribution statistics are updated automatically when the index is created. If you disable this option, you can compromise query performance.
Partitioned	When selected, an ON clause is added to the CREATE TABLE statement, letting you specify a Partition Scheme, partitioning this table.
Partition Scheme	This property is only available if the Partitioned check box is selected. Select the partition scheme that specifies the filegroup mapping for this table. For information on creating partition schemes, see Partition Schemes Wizard (SQL Server).
Partition Column	This property is only available if the Partitioned check box is selected. Select the column that the index will be partitioned against.
Filegroup	This property is only available if the Partitioned check box is not selected. Select an existing, named filegroup to have the index stored on the specified filegroup. Select PRIMARY to have the index stored on the default database filegroup.
Fill Factor	Specifies the percentage that the Database Engine should make the leaf level of each index page when the index is created or rebuilt.
Pad Index	Enable or disable padding of index pages.
Sort in TempDB	Select to store the intermediate index sort results in tempdb. This option may reduce the time needed to create an index if tempdb is on a different set of disks than the user database, but it increases the amount of disk space used to create an index. In addition to the space required in the user database to create the index, tempdb must have about the same amount of additional space to hold the intermediate sort results.
Allow Row Locks and Allow Page Locks	Lets you enable locking granularity at the page and row level (Default=TRUE) NOTE: You cannot reorganize an index (primary key, or unique key) that has an Allow Page Locks property set to FALSE. For information on reorganizing indexes, see Reorganize (SQL Server indexes, primary keys, and unique keys).

Indexes (SQL Server) - Columns

From the Column dropdown, select a column for the index and specify a Sort option. To add more columns, click the New button and then follow the steps in the last instruction. Use the Delete button to drop columns.

Indexes (SQL Server) - Data Compression

Right-click the partition line to change the data compression type and reduce the size of your database. Select Change Compression Type, and then select whether you want to compress to ROW, PAGE, or NONE. Note that page compression automatically implements row compression. You can compress tables and indexes when they are created by using the CREATE TABLE and CREATE INDEX statements.

If you are using a columnstore index, selections for the compression type are COLUMNSTORE or COLUMNSTORE ARCHIVE. To designate a columnstore index, select COLUMNSTORE from the Index Type list in the index properties. A clustered index is designated by the selection of the Clustered checkbox. Leave the checkbo blank when usign a non-clustered columnstore index.

Indexes (SQL Server) - Spatial

When GEOMETRY is the selected spatial index type, select a Tessellation Scheme of GEOMETRY_GRID or GEOMETRY_AUTO_GRID.

When GEOGRAPHY is the selected spatial index type, select a Tessellation Scheme of GEOGRAPHY_GRID or GEOGRAPHY_AUTO_GRID.