October 21, 2017 at 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.
October 21, 2017 at 3:43 am
kannan_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.
October 21, 2017 at 3:46 am
kannan_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?
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.
October 21, 2017 at 7:01 am
This is a known issue, see https://connect.microsoft.com/SQLServer/feedback/details/1860102
😎
October 21, 2017 at 8:04 am
Eirikur Eiriksson - Saturday, October 21, 2017 7:01 AM
Hello Eirik,
The link speaks about the index created on the computed column, but my query is index is not created even on the ordinary columns. This feature works perfectly in 2012 but not in 2016. Existing functionality is now stop working in 2016. Nothing special in 2016, Microsoft market this read, write column store as a feature enhancement but not succeed well..
October 21, 2017 at 8:12 am
kannan_egd - Saturday, October 21, 2017 8:04 AMEirikur Eiriksson - Saturday, October 21, 2017 7:01 AMHello Eirik,
The link speaks about the index created on the computed column, but my query is index is not created even on the ordinary columns. This feature works perfectly in 2012 but not in 2016. Existing functionality is now stop working in 2016. Nothing special in 2016, Microsoft market this read, write column store as a feature enhancement but not succeed well..
Would requst Eirik, to run my script in both 2012 and 2016 version of sql server, may know the difference and problem statement.
October 21, 2017 at 8:14 am
kannan_egd - Saturday, October 21, 2017 8:12 AMkannan_egd - Saturday, October 21, 2017 8:04 AMEirikur Eiriksson - Saturday, October 21, 2017 7:01 AMHello Eirik,
The link speaks about the index created on the computed column, but my query is index is not created even on the ordinary columns. This feature works perfectly in 2012 but not in 2016. Existing functionality is now stop working in 2016. Nothing special in 2016, Microsoft market this read, write column store as a feature enhancement but not succeed well..
Would requst Eirik, to run my script in both 2012 and 2016 version of sql server, may know the difference and problem statement.
Anyone is having any workaround for this problem?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply