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

ALTER TABLE [Sales].[SalesOrderDetail]
ADD CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_
SalesOrderDetailID]
PRIMARY KEY CLUSTERED
(
[SalesOrderID] ASC,
[SalesOrderDetailID] ASC
) ON [fg_SalesOrderDetails]
GO

5. Now, verify the new location of the table Sales.SalesOrderDetail , by running

the following query:
USE AdventureWorks2012
GO

--Verifying new physical location
--of table Sales.SalesOrderDetail
SELECT
OBJECT_NAME(I.object_id) AS TableName
,FG.name AS FileGroupName
,DF.physical_name AS DataFilePath
FROM sys.indexes AS I
INNER JOIN sys.tables AS T
ON I.object_id = T.object_id
INNER JOIN sys.filegroups AS FG
ON I.data_space_id = FG.data_space_id
INNER JOIN sys.database_files AS DF
ON I.data_space_id = DF.data_space_id
WHERE I.index_id <= 1 AND I.object_id = OBJECT_ID('Sales.
SalesOrderDetail')
GO

The following screenshot is the output of the previous query after moving the Sales.
SalesOrderDetail table to a new location:

How it works...

In order to place Sales.SalesOrderDetail on separate physical disk, we created a new
filegroup, fg_SalesOrderDetails , in the AdventureWorks2012 database and added a
new data file, fg_SalesOrderDetails_Data.ndf , to this filegroup. After creating a new
filegroup and data file, we observed the current location of the Sales.SalesOrderDetail
table with a query similar to what we had used in the previous recipe.