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:
- Partition Function – Defines how data is divided based on a partition key.
- Partition Scheme – Maps the partitions to different storage filegroups.
- Partitioned Table – The actual table that is split based on the partition function.
- 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:
- Create a new partitioned table.
- Move data from the old table to the new one.
- 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:
Feature | Partitioning | Sharding |
---|---|---|
Definition | Dividing data within a single database | Splitting data across multiple databases |
Scope | Logical division of table into partitions | Physical separation across databases |
Management | Managed inside the same database | Each shard is a separate database |
Use Case | Handling large tables within a single DB | Distributing 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.