SQL Server Questions and Answers – Indexing Strategies – 1

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

1. Setting the SORT_IN_TEMPDB option to ON can direct the SQL Server Database Engine to use ________ to store the intermediate sort results.
a) tempdb
b) master
c) msdb
d) model
View Answer

Answer: a
Explanation: It is required to create or rebuild an index when tempdb is on a set of disks different from that of the user database.

2. Point out the correct statement.
a) The Database Engine merges the sorted runs of index leaf rows into a single, sorted stream
b) SQL Server also supports full-text indexes and XML indexes, but those are relevant only for specific data types
c) The fill-factor value is a percentage from 1 to 50
d) All of the mentioned
View Answer

Answer: a
Explanation: The sort merge component of the Database Engine starts with the first page of each sort run, finds the lowest key in all the pages, and passes that leaf row to the index create component.

3. Which of the following rebuilds the IX_Employee_OrganizationLevel_OrganizationNode index with a fill factor of 80 on the HumanResources.Employee table?
a)

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources
REBUILD WITH (FILLFACTOR = 80);

b)

advertisement
advertisement
CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80);

c)

Sanfoundry Certification Contest of the Month is Live. 100+ Subjects. Participate Now!
ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80);

d) All of the mentioned
View Answer

Answer: c
Explanation: The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.

advertisement

4. Which of the following guideline is considered during column design?
a) Keep the length of the index key short for non clustered indexes
b) An xml data type can only be a key column only in an XML index
c) Examine column which have composite primary keys
d) None of the mentioned
View Answer

Answer: b
Explanation: QL Server 2012 SP1 introduces a new type of XML index known as a Selective XML Index. This new index can improve querying performance over data stored as XML in SQL Server, allow for much faster indexing of large XML data workloads.

advertisement

5. Point out the wrong statement.
a) SQL Server supports only two index types: clustered and non-clustered
b) Appropriate indexes can make a world of difference in performance
c) SET option requires ALTER permission on the table or view
d) None of the mentioned
View Answer

Answer: a
Explanation: There are plenty of indexes in SQL Server such as full text, columnstore index in SQL Server 2012

6. By default, indexes are stored in the ______ filegroup as the base table on which the index is created.
a) same
b) different
c) may be same or different
d) none of the mentioned
View Answer

Answer: a
Explanation: A nonpartitioned clustered index and the base table always reside in the same filegroup.

7. Specifying the order in which key values are stored in an index is useful when queries referencing the table have _____________
a) ORDER BY clauses
b) SORT clauses
c) WHERE clauses
d) None of the mentioned
View Answer

Answer: a
Explanation: In these cases, the index can remove the need for a SORT operator in the query plan; therefore, this makes the query more efficient.

8. Every table should have a ________ index defined on the column or columns.
a) CLUSTERED
b) NON CLUSTERED
c) FULL TEXT
d) All of the mentioned
View Answer

Answer: a
Explanation: Clustered indexes sort and store the data rows in the table based on their key values.

9. Which of the following option is immediately applied to the index by using the SET clause in the ALTER INDEX statement?
a) ALLOW_PAGE_LOCKS
b) ALLOW_COLUMN_LOCKS
c) STATISTICS_RECOMPUTE
d) None of the mentioned
View Answer

Answer: a
Explanation: ALTER INDEX statement requires ALTER permission on the table or view.

10. Which of the following code snippet demonstrates ALTER statement with STATISTICS_NORECOMPUTE in ONLINE state?
a)

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON,
              STATISTICS_RECOMPUTE = ON);

b)

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

c)

ALTER INDEX ALL ON Production.Product
BUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

d) All of the mentioned
View Answer

Answer: b
Explanation: The following options can be set when you rebuild an index by using either ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING: PAD_INDEX, FILLFACTOR, SORT_IN_TEMPDB, IGNORE_DUP_KEY, STATISTICS_NORECOMPUTE, ONLINE, ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, MAXDOP, and DROP_EXISTING.

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.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.