SQL Server Questions and Answers – Indexing Strategies – 2

This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Indexing Strategies – 2”.

1. Which of the following query is used to enable a disabled index using CREATE INDEX?
a)

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
   (OrganizationLevel, OrganizationNode)
WITH (DROP_EXIST = ON);

b)

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
   (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = OFF);

c)

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
   (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON);

d) None of the mentioned
View Answer

2. Point out the correct statement.
a) When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the source filegroup
b) The Database Engine first scans the data pages of the base table to retrieve key values and builds an index leaf row for each data row
c) When SORT_IN_TEMPDB is set to ON, the default, the sort runs are stored in the destination filegroup
d) None of the mentioned
View Answer

3. Which of the following query enables the IX_Employee_OrganizationLevel_OrganizationNode index on the Employee table?
a) DBCC DBREINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);
b) DBCC REINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);
c) DBCC DBINDEX (“Employee”, IX_Employee_OrganizationLevel_OrganizationNode);
d) All of the mentioned
View Answer

4. Which of the following query can enable all indexes on Employee table?
a) DBCC DBREINDEX (“Employee”, ” “);
b) DBCC DBREINDEX (“EMP”, ” “);
c) DBCC DBREINDEX (“HumanResources.Employee”, “ALL”);
d) None of the mentioned
View Answer

5. Point out the wrong statement.
a) If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final index structure
b) When you create a clustered index on a table that has nonclustered indexes, you must have available as free space
c) When you set the SORT_IN_TEMPDB option to ON, you must have sufficient free disk space available in master database
d) All of the mentioned
View Answer

6. Which of the following query disables all the indexes on Employee table?
a) ALTER INDEX MUL ON HumanResources.Employee
DISABLE;
b) ALTER INDEX NEST ON HumanResources.Employee
DISABLE;
c) ALTER INDEX ALL ON HumanResources.Employee
ENABLE;
d) ALTER INDEX ALL ON HumanResources.Employee
DISABLE;
View Answer

7. Which of the following index operations require no additional disk space?
a) ALTER INDEX
b) DROP INDEX
c) CREATE INDEX
d) All of the mentioned
View Answer

8. Which of the following index operation require additional disk space?
a) UPDATE INDEX
b) DROP INDEX MOVE TO
c) ALTER INDEX ADD CONSTRAINT
d) All of the mentioned
View Answer

9. The SORT_IN_TEMPDB option cannot be set for ________ statements.
a) ALTER INDEX
b) DROP INDEX
c) CREATE INDEX
d) All of the mentioned
View Answer

10. Which of the following query will disable the index on Employee table?
a) ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode Employee
DISABLE;
b) ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources
DISABLE;
c) CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
DISABLE;
d) None of the mentioned
View Answer

Sanfoundry Global Education & Learning Series – SQL Server.

To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.