Which statements are true for online indexes?

You are the database administrator for your company and manage all the SQL Server 2008 databases of thecompany.
A production database named Prod1 contains all the product and sales data for your company.
The Prod_details table contains detailed data for all of the products manufactured by your company and is frequentlyaccessed by the employees of the company.
Most of the employees’ queries include the Prod_id column of the Prod_details table in the WHERE clause.
You create an online index on the Prod_id column by using the following statement:
CREATE INDEX Prod_id_indx
ON Prod_details (Prod_id) WITH (ONLINE = ON);
You must understand the restrictions that apply to an online index. Which statements are true for online indexes? (Choose all that apply.)

You are the database administrator for your company and manage all the SQL Server 2008 databases of thecompany.
A production database named Prod1 contains all the product and sales data for your company.
The Prod_details table contains detailed data for all of the products manufactured by your company and is frequentlyaccessed by the employees of the company.
Most of the employees’ queries include the Prod_id column of the Prod_details table in the WHERE clause.
You create an online index on the Prod_id column by using the following statement:
CREATE INDEX Prod_id_indx
ON Prod_details (Prod_id) WITH (ONLINE = ON);
You must understand the restrictions that apply to an online index. Which statements are true for online indexes? (Choose all that apply.)

A.
An online index cannot be created on a global temporary table.

B.
An online index cannot be created on a local temporary table.

C.
An online index cannot be created on an XML column.

D.
A unique clustered index cannot be rebuilt online.

E.
An index defined with a text or ntext type columns cannot be rebuilt online.

Explanation:

An online index can be created by specifying the ONLINE = ON argument while creating an index. This argumentspecifies that the tables and associated indexes remain online and are available for modifications and querieswhile an operation is being performed on the index. The following restrictions apply to creating or rebuildingindexes online:
? Online indexes cannot be created on local temporary tables.
? Disabled clustered indexes cannot be rebuilt online.
? Clustered indexes cannot be rebuilt online if the underlying table contains large object binary (LOB) datatypes. LOB data types include image , ntext , text , varchar(max) ,
nvarchar(max) , varbinary(max) , and xml data types.
? Nonclustered indexes created on LOB data type columns cannot be rebuilt online.
? XML indexes cannot be rebuilt online.The option stating that an online index cannot be created on a global temporary table is incorrect because you
can create an online index on a global temporary table. You cannot create an online index on a local temporarytable. A unique clustered index can be rebuilt online provided the underlying table does not contain LOB data typecolumns.

Objective:
Performing Data Management Tasks

Sub-Objective:
Maintain indexes.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > CREATE INDEX (Transact-SQL)



Leave a Reply 0

Your email address will not be published. Required fields are marked *