In the last post on this subject I talked about table partitioning and aligned indexes. This time I would like to discuss table partitioning and non-aligned indexes. But first I wanted to give you a little code that would answer the very last pieces of the summary in the last post. In the summary I asked you to take a look at the indexes we created to find more information about them. If you looked at the sys.index_columns catalog view, you would have noticed that the nonclustered, aligned index also included the partitioning column.
–Review the indexes just created
SELECT i.[name] AS ‘Index Name’
,i.index_id AS ‘Index ID’
,i.type_desc AS ‘Index Type’
,ic.index_column_id AS ‘Column’
,c.[name] AS ‘Column Name’
,ic.key_ordinal AS ‘Key Column’
,ic.is_included_column AS ‘INCLUDE Column’
,ic.partition_ordinal AS ‘Partitioning Column’–partitioning column added to index or part of key
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.[object_id] = ic.[object_id]
AND i.index_id = ic.index_id
INNER JOIN sys.columns c
ON ic.[object_id] = c.[object_id]
AND ic.column_id = c.column_id
WHERE i.[object_id] = OBJECT_ID(‘partitionDate’)
ORDER BY i.name, i.index_id,ic.index_column_id
My output
Index Name Index ID Index Type Column Column Name Key Column INCLUDE Column Partitioning Column
cl_partitionDate_colDate 1 CLUSTERED 1 colDate 1 0 1
ncl_partitionDate_colSession_1 2 NONCLUSTERED 1 colSession 1 0 0
ncl_partitionDate_colSession_1 2 NONCLUSTERED 2 colDesc 0 1 0
ncl_partitionDate_colSession_1 2 NONCLUSTERED 3 colDate 0 0 1
If you look at the reformatted output, you will notice that the nonclustered index has the one index key we defined (colSession), one INCLUDED Column (colDesc), and the partition column (ColDate). SQL Server added the colDate to the index in order for the index to be able to eliminate partitions during queries.
Let’s move forward and talk about non-aligned indexes. A non-aligned index is one that does not share the same partitioning design as the table which contains the data. You achieve non-aligned indexes by creating the index and using a different function and scheme when doing so. While there are several disadvantages to having non-aligned indexes on a partitioned table, there are several advantages that will be explained in the third part of this series when we discuss partitioned indexes and performance.
Note: Having non-aligned indexes on a table will prevent you from switching partitions from one table to another.
Let’s take a look at how to create a non-aligned index.
SET NOCOUNT ON
GO
–Create table on main DateRange partition
IF OBJECT_ID (‘partitionDate’) IS NOT NULL
DROP TABLE partitionDate
GO
CREATE TABLE partitionDate
(colDate DATETIME
,colSession INT
,colDesc VARCHAR(100)
)
GO
–Insert data into table
DECLARE @loop INT
SET @loop = 5000
WHILE @loop > 0
BEGIN
INSERT INTO partitionDate VALUES (‘20060301’,1,‘20060301-1’ )
INSERT INTO partitionDate VALUES (‘20060401’,1,‘20060401-1’ )
INSERT INTO partitionDate VALUES (‘20060501’,1,‘20060501-1’ )
INSERT INTO partitionDate VALUES (‘20060601’,1,‘20060601-1’ )
INSERT INTO partitionDate VALUES (‘20060701’,1,‘20060701-1’ )
INSERT INTO partitionDate VALUES (‘20060801’,1,‘20060801-1’ )
INSERT INTO partitionDate VALUES (‘20060901’,1,‘20060901-1’ )
INSERT INTO partitionDate VALUES (‘20061001’,1,‘20061001-1’ )
INSERT INTO partitionDate VALUES (‘20061101’,1,‘20061101-1’ )
INSERT INTO partitionDate VALUES (‘20061201’,1,‘20061201-1’ )
INSERT INTO partitionDate VALUES (‘20070101’,1,‘20070101-1’ )
INSERT INTO partitionDate VALUES (‘20070201’,1,‘20070201-1’ )
INSERT INTO partitionDate VALUES (‘20070301’,1,‘20070301-1’ )
INSERT INTO partitionDate VALUES (‘20060301’,2,‘20060301-2’ )
INSERT INTO partitionDate VALUES (‘20060401’,2,‘20060401-2’ )
INSERT INTO partitionDate VALUES (‘20060501’,2,‘20060501-2’ )
INSERT INTO partitionDate VALUES (‘20060601’,2,‘20060601-2’ )
INSERT INTO partitionDate VALUES (‘20060701’,2,‘20060701-2’ )
INSERT INTO partitionDate VALUES (‘20060801’,2,‘20060801-2’ )
INSERT INTO partitionDate VALUES (‘20060901’,2,‘20060901-2’ )
INSERT INTO partitionDate VALUES (‘20061001’,2,‘20061001-2’ )
INSERT INTO partitionDate VALUES (‘20061101’,2,‘20061101-2’ )
INSERT INTO partitionDate VALUES (‘20061201’,2,‘20061201-2’ )
INSERT INTO partitionDate VALUES (‘20070101’,2,‘20070101-2’ )
INSERT INTO partitionDate VALUES (‘20070201’,2,‘20070201-2’ )
INSERT INTO partitionDate VALUES (‘20070301’,2,‘20070301-2’ )
INSERT INTO partitionDate VALUES (‘20060301’,3,‘20060301-3’ )
INSERT INTO partitionDate VALUES (‘20060401’,3,‘20060401-3’ )
INSERT INTO partitionDate VALUES (‘20060501’,3,‘20060501-3’ )
INSERT INTO partitionDate VALUES (‘20060601’,3,‘20060601-3’ )
INSERT INTO partitionDate VALUES (‘20060701’,3,‘20060701-3’ )
INSERT INTO partitionDate VALUES (‘20060801’,3,‘20060801-3’ )
INSERT INTO partitionDate VALUES (‘20060901’,3,‘20060901-3’ )
INSERT INTO partitionDate VALUES (‘20061001’,3,‘20061001-3’ )
INSERT INTO partitionDate VALUES (‘20061101’,3,‘20061101-3’ )
INSERT INTO partitionDate VALUES (‘20061201’,3,‘20061201-3’ )
INSERT INTO partitionDate VALUES (‘20070101’,3,‘20070101-3’ )
INSERT INTO partitionDate VALUES (‘20070201’,3,‘20070201-3’ )
INSERT INTO partitionDate VALUES (‘20070301’,3,‘20070301-3’ )
SET @loop = @loop – 1
END
GO
–Create partition function for session
CREATE PARTITION FUNCTION [pfSessionRange] (INT)
AS RANGE RIGHT FOR VALUES (1, 2, 3);
GO
–Create partition scheme for session
CREATE PARTITION SCHEME psFunctionRange
AS PARTITION [pfSessionRange]
TO ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]);
GO
–Create new non-aligned index
CREATE NONCLUSTERED INDEX ncl_partitionDate_colSession_21 ON partitionDate(colSession) INCLUDE (colDesc)
ON psFunctionRange (colSession)
GO
Once we create the index, let’s look at the index information using the code from above. You will notice that the partitioning column is now the index key column.
Index Name Index ID Index Type Column Column Name Key Column INCLUDE Column Partitioning Column
ncl_partitionDate_colSession_21 2 NONCLUSTERED 1 colSession 1 0 1
ncl_partitionDate_colSession_21 2 NONCLUSTERED 2 colDesc 0 1 0
What does the nonclustered index look like when the table has been partitioned but no clustered index exists? Recreate the table from above but this time partition the table with the same format as before.
–Create main partition function for date
CREATE PARTITION FUNCTION [pfDateRange] (DATETIME)
AS RANGE RIGHT FOR VALUES ( ‘20060301’, ‘20060401’,
‘20060501’, ‘20060601’, ‘20060701’, ‘20060801’,
‘20060901’, ‘20061001’, ‘20061101’, ‘20061201’,‘20070101’,‘20070201’,‘20070301’);
GO
–Create partition scheme for date
CREATE PARTITION SCHEME psDateRange
AS PARTITION [pfDateRange]
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]);
GO
–Create table on main DateRange partition
CREATE TABLE partitionDate
(colDate DATETIME
,colSession INT
,colDesc VARCHAR(100)
) ON psDateRange(colDate)
GO
Now, recreate the non-aligned index.
–Create new non-aligned index
CREATE NONCLUSTERED INDEX ncl_partitionDate_colSession_21 ON partitionDate(colSession) INCLUDE (colDesc)
ON psFunctionRange (colSession)
GO
Take a look at the non-aligned again. You will notice that the heap is partitioned by colDate but the non-aligned index still only has the two columns we defined in the CREATE INDEX statement, the partition column of the table is not included in the index since the index key is the partition column of the index.
Note: This will stay the same even if you created a clustered index on the table.
I know, this post doesn’t have a lot of great information in it but I wanted to use it to set up the next post in this series. The next post will cover how aligned and non-aligned indexes affect the performance of SELECT queries against the table and its indexes.
