MS SQL Server - 92 - Data Partitioning MCQ

92 – Data Partitioning MCQ

1 / 25

1. What is data partitioning in SQL Server?

C. Combining data from multiple tables

B. Dividing data into multiple logical or physical units

D. Indexing data for faster retrieval

A. Splitting data into smaller tables

2 / 25

2. Which SQL Server feature allows you to manage large tables by dividing them into smaller, more manageable units?

A. Data Warehouse

D. Data Encryption

B. Data Partitioning

C. Data Replication

3 / 25

3. What is the primary benefit of using data partitioning in SQL Server?

C. Simplified data retrieval

D. Enhanced data quality

A. Improved data security

B. Reduced storage space requirements

4 / 25

4. In SQL Server, which type of partitioning is based on a range of values, such as dates or numeric ranges?

D. Round-robin Partitioning

A. List Partitioning

C. Range Partitioning

B. Hash Partitioning

5 / 25

5. What is the purpose of list partitioning in SQL Server?

C. Distributing data evenly across partitions

A. Dividing data into partitions based on a range of values

D. Applying encryption to data partitions

B. Dividing data into partitions based on a list of discrete values

6 / 25

6. Which SQL Server edition supports table partitioning?

B. SQL Server Standard Edition

C. SQL Server Developer Edition

A. SQL Server Express Edition

D. SQL Server Enterprise Edition

7 / 25

7. What is hash partitioning in SQL Server?

A. Dividing data into partitions based on a hash function

C. Dividing data into partitions based on a list of discrete values

D. Dividing data into partitions based on the row number

B. Dividing data into partitions based on a range of values

8 / 25

8. In SQL Server, what is a partitioning key?

C. The column used to determine how data is divided into partitions

D. A foreign key in a related table

B. A unique constraint on a column

A. A primary key of a table

9 / 25

9. Which type of partitioning in SQL Server allows you to specify which partition should hold specific values?

B. List Partitioning

C. Hash Partitioning

D. Round-robin Partitioning

A. Range Partitioning

10 / 25

10. What is round-robin partitioning in SQL Server?

B. Dividing data into partitions based on a range of values

A. Dividing data into partitions based on a hash function

D. Dividing data into partitions in a cyclical manner

C. Dividing data into partitions based on a list of discrete values

11 / 25

11. In SQL Server, what is a sliding window partitioning strategy commonly used for?

A. Rotating partitions periodically

C. Merging existing partitions

B. Adding new partitions to an existing table

D. Deleting old data from partitions

12 / 25

12. Which SQL Server feature allows you to perform maintenance tasks on individual partitions without affecting the entire table?

B. Partition Switching

A. Partitioned Views

D. Subqueries

C. Table Joins

13 / 25

13. What is a partitioned view in SQL Server?

A. A table that stores partitioning metadata

B. A virtual table that combines data from multiple partitioned tables

D. A system table that tracks partition utilization

C. A table used for logging partitioning activities

14 / 25

14. What is the purpose of partition pruning in SQL Server?

C. Merging existing partitions

B. Adding new partitions to a table

D. Deleting old data from partitions

A. Eliminating unused partitions during query execution

15 / 25

15. Which SQL Server function allows you to determine which partitions will be used in a query?

D. PARTITION_PRUNE()

B. PARTITION()

A. PARTITION_BY

C. PARTITION_RANK()

16 / 25

16. In SQL Server, what is the benefit of having a sliding window partitioning strategy?

C. It automates the partition management process.

D. It improves data security.

B. It simplifies query optimization.

A. It reduces the number of partitions.

17 / 25

17. What is the main purpose of using a table partitioning scheme in SQL Server?

B. To simplify database design

C. To optimize query performance and manage large datasets

D. To ensure high availability of data

A. To enforce data integrity constraints

18 / 25

18. In SQL Server, what is the maximum number of partitions that a partitioned table can have?

B. 1,000

D. There is no fixed limit.

C. 10,000

A. 100

19 / 25

19. What is the purpose of the PARTITION_BY clause in SQL Server?

A. To create a partitioned table

B. To define the partitioning key for a table

D. To list the discrete values for list partitioning

C. To specify the number of partitions for a table

20 / 25

20. Which SQL Server command is used to merge two adjacent partitions in a partitioned table?

B. MERGE PARTITION

A. ALTER PARTITION

D. DROP PARTITION

C. SPLIT PARTITION

21 / 25

21. In SQL Server, how does partitioning improve query performance?

C. By allowing parallel processing on partitions

B. By limiting the number of rows in a table

D. By creating smaller indexes

A. By reducing the size of the database

22 / 25

22. What is the primary benefit of using a partitioned table for archiving data in SQL Server?

D. Faster data insertion

A. Simplified data retrieval

C. Reduced storage costs

B. Improved data security

23 / 25

23. In SQL Server, how can you determine which partition contains a specific row of data?

A. Use the PARTITION_BY clause in a query.

C. Query the partitioning metadata.

D. Perform a full table scan.

B. Check the primary key of the table.

24 / 25

24. What is the main difference between range and list partitioning in SQL Server?

C. Range partitioning allows dynamic addition of partitions, while list partitioning requires predefined lists.

B. Range partitioning divides data based on numeric ranges, while list partitioning uses discrete values.

A. Range partitioning uses a hash function, while list partitioning uses a list of values.

D. Range partitioning is suitable for large datasets, while list partitioning is best for small datasets.

25 / 25

25. In SQL Server, how can you efficiently manage partitions with minimal impact on query performance?

C. By using sliding window partitioning

A. By using the ALTER PARTITION command

D. By deleting old partitions

B. By periodically rebuilding all partitions

Your score is

MS SQL Server – 92 – Data Partitioning MCQ