SQL Server Questions and Answers – Partitioning

This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Partitioning”.

1. SQL Server 2014 supports up to ________ partitions by default.
a) 20000
b) 2000
c) 1000
d) 15000
View Answer

Answer: d
Explanation: In versions earlier than SQL Server 2012, the number of partitions was limited to 1,000 by default.

2. Point out the correct statement.
a) On x86-based systems, creating a table or index with more than 1000 partitions is possible
b) Compressing large tables or indexes can have manageability and performance benefit
c) Partitioning slow tables or indexes can have the following manageability and performance benefit
d) None of the mentioned
View Answer

Answer: a
Explanation: Partitioning feature is not supported on x86 system.

3. __________ is a database object that defines how the rows of a table or index are mapped to a set of partitions based on the values of certain column.
a) Partition function
b) Partition scheme
c) Partition column
d) All of the mentioned
View Answer

Answer: a
Explanation: Partition function defines the number of partitions that the table will have and how the boundaries of the partitions are defined.

4. Designing an nonaligned partitioned index can be useful in the following case?
a) The base table has not been partitioned
b) The index key is unique and it does not contain the partitioning column of the table
c) You want the base table to participate in collocated joins with more tables using different join columns
d) All of the mentioned
View Answer

Answer: d
Explanation: Nonaligned index is an index partitioned independently from its corresponding table.

advertisement
advertisement

5. Point out the wrong statement.
a) Computed columns that participate in a partition function must be explicitly marked PERSISTED
b) The new, higher limit of 15,000 partitions affects memory, partitioned index operations, DBCC commands, and queries
c) Partitioning column is the process by which the query optimizer accesses only the relevant partitions to satisfy the filter criteria of the query
d) All of the mentioned
View Answer

Answer: c
Explanation: Partition elimination is the process by which the query optimizer accesses only the relevant partitions to satisfy the filter criteria of the query.

6. At least _______ of RAM is recommended if a large number of partitions are in use.
a) 16 GB
b) 4 GB
c) 8 GB
d) 2 GB
View Answer

Answer: a
Explanation: Systems with 16 GB of RAM that run many memory-intensive processes may run out of memory on operations that run on a large number of partitions.

Sanfoundry Certification Contest of the Month is Live. 100+ Subjects. Participate Now!

7. With a larger number of partitions, ______ commands could take longer to execute as the number of partitions increases.
a) DML
b) DDL
c) DBCC
d) TCL
View Answer

Answer: c
Explanation: The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.

8. SQL Server 2008 provides enhanced partitioning information for __________ execution plans.
a) compile-time
b) run time
c) both compile-time and run-time
d) all of the mentioned
View Answer

Answer: c
Explanation: Execution plans now provide an optional Partitioned attribute that indicates that an operator, such as a seek, scan, insert, update, merge, or delete, is performed on a partitioned table.

advertisement

9. Which of the following partitioned attribute can appear in the following physical and logical operators?
a) Index Seek
b) Drop
c) Alter
d) None of the mentioned
View Answer

Answer: a
Explanation: When an operator such as an Index Seek is executed on a partitioned table or index, the Partitioned attribute appears in the compile-time and run-time plan and is set to True.

10. The query processor uses a __________ execution strategy for queries that select from partitioned objects.
a) distributed
b) parallel
c) lock
d) none of the mentioned
View Answer

Answer: b
Explanation: As part of the execution strategy, the query processor determines the table partitions required for the query and the proportion of threads to allocate to each partition.

advertisement

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.