Implementing Physical Database Structure - Microsoft SQL Server 2012 Performance Tuning - page 346

4. If the AdventureWorks2012 database is installed at its current location, depending

upon your SQL Server installation path, you will see a result set similar to the one
shown in the following screenshot:

5. Now, the following query will drop and recreate the non-clustered indexes on a new

filegroup, fg_indexes :
USE AdventureWorks2012
GO

--Dropping and re-creating nonclustered
--index on filegroup fg_Indexes
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON
[Sales].[SalesOrderDetail]
(
[ProductID] ASC
) WITH (DROP_EXISTING = ON)
ON [fg_Indexes]
GO

--Dropping and re-creating nonclustered
--index on filegroup fg_Indexes
CREATE UNIQUE NONCLUSTERED INDEX [AK_SalesOrderDetail_rowguid] ON
[Sales].[SalesOrderDetail]
(
[rowguid] ASC
) WITH (DROP_EXISTING = ON)
ON [fg_Indexes]
GO

6. To verify the new location of indexes, run the following query:

USE AdventureWorks2012
GO

--Verifying the new physical location of
--nonclustered indexes on table Sales.SalesOrderDetail
SELECT
OBJECT_NAME(I.object_id) AS TableName
,I.name AS IndexName