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