SQL Server Utilities - SQL Server - The secret index syntax

In this post I would like to share a CREATE INDEX syntax I was not aware of which I came across a few years ago while moving a multi tera VLDB to a new filegroup. Recently I had to use this syntax again and it took me a few seconds to pull it out of my mind.

The advantage is of course a single atomic operation which performs faster as opposed to 2 separate commands. The command preforms much faster as it needs to scan the data once instead of twice and on large objects this can make a significant difference impacting the duration we hold locks on resources effecting database concurrency, stressing the IO subsystem etc.

Here is the CREATE UNIQUE CLUSTERED INDEX using the DROP_EXISTING=ON for a Clustered Primary Key which eliminates the need of using the ALTER TABLE … DROP CONSTRAINT and then ALTER TABLE … CREATE CONSTRAINT

CREATE UNIQUE CLUSTERED  INDEX PK_dt_DetailsGeneral ON dbo.dt_DetailsGeneral (DetailsID )
	WITH (DROP_EXISTING=ON,  ONLINE = OFF, FILLFACTOR = 100, DATA_COMPRESSION = PAGE) ON FG_ALL_DATA;

This is the output of sp_helpindex2 showing the table structure and indexes.