Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Unable to create column store index on a partitioned table holding computed column RE: Unable to create column store index on a partitioned table holding computed column
October 21, 2017 at 4:13 am
kannan_egd - Saturday, October 21, 2017 3:46 AMkannan_egd - Saturday, October 21, 2017 3:43 AMkannan_egd - Saturday, October 21, 2017 2:17 AMDear Friends,
I am trying to create a column store index on my table that holds a computed column, i am aware that we will not create column store index on a computed column, but i am trying to create non cluster column store index other than than the computed column on the partitioned table as non aligned index (not partitioned) but SQL server 2016 stills throws an error
CREATE PARTITION FUNCTION [My_PF](bigint) AS RANGE LEFT FOR VALUES (142002, 142092, 142183, 142275, 142367, 142458, 142549, 142641, 142733, 142823, 142914, 143006, 143098, 143188, 143279, 143371, 143463)
GOCREATE PARTITION SCHEME [MY_PS] AS PARTITION [My_PF] ALL TO ([PRIMARY])
GOCREATE TABLE Column_Store_Intex_Test(
[Employee_ID] varchar NULL,
[Date_Of_Joining] [date] NOT NULL,
[Dept_ID] [varchar](50) NOT NULL,
[LastModified_Date] [datetime2](0) NULL,
[Location_Key] [Tinyint],
[Partition_Key] AS (CONVERT([bigint],concat([Location_Key],CONVERT([bigint],CONVERT([datetime],Date_Of_Joining))))) PERSISTED
)ON [MY_PS]([Partition_Key])
CREATE NONCLUSTERED COLUMNSTORE INDEX CSINDX_Test
ON Column_Store_Intex_Test(Employee_ID,Date_Of_Joining,Dept_ID,LastModified_Date,Location_Key)Error Msg: The statement failed because column 'Partition_Key' on table 'Column_Store_Intex_Test' is a computed column. Columnstore index cannot include a computed column implicitly or explicitly.
Big Bug in Microsoft SQL server 2016. Unable to create column store index on a partitioned table. The above script works perfectly in sql server 2012.
Dear Friends would request all to stop migrating SQL server 2016 if you have a plan for the migration. The above script works perfectly in SQL server 2012 without any issues. Recently we migrated our server to 2016 and trying to create a column store index in our production server but failed. Will Microsoft will give any patch for that?
Changing the database comparability to SQL server 2012 is also not working.