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:
- Open a creation wizard for an index. For details, see Opening an Object Wizard.
- 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.
- Finally, use the Execute button to create the object.
Contents
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:
|
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.