Microsoft® SQL Server 2012 Unleashed

Managing Indexes with T-SQL

One of the T-SQL features available with SQL Server 2012 is the ALTER INDEX statement. This statement simplifies many of the tasks associated with managing indexes. Index operations such as index rebuilds and changes to fill factor that were previously handled with DBCC commands are now available via the ALTER INDEX statement. The basic syntax for ALTER INDEX is as follows:

Click here to view code image

ALTER INDEX {index_name | ALL}   ON [{database_name.[schema_name]. | schema_name.}]     {table_or_view_name}   { REBUILD [WITH(<rebuild_index_option>[,...n])]   | REORGANIZE [ WITH( LOB_COMPACTION = {ON | OFF})]   | DISABLE   | SET (<set_index_option>[,...n]) }

Let’s look at a few examples that demonstrate the power ...