Azure SQL Database Partitioning – Interview Questions & Answers

Posted by

Here is a list of important interview questions related to partitioning in Azure SQL Database, along with detailed answers.


1. What is partitioning in Azure SQL Database?

Answer:
Partitioning in Azure SQL Database is a technique that divides a large table or index into smaller, more manageable pieces (partitions) based on a partition key. It helps in query performance optimization, easier data management, and maintenance.


2. How does partitioning work in Azure SQL Database?

Answer:
Partitioning in Azure SQL Database works by logically splitting a table using a partition function, which defines how rows are distributed based on a partition column (key). The partitions are then assigned using a partition scheme that maps them to specific filegroups.


3. What are the benefits of partitioning in Azure SQL Database?

Answer:
Improved Query Performance – Queries targeting specific partitions run faster.
Efficient Data Management – Easier to archive, delete, or update specific partitions.
Better Resource Utilization – Reduces I/O contention and improves parallel processing.
Faster Backup and Restore – Only backup specific partitions instead of the entire table.
Index Management Optimization – Indexes can be managed at the partition level.


4. What are the key components of partitioning in Azure SQL Database?

Answer:
Partitioning in Azure SQL Database involves:

  1. Partition Function – Defines how data is divided based on a partition key.
  2. Partition Scheme – Maps the partitions to different storage filegroups.
  3. Partitioned Table – The actual table that is split based on the partition function.
  4. Partitioned Index – An index aligned with the partitioned table.

5. How do you create a partitioned table in Azure SQL Database?

Answer:
To create a partitioned table, follow these steps:

1️⃣ Create a Partition Function

CREATE PARTITION FUNCTION pf_RangeByYear (INT)
AS RANGE LEFT FOR VALUES (2019, 2020, 2021, 2022);

2️⃣ Create a Partition Scheme

CREATE PARTITION SCHEME ps_RangeByYear
AS PARTITION pf_RangeByYear ALL TO ([PRIMARY]);

3️⃣ Create a Partitioned Table

CREATE TABLE SalesData (
    SaleID INT PRIMARY KEY,
    SaleYear INT,
    SaleAmount DECIMAL(10,2)
)
ON ps_RangeByYear(SaleYear);

💡 This table stores records in different partitions based on the SaleYear column.


6. How do you check which partition a row belongs to?

Answer:
Use the $PARTITION function to determine which partition a row is stored in:

SELECT SaleID, SaleYear, $PARTITION.pf_RangeByYear(SaleYear) AS PartitionNumber
FROM SalesData;

7. How do you move data between partitions?

Answer:
You can switch partitions using the ALTER TABLE SWITCH command:

ALTER TABLE SalesData SWITCH PARTITION 1 TO SalesArchive PARTITION 1;

This moves data instantly without physical data movement.


8. How do you check partition statistics?

Answer:
To check how many rows exist in each partition:

SELECT ps.name AS PartitionScheme,
       pf.name AS PartitionFunction,
       p.partition_number,
       SUM(s.rows) AS RowCount
FROM sys.partitions p
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
JOIN sys.dm_db_partition_stats s ON p.object_id = s.object_id AND p.index_id = s.index_id
WHERE i.object_id = OBJECT_ID('SalesData')
GROUP BY ps.name, pf.name, p.partition_number;

9. Can you partition an existing table?

Answer:
No, Azure SQL Database does not support directly partitioning an existing table. You must:

  1. Create a new partitioned table.
  2. Move data from the old table to the new one.
  3. Drop the old table.

10. What are the limitations of partitioning in Azure SQL Database?

Answer:
Cannot modify an existing table to be partitioned.
Limited to horizontal partitioning (vertical partitioning requires table splitting).
Partition switching is not supported in Azure SQL Database (only available in SQL Server).
Filegroups are not supported (all data resides in the default storage).


11. How do you rebuild indexes on a partitioned table?

Answer:

ALTER INDEX ALL ON SalesData REBUILD PARTITION = 3;

This rebuilds only Partition 3, making index maintenance efficient.


12. When should you use partitioning?

Answer:
✅ When working with very large tables (millions+ rows).
✅ When queries filter data based on a range column (e.g., dates, years, IDs).
✅ When you need to manage historical data efficiently.
✅ When you require parallel processing for better performance.


13. How does partitioning affect query performance?

Answer:

  • Queries that filter by the partition key can quickly locate relevant partitions (partition elimination).
  • Indexes remain smaller when defined within partitions, improving lookup speed.
  • Full table scans are reduced, leading to faster query execution.

14. How can you drop a partitioned table?

Answer:
Use:

DROP TABLE SalesData;

This removes the table but does not drop the partition function or scheme.

To drop everything:

DROP PARTITION SCHEME ps_RangeByYear;
DROP PARTITION FUNCTION pf_RangeByYear;

15. What is the difference between sharding and partitioning?

Answer:

FeaturePartitioningSharding
DefinitionDividing data within a single databaseSplitting data across multiple databases
ScopeLogical division of table into partitionsPhysical separation across databases
ManagementManaged inside the same databaseEach shard is a separate database
Use CaseHandling large tables within a single DBDistributing load across multiple DB instances

16. Can you combine partitioning with indexing?

Answer:
Yes! Partitioned indexes improve query speed. Example:

CREATE CLUSTERED INDEX idx_SalesAmount ON SalesData (SaleAmount)
ON ps_RangeByYear(SaleYear);

This keeps the index aligned with partitions, improving efficiency.


17. How does partition elimination work in Azure SQL?

Answer:
Partition elimination happens when SQL Server only scans the relevant partitions in a query:

SELECT * FROM SalesData WHERE SaleYear = 2022;

This skips unnecessary partitions, making queries much faster.


Conclusion

Partitioning in Azure SQL Database is a powerful tool for performance tuning and efficient data management. Understanding partition functions, schemes, indexing, and maintenance will help optimize large databases.

guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x