intro
Let’s learn everything you need to know about SQL Server indexes on partitioned tables to master data retrieval, even in complex scenarios.
What is a great tool to speed up queries on large tables? Partitions! Another possible solution? Indexes! Can these two work together? What is the relationship between indexes and partitioned tables? Find out all that in this article on SQL Server indexes on partitioned tables!
Key Concepts You Need to Know
Here are the main facts and key concepts you need to know to understand this article on SQL Server indexes on partitioned tables:
Partitioning enables efficient data access, faster data transfers (e.g., OLTP to OLAP), targeted maintenance (e.g., compressing or truncating partitions), and improved query performance, especially for equijoin queries on tables with matching partitioning columns by minimizing the amount of data that is accessed by your database. If you want, you can learn more about database sharding and distributing data later on, but now, let’s dig into types of indexes on partitioned tables in SQL Server.
Types of SQL Server Indexes on Partitioned Tables
Now that you have explored the prerequisites for this guide, you are ready to discover the different types of indexes supported by SQL Server on partitioned tables.
Let’s get started!
Aligned Indexes
An aligned index is built using the same partition scheme as the corresponding table. When a table and its indexes are aligned, the database engine can quickly and efficiently switch partitions in or out of the table, while preserving the partition structure of both the table and its indexes. This type of index is unique to SQL Server and is not available on other database management systems.
In such a scenario, when partitions are added or removed, the table and index stay in sync, maintaining data integrity and performance.
To be aligned, the partitioning functions of the index and table must meet these three conditions:
An aligned index in turn can be categorized into one of the following:
Non-Aligned Indexes
A non-aligned index differs from the table’s partitioning scheme. The consequence is that the index is placed in a different filegroup or set of filegroups, potentially enabling better performance for certain workloads or use cases. Non-aligned SQL Server indexes on partitioned tables are useful when the index key does not include the partitioning column or when collocated joins are needed with other tables partitioned differently.
How Indexes on SQL Server Partitions Work
The best way to understand how SQL Server indexes on partitioned tables work is to dive into the two most common scenarios.
Adding an Index to a Table That Is Already Partitioned
When adding an index to a table that is already partitioned, SQL Server first ensures that the index is aligned with the partitioning scheme—if applicable. In detail, the database system associates the new index with the partition function and partition scheme of the table. It checks the partitioning column and data types to confirm alignment and verify that the index respects the same partition boundaries. If the index is nonclustered, SQL Server maintains separate data structures for the index, with pointers referencing the data rows in the partitioned table.
For non-aligned indexes, the SQL Server creates a separate partitioning scheme for the index, which is then placed in a filegroup different from the table. See an example of a filegroup in the documentation.
Partitioning a Table That Already Has Indexes
When partitioning a table that already has indexes, SQL Server needs to align the indexes with the new partitioning scheme. For clustered indexes, the system ensures that the clustering key includes the partitioning column, else it adds the partitioning column by default. Nonclustered indexes are also adjusted to include the partitioning column, if necessary. SQL Server updates the partition function and the partitioning scheme, redistributing the data according to the new partitioning rules.
For non-aligned indexes, the system will apply a different partitioning scheme, storing the index data on a separate filegroup from the base table.
Conclusion
In this guide, you gained valuable insights into partitioned tables and indexes in SQL Server. You now understand how the popular DBMS handles partitions, partitioned indexes, and the differences between aligned and non-aligned indexes.
This topic is quite complex, which highlights how intricate databases can be. Thankfully, tools like DbVisualizer greatly simplify database management!
That powerful database client supports multiple DBMS technologies and offers advanced features such as visual data exploration, query optimization, SQL formatting, and ERD-like schema generation. Try DbVisualizer for free today!
Is it possible to define SQL Server indexes on partitioned tables?
Yes, it is possible to define indexes on partitioned tables in SQL Server. Aligned indexes follow the table's partitioning scheme, while non-aligned indexes do not. At the same time, remember that creating or rebuilding non-aligned indexes on tables with over 1,000 partitions is unsupported.
What is the maximum number of partitions before SQL Server no longer supports indexes?
SQL Server supports indexes on tables with up to 15,000 partitions. However, using more than 1,000 partitions has implications on memory, partitioned index operations, DBCC commands, and queries. In particular, creating or rebuilding non-aligned indexes on tables with more than 1,000 partitions is not supported.
What is the relationship between partitioned tables and indexes in SQL Server?
In SQL Server, partitioned tables organize data into partitions for efficient management and query performance. Indexes on these tables can either align with the partitioning scheme as aligned indexes or exist independently as non-aligned indexes.
How does query processing work on partitioned tables and indexes?
On partitioned tables and indexes, SQL Server optimizes performance by leveraging partitioning to improve parallel query plans. SQL Server 2008 introduced enhancements to parallel and serial plan representation and provided detailed partitioning information in compile-time and run-time execution plans. For more information on these improvements, refer to the official documentation.
Can SQL server indexes on partitioned tables be managed singularly?
No, you cannot directly create, delete, or update indexes on individual partitions in SQL Server. Indexes on partitioned tables are managed at the partition level, but operations like creating, deleting, or rebuilding indexes apply to the entire table rather than individual partitions. When you rebuild or reorganize an index, it affects all partitions covered by the index.
Why use a database client?
Adopting an SQL client like DbVisualizer allows you to visually deal with data in your databases. A robust SQL client offers a range of tools to simplify data management and analysis, no matter which DBMS you are using. These features enhance productivity, streamline workflows, and help you gain insights from your data more effectively.
