question

BartekOz avatar image
BartekOz asked

Do not get include column when partitioning nonclustered indexes

I'm wondering if this is still valid what I found on Microsoft pages and on other sites whent it comes to partitioning nonclustered indexes: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms187526%28v%3dsql.105%29

It's written that: "When partitioning a nonunique, nonclustered index, SQL Server adds the partitioning column by default as a nonkey (included) column of the index to make sure the index is aligned with the base table."

I was doing many attepmts and never get partitioning column in included columns in the index. Ie. in the example below I will not get myorder_id column in included columns.

--Partition Function
CREATE PARTITION FUNCTION pf_myorder (int) AS RANGE RIGHT FOR VALUES(0, 10000, 20000, 30000, 40000)
GO
-- Partition Scheme 
CREATE PARTITION SCHEME ps_myorder AS PARTITION pf_myorder ALL TO ([PRIMARY])
GO
--Partitioned table
CREATE TABLE dbo.myorders
(
     myorder_id        INT NOT NULL
     , myorder_date    DATETIME NOT NULL
     , CONSTRAINT pk_myorderid PRIMARY KEY CLUSTERED (myorder_id ASC)
              ON ps_myorder(myorder_id)
)
GO
-- Partition index which should have gotten myorder_id column in included columns as theory says 
CREATE NONCLUSTERED INDEX ix_myorder_date
    ON dbo.myorders (myorder_date)
    ON ps_myorder (myorder_id) 
GO

indexingpartitioningnonclustered-index
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.