SQL Server Indexes

The leaf nodes contain the data pages of the underlying table ... in which index type?

• Nonclustered

• Spatial

• Clustered

• Unique

Table without a clustered index is called

• Heap

• Hash

• B tree

• Nonclustered table

Nonclustered indexes can not be created on tables stored as a heap.

• True

• False

Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns.

• True

• False

Which code snippet can be used to create clustered index ?

• ALTER TABLE dbo.ProductVendor CREATE CLUSTERED INDEX (VendorID)

• ALTER TABLE dbo.ProductVendor ADD INDEX (VendorID)

• CREATE INDEX IX_ProductVendor_VendorID ON dbo.ProductVendor (VendorID);

• CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON dbo.ProductVendor (VendorID);

Which index option specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild?

• FILLFACTOR

• FULLFACTOR

• PAD_INDEX

• PERCENTAGE

Renaming an index causes the index to be rebuilt.

• True

• False

Which operation drops and re-creates the index?

• Reorganize

• Rebuild

It is suggested to perform index rebuild when fragmentation is

• higher than 15%

• higher than 20%

• higher than 25%

• higher than 30%

(a)   indexes are the standard for storing and querying large data warehousing fact tables.