kannan_egd - Saturday, October 21, 2017 3:46 AM

kannan_egd - Saturday, October 21, 2017 3:43 AM

kannan_egd - Saturday, October 21, 2017 2:17 AM

Dear 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)
GO

CREATE PARTITION SCHEME [MY_PS] AS PARTITION [My_PF] ALL TO ([PRIMARY])
GO

CREATE 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?