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
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
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
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
Explanation: Nonaligned index is an index partitioned independently from its corresponding table.
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
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
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.
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
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
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.
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
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
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.
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]- Check Information Technology Books
- Check SQL Server Books
- Practice Programming MCQs
- Apply for Programming Internship