You administer a SQL Server 2008 instance. The instance contains a database table named Sales.SalesOrderDetail.
The table has the following definition:
CREATE TABLE [Sales].[SalesOrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
PRIMARY KEY CLUSTERED ( [SalesOrderID] ASC,[SalesOrderDetailID] ASC) WITH (DATA_COMPRESSION = ROW) ON [PRIMARY]) ON
[PRIMARY]
GO
The table includes the following index:
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] ([ProductID] ASC) ON [PRIMARY]
GO
You need to enable row compression for the index.
Which Transact-SQL statement or statements should you use?
A. ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] REBUILD WITH (FILLFACTOR = 50)
GO
B. ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] REBUILD WITH (DATA_COMPRESSION = ROW)
GO
C. ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] REORGANIZE WITH (DATA_COMPRESSION = ROW)
GO
D. ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] REORGANIZE WITH (DATA_COMPRESSION = ON)
GO
E. ALTER INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] REBUILD WITH (ALLOW_ROW_LOCKS = ON, MAXDOP = 50)
GO
F. CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] ([ProductID] ASC)
WITH (DATA_COMPRESSION = ROW, SORT_IN_TEMPDB = OFF, DROP_EXISTING= ON) ON [PRIMARY]
GO
G. CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] ([ProductID] ASC)
WITH (DATA_COMPRESSION = ROW, SORT_IN_TEMPDB = ON, DROP_EXISTING = OFF) ON [PRIMARY]
GO
H. SELECT 50 AS fill_factor
FROM sys.dm_db_index_operational_stats (DB_ID(), OBJECT_ID(N’Sales.SalesOrderDetail’), NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = object_id AND a.index_id = b.index_id
WHERE name=’IX_SalesOrderDetail_ProductID’
GO
