SQL Partition MCQs

What is SQL table partitioning?

• Grouping rows into different views

• Dividing a table into smaller, manageable pieces

• Joining tables by keys

• Clustering indexes

Answer explanation

SQL table partitioning refers to dividing a table into smaller, manageable pieces, which can improve performance and manageability. This is the correct choice, as it directly describes the process of partitioning.

What is Range Partitioning?

• Partitions based on distinct values

• Partitions based on a modulo function

• Partitions based on value ranges

• Partitions based on alphabet

Answer explanation

Range partitioning divides data into segments based on specified value ranges. This allows for efficient data management and retrieval, making 'Partitions based on value ranges' the correct choice.

What is List Partitioning?

• Based on a hash function

• Based on value lists (e.g., region names)

• Based on ranges

• Based on indexes

Answer explanation

List Partitioning divides data based on specific values, such as region names. This method allows for efficient data management by grouping related entries, making 'Based on value lists (e.g., region names)' the correct choice.

What is Hash Partitioning used for?

• Balancing data evenly

• Sorting alphabetically

• Organizing values by size

• Creating composite indexes

Answer explanation

Hash Partitioning is used for balancing data evenly across partitions. It distributes records based on a hash function, ensuring that each partition receives a roughly equal amount of data, which optimizes performance.

Which of the following supports partitioned tables?

• Oracle

• PostgreSQL

• SQL Server

• All of the above

Answer explanation

All of the listed database systems—Oracle, PostgreSQL, and SQL Server—support partitioned tables, allowing for improved performance and easier management of large datasets.

Which partitioning method is best for a date-based column?

• List

• Hash

• Range

• Full Table Scan

Answer explanation

The Range partitioning method is ideal for date-based columns as it allows data to be divided into ranges, making it efficient for queries that filter by date intervals. This enhances performance compared to other methods.

Which partition method helps balance data load with no natural key?

• Range

• List

• Hash

• View Partition

Answer explanation

Hash partitioning is effective for balancing data load when there is no natural key, as it distributes rows evenly across partitions based on a hash function, ensuring a more uniform data distribution compared to range or list methods.

What happens when a row doesn’t match any List Partition?

• It's added to a default partition (if defined)

• The row is deleted

• It creates a new partition

• Error occurs

Answer explanation

When a row doesn't match any List Partition, it is added to a default partition if one is defined. This ensures that no data is lost and allows for better organization of data within the database.

Which column type is ideal for List Partitioning?

• Sales (numeric)

• Profit (decimal)

• Region (categorical)

• OrderDate

Answer explanation

List Partitioning is best suited for categorical data, as it allows for distinct groups. The 'Region' column, being categorical, is ideal for this purpose, while numeric and date types are not suitable for partitioning by distinct categories.

Which SQL clause defines partitions?

• PARTITION BY

• DISTRIBUTE BY

• CREATE PARTITION

• PARTITION OF TABLE

Answer explanation

The correct clause for defining partitions in SQL is 'PARTITION BY'. This clause is used in window functions to specify how the result set should be divided into partitions for processing.