Indexes

An index is a database object that speed up data retrieval from a table or view by providing quick access to the data.

Database Explorer allows you to perform operations on indexes using the shortcut menu. Right-click a index in Database Explorer to access commands for specific tasks, such as creating, editing, deleting, or rebuilding indexes, or generating scripts for them.

Create an index

1. In Database Explorer, right-click the Indexes node or the index and select New Index, then select an index type:

• Clustered Index
• Nonclustered Index
• Primary XML Index
• Secondary XML Index
• Spatial Index
• Clustered Columnstore Index
• Nonclustered Columnstore Index

2. On the Indexes tab of Table Editor, configure the index properties.

3. Click Apply Changes.

Edit an index

1. In Database Explorer, right-click the index you want to edit and select Edit Index.

2. On the Indexes tab of Table Editor, update the index properties.

3. Click Apply Changes.

Rebuild indexes

1. In Database Explorer, right-click the index and select Rebuild Index.

Alternatively, right-click the Indexes node and select Rebuild All Indexes.

2. In the Rebuild Indexes dialog, configure index properties:

• Indexes grid – Select the indexes you want to rebuild.
  • Object Name – The system-generated name of the index.
  • Table Name – The table to which the index belongs.
  • Index Type – The type of the index.
  • Total Fragmentation – The percentage of fragmentation detected, for example, 0 means no fragmentation.
  • Result – The outcome of the rebuild process after it completes.

3. Click OK.

Reorganize indexes

1. In Database Explorer, right-click the index and select Reorganize Index.

Alternatively, right-click the Indexes node and select Reorganize All Indexes.

2. In the Reorganize Indexes dialog, configure index properties:

• Compact large object column data – Select the checkbox to compact storage used by large object (LOB) columns, such as VARCHAR(MAX), TEXT, IMAGE, NVARCHAR(MAX).
• Indexes grid – Select the indexes you want to reorganize.
  • Object Name – The system-generated name of the index.
  • Table Name – The table to which the index belongs.
  • Index Type – The type of the index.
  • Total Fragmentation – The percentage of fragmentation detected, for example, 0 means no fragmentation.
  • Result – The outcome of the reorganization after it completes.

3. Click OK.

Turn on the index

To turn on the index, in Database Explorer, right-click the index and select Rebuild Index.

Turn off the index

1. In Database Explorer, right-click the index and select Disable Index.

Alternatively, right-click the Indexes node and select Disable All Indexes.

2. In the Disable Indexes dialog, configure index properties:

• Indexes grid – Select the indexes you want to disable.
  • Object Name – The system-generated name of the index.
  • Table Name – The table to which the index belongs.
  • Index Type – The type of the index.
  • Result – The outcome of the disabling process after it completes.

3. Click OK.

Analyze indexes

1. In Database Explorer, right-click the Indexes node or an index and select Manage index fragmentation.

Note

The fragmentation settings are available only for clustered and nonclustered indexes.

2. In the Index Manager document, select indexes you want to analyze.

3. Click Reanalyze.

Generate a script for an index

1. In Database Explorer, right-click the required index and select Generate Script As, then select the statement type.

2. Select the output option:

• To New SQL Window – Opens the script in a new SQL document.
• To File – Generates the script to a .sql file.
• To Clipboard – Copies the script to the clipboard.

For more information, see Generate the DDL and DML statements for database objects.

Rename an index

1. In Database Explorer, right-click the index you want to rename and select Rename, or press F2.

2. Enter the new index name.

3. Click Yes to confirm the renaming of the index.

View index properties

View index properties in one of these ways:

• Double-click the required index.
• In Database Explorer, right-click the index and select Show Details.

Object Viewer opens and provides detailed information about the index.

Delete an index

1. In Database Explorer, right-click the index you want to delete and select Delete, or press Delete.

2. Click Yes to confirm deleting the index, or No to discard the operation.

Refresh the index

To update the index to reflect the latest changes, in Database Explorer, right-click the index or Indexes node and select Refresh, or press F5.

Open the Properties window

To open the Properties window, in Database Explorer, right-click the index and select Properties, or press F4.

Filter indexes

1. In Database Explorer, right-click the Indexes node and select Filter.

2. In the Filter Settings (Indexes) dialog, configure the filter parameters.

3. Click OK to apply the filter.

Note

When you open the Indexes node, the number of objects it contains appears in parentheses next to the node name. This number changes based on your filter settings.

Shortcut menu options for a specific index

The following table describes the shortcut menu options, which appear when you right-click a specific index in Database Explorer.

Name	Description
Edit Index	Opens the Indexes tab of Table Editor to modify an existing index.
New Index > Clustered Index	Opens the Indexes tab of Table Editor to create a clustered index.
New Index > Nonclustered Index	Opens the Indexes tab of Table Editor to create a nonclustered index.
New Index > Primary XML Index	Opens the Indexes tab of Table Editor to create a primary XML index.
New Index > Secondary XML Index	Opens the Indexes tab of Table Editor to create a secondary XML index.
New Index > Spatial Index	Opens the Indexes tab of Table Editor to create a spatial index.
New Index > Clustered Columnstore Index	Opens the Indexes tab of Table Editor to create a clustered columnstore index.
New Index > Nonclustered Columnstore Index	Opens the Indexes tab of Table Editor to create a nonclustered columnstore index.
Rebuild Index	Opens the Rebuild Indexes dialog to rebuild the index.
Reorganize Index	Opens the Reorganize Indexes dialog to reorganize the index. Note: The Reorganize Index option is unavailable for the index that was turned off.
Disable Index	Open the Disable Indexes dialog to turn off the index.
Manage Index Fragmentation	Opens the Index Manager document to identify and fix indexes in SQL Server databases.
Show Details	Opens Object Viewer to view object details.
Source Control > Source Control Manager	Opens Source Control Manager, where you can track and manage changes to database schema and static data. When Source Control Manager is already open, this option refreshes it.
Source Control > Commit	Opens Source Control Manager with all local changes (if any) selected and ready to commit. When Source Control Manager is already open, this option refreshes it and selects all local database changes.
Source Control > Get Latest	Opens Source Control Manager with all remote changes (if any) selected, ready to pull using Get Latest. When Source Control Manager is already open, this option refreshes it and selects all remote database changes.
Generate Script As	Generates a script of the selected object using the CREATE, DROP, and DROP and CREATE statements. The script can be saved to a new SQL document or to a file, or copied to the clipboard.
Rename	Allows you to enter a new name for the index.
Delete	Drops the selected object from the database. Shortcut: Delete
Refresh	Updates the object to reflect the latest changes. Shortcut: F5
Properties	Opens the Properties pane to view the object information. Shortcut: F4

Shortcut menu options for the Indexes node

The following table describes the shortcut menu options, which appear when you right-click the Indexes node in Database Explorer.

Name	Description
New Index > Clustered Index	Opens the Indexes tab of Table Editor to create a clustered index.
New Index > Nonclustered Index	Opens the Indexes tab of Table Editor to create a nonclustered index.
New Index > Primary XML Index	Opens the Indexes tab of Table Editor to create a primary XML index.
New Index > Secondary XML Index	Opens the Indexes tab of Table Editor to create a secondary XML index.
New Index > Spatial Index	Opens the Indexes tab of Table Editor to create a spatial index.
New Index > Clustered Columnstore Index	Opens the Indexes tab of Table Editor to create a clustered columnstore index.
New Index > Nonclustered Columnstore Index	Opens the Indexes tab of Table Editor to create a nonclustered columnstore index.
Rebuild All Indexes	Opens the Rebuild Indexes dialog to rebuild all indexes.
Reorganize All Indexes	Opens the Reorganize Indexes dialog to reorganize all indexes.
Disable All Indexes	Opens the Disable Indexes dialog to turn off all indexes.
Manage Index Fragmentation	Opens the Index Manager document to identify and fix indexes in SQL Server databases.
Source Control > Source Control Manager	Opens Source Control Manager, where you can track and manage changes to database schema and static data. When Source Control Manager is already open, this option refreshes it.
Source Control > Commit	Opens Source Control Manager with all local changes (if any) selected and ready to commit. When Source Control Manager is already open, this option refreshes it and selects all local database changes.
Source Control > Get Latest	Opens Source Control Manager with all remote changes (if any) selected, ready to pull using Get Latest. When Source Control Manager is already open, this option refreshes it and selects all remote database changes.
Filter	Opens the Filter Settings dialog to control which indexes should appear in the table in Database Explorer.
Show Details	Opens Object Viewer to view object details.
Refresh	Updates the object to reflect the latest changes. Shortcut: F5

Note

The Source Control shortcut menu is available only after you link the corresponding database to a source-control system.

Bulk operations

In Database Explorer, hold down Ctrl and select multiple indexes. Then right-click the selection and select the required option.

The following table describes the shortcut menu options available for multiple indexes.

Name	Description
Rebuild Index	Opens the Rebuild Indexes dialog to rebuild the selected indexes.
Reorganize Index	Opens the Reorganize Indexes dialog to reorganize the selected indexes.
Disable Index	Opens the Disable Indexes dialog to turn off the selected indexes.
Manage Index Fragmentation	Opens the Index Manager document to identify and fix the selected indexes.
Source Control > Source Control Manager	Opens Source Control Manager, where you can track and manage changes to database schema and static data. When Source Control Manager is already open, this option refreshes it.
Source Control > Commit	Opens Source Control Manager with all local changes (if any) selected and ready to commit. When Source Control Manager is already open, this option refreshes it and selects all local database changes.
Source Control > Get Latest	Opens Source Control Manager with all remote changes (if any) selected, ready to pull using Get Latest. When Source Control Manager is already open, this option refreshes it and selects all remote database changes.
Source Control > Link/Unlink Static Data	Opes the Source Control Link Static Data dialog that displays the tables whose data you want to link.
Generate Script As	Generates a script of the selected objects using the CREATE, DROP, and DROP and CREATE statements. The script can be generated to a new SQL document or to a file, or copied to the clipboard.
Delete	Drops the selected indexes from the database. Shortcut: Delete