Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You administer a Microsoft SQL Server 2008 database named AdventureWorks that contains a table named Production.Product. The table contains a primary key named PK_Product_ProductID and a non-clustered index named AK_Product_ProductNumber. Both indexes have been created on a single primary partition.
The table has the following definition:
CREATE TABLE [Production].[Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[ProductNumber] [nvarchar](25) NOT NULL,
[Color] [nvarchar](15) NULL,
[Class] [nchar](2) NULL,
[Style] [nchar](2) NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED ([ProductID] ASC) ON [PRIMARY]) ON [PRIMARY]
GO
The index has the following definition:
CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_ProductNumber] ON [Production].[Product] ([ProductNumber] ASC) ON [PRIMARY]
GO
The Production.Product table contains 1 million rows. You want to ensure that data retrieval takes the minimum amount of time when the queries executed against the Production.Product table are ordered by product number or filtered by class. You observe that the average fragmentation for the AK_Product_ProductNumber index is 24 percent. You need to reduce fragmentation. You need to achieve this goal without blocking access to table data.
Which Transact-SQL statement should you use?
A. EXEC sys.sp_configure ‘index create memory’, 1
B. CREATE STATISTICS ProductClass_StatsON Production.Product (Name, ProductNumber, Class) WHERE Class <> null
C. UPDATE INDEX AK_Product_ProductNumber ON Production.Product SET (STATISTICS_NORECOMPUTE = ON)
D. ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS ON
E. DBCC SHOW_STATISTICS (‘Production.Product’, AK_Product_ProductNumber)
F. CREATE STATISTICS ProductClass_StatsON Production.Product (Name, ProductNumber, Class) WHERE Class is not null
G. ALTER INDEX AK_Product_ProductNumber ON Production.Product REORGANIZE
H. CREATE STATS ProductClass_StatsON Production.Product (Name, ProductNumber, Class) WHERE Class is not null WITH SAMPLE 100 PERCENT
I. SELECT * FROM sys.dm_db_index_operational_stats (DB_ID(), OBJECT_ID(N’Production.Product’),NULL, NULL)
J. SELECT * FROM STATS WHERE name=’AK_Product_ProductNumber’
K. SELECT * FROM SYS.STATS WHERE name=’AK_Product_ProductNumber’
L. ALTER INDEX AK_Product_ProductNumber ON Production.Product REBUILD Partition = 1
M. UPDATE STATISTICS Production.Product
N. SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N’Production.Product’),NULL, NULL, NULL)
O. ALTER INDEX AK_Product_ProductNumber ON Production.Product REBUILD
P. ALTER STATISTICS Production.Product
Q. SELECT * FROM sys.indexes where name=N’Production.Product’
