SQL Server 2008 R2 : Dropping Indexes, Online Indexing Operations, Indexes on Views

9/18/2012 1:19:15 AM

Dropping Indexes

You can drop indexes by using T-SQL or via tools in the SSMS. To drop indexes with T-SQL, you use the DROP INDEX command, a simple example of which follows:

DROP INDEX [IX_WorkOrder_ScrapReasonID] ON [Production].[WorkOrder]

This command drops the index named IX_WorkOrder_ScrapReasonID on the Production.WorkOrder table.

Using the Object Explorer in SSMS is the simplest alternative for dropping indexes. In the Object Explorer, you simply right-click the index you want to drop and then select Delete. The same execution options available for adding and modifying indexes are also available after you select Delete. This includes the option to script the T-SQL statements like that shown in the preceding DROP INDEX example.


If you drop a clustered index on a table, SQL Server needs to rebuild all the remaining nonclustered indexes on the table. The reason is that when a clustered index exists on a table, the nonclustered indexes include the clustered index key in the nonclustered index rows as a pointer to the corresponding data rows. When the clustered index is dropped, the clustered index key needs to be replaced with page and row pointers. If a large number of nonclustered indexes exist on the table, the operation to rebuild the nonclustered indexes can be very time consuming and I/O intensive. 

Online Indexing Operations

One of the great features available with SQL Server 2008 is online indexing. This feature, available only with the Enterprise or Developer Edition, allows you to create, rebuild, or drop indexes without having exclusive access to the index or table. This means that users can have concurrent access to the underlying tables and indexes while the index operation is in progress. This bodes well for high-availability applications and databases that have limited downtime available for offline operations.

Following is an example of the T-SQL syntax for an online index operation:

ALTER INDEX [PK_Product_ProductID] ON [Production].[Product]

The ONLINE = ON parameter is the key to making the index operation an online operation.

To accomplish online indexing, SQL Server must maintain the old and new versions of the affected indexes during the operation. The old version (referred to as the source) includes any table or indexes that are affected by the index operation. For example, if a clustered index is part of the online operation, the clustered index and all the nonclustered indexes that reference the clustered index are maintained as part of the source. The new version (referred to as the target) is the new index or indexes that are being created or rebuilt. In the case of a table without a clustered index, a structure known as a heap is used as the source and target.

During online index operations, the following three phases occur:

  • Preparation— Concurrent activity is temporarily suspended while a snapshot of the source index structure is taken and written as an empty structure to the target.

  • Building— The source index structures are scanned, sorted, merged, and inserted into the target. User SELECT statements are satisfied via the source. Insertions, updates, and deletions to the affected table are written to both the source and target.

  • Final— Concurrent activity is temporarily suspended while the source is replaced by the newly created structures (target).

When the final phase is complete, all the query and update plans that were using the old structures are invalidated. Future queries utilize the newly created index structures after this point.

When considering online indexing, you need to account for the following:

  • Disk space— Generally, the disk space requirements for online operations are the same as those for offline operations. The exception to this is online index operations on clustered indexes. These operations use a temporary mapping index that requires additional disk space. The temporary mapping index contains one row for each record in the table.

  • Performance— Online index operations are generally slower and take more system resources than offline operations. Primarily, the reason is that the old and new index structures are maintained during the index operation. Heavy updates to the tables involved in the index operation can cause an overall decrease in performance and a spike in CPU utilization and I/O as the two index structures are maintained.

  • Transaction log— Online index operations are fully logged. You may therefore encounter a heavy burden on your transaction log during online index operations for large tables. This can cause your transaction log to fill quickly. The transaction log can be backed up, but it cannot be truncated during online index operations. You need to make sure you have enough space for your log to grow; otherwise, the online index operation could fail.

Indexes on Views

SQL Server 2008 supports the creation of indexes on views. Like indexes on tables, indexes on views can dramatically improve the performance of the queries that reference the views. By nature, a view is a virtual table and does not have a separate data structure as does a table, even though it can be referenced like a table. After an index is created on a view, the result set of the view is stored in the database, just as it would be for a table. The indexed view is no longer virtual because it requires maintenance as rows are added to, deleted from, or modified in the tables referenced by the view. 

The first index created on a view must be a unique clustered index. After that is created, other nonclustered indexes can be built on the view for additional performance gains.

The most difficult part of the index creation process is identifying a view that is valid for index creation. Many requirements must be met for a view to qualify. The following is a partial list of the most common requirements:

  • All the tables in the view must be in the same database as the view and have the same owner as the view.

  • The view must not reference any other views.

  • The view must be created with SCHEMABINDING, and any function referenced in the view must also be created with SCHEMABINDING.

  • A two-part name with the schema prefix must be used for every table or user-defined function referenced in the view.


  • Any functions referenced in the view must be deterministic.

  • Views with aggregate functions must also include COUNT_BIG(*).

The following example shows the creation of a view that can have an index created on it:

CREATE VIEW titleview
      select title, au_ord, au_lname, price, ytd_sales, pub_id
        from dbo.authors, dbo.titles, dbo.titleauthor
        where authors.au_id = titleauthor.au_id
          AND titles.title_id = titleauthor.title_id

The SCHEMABINDING clause and database schema qualifier (dbo) for each table are necessary in the view definition to be able to make the view valid for index creation. The following example creates an index on the titleview view:

CREATE UNIQUE CLUSTERED INDEX [AK_vw_Employee] ON [dbo].[vw_Employee]
( [JobTitle] ASC, [LoginID] ASC)

After the index is created, you can manage it in much the same way that you manage the indexes on tables. You can use both T-SQL and SSMS to manage these indexes.

  •  SQL Server 2008 R2 : Managing Indexes - Managing Indexes with T-SQL, Managing Indexes with SSMS
  •  SQL Server 2005 : Advanced OLAP - Advanced Dimensions and Measures (part 3)
  •  SQL Server 2005 : Advanced OLAP - Advanced Dimensions and Measures (part 2) - Parent-Child Dimensions
  •  SQL Server 2005 : Advanced OLAP - Advanced Dimensions and Measures (part 1)
  •  Microsoft Systems Management Server 2003 : Maintaining the Database Through Microsoft SQL Server - Database Maintenance
  •  Microsoft Systems Management Server 2003 : Maintaining the Database Through Microsoft SQL Server - SQL Server Components
  •  Microsoft Visual Basic 2008 : Processing and Storing Data in SQL Server 2005 - Optimizing the LINQSQL Class
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 6) - Disconnected Data Access
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 5) - Direct Data Access - Updating Data
  •  ASP.NET 4 in VB 2010 : ADO.NET Fundamentals (part 4) - Direct Data Access - The DataReader
    Video tutorials
    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Install Windows Server 2012 On VirtualBox

    - How To Disable Windows 8 Metro UI

    - How To Install Windows Store Apps From Windows 8 Classic Desktop

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
    programming4us programming4us