Answered by:
Column Store Index in SQL SERVER 2012

-
Hey Folks,
Hope you all are doing well there.
Here i come again with one of the intresting query , it would be really appreciable if any one have solution for the same.
My query is
As you all know, Column store Index is newbie to SQL SERVER and get introduced in SQL SERVER 2012, i have question based on the same.
Problem
I have table say Table A having column ColA,ColB and ColC.
-----------------------------------------------------
Data Type
ColA - BIGINT
ColB - NUMERIC(22,2)
ColC - INT
Primary Key
Composite primary key of (ColA and ColB)
----------------------------------------------------
Now when i am trying to create ColumnStore index based on ColA and ColB, i am getting below error
CREATE INDEX statement failed. A columnstore index cannot include a decimal or numeric data type with a precision greater than 18. Reduce the precision of column 'ColB' to 18 or omit column 'ColB'.
Please let me know, how to create Column Store index based on these columns
Note
1. It is mandatory for ColB to have Data type defined NUMERIC(22,2), cant tweek or change at table level.
2. As per ColumnStore index, it is mandatory to include all the primary key in Column Store definition
3. It is business requirement to include ColA and ColB as a Primary key.
Please let me know the solution, how to create Column Store index based on above condition.
Many Thanks
Amit
Please mark as answer if helpful
http://fascinatingsql.wordpress.com/
- Edited by Amit Srivastava Friday, May 09, 2014 4:55 AM
Question
Answers
-
I dont think you'll be able to create columnstore index with numeric or decimal column included having precision > 18
see below part from MSDN documentation
The common business data types can be included in a columnstore index. The following data types can be included in a columnstore index.
-
char and varchar
-
nchar and nvarchar (except varchar(max) and nvarchar(max))
-
decimal (and numeric) (Except with precision greater than 18 digits.)
http://technet.microsoft.com/en-us/library/gg492153.aspx
Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
- Proposed as answer by Manoj Pandey (manub22)Microsoft employee Friday, May 09, 2014 6:41 AM
- Marked as answer by Elvis LongMicrosoft contingent staff, Moderator Monday, May 19, 2014 2:29 AM
-
All replies
-
I dont think you'll be able to create columnstore index with numeric or decimal column included having precision > 18
see below part from MSDN documentation
The common business data types can be included in a columnstore index. The following data types can be included in a columnstore index.
-
char and varchar
-
nchar and nvarchar (except varchar(max) and nvarchar(max))
-
decimal (and numeric) (Except with precision greater than 18 digits.)
http://technet.microsoft.com/en-us/library/gg492153.aspx
Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
- Proposed as answer by Manoj Pandey (manub22)Microsoft employee Friday, May 09, 2014 6:41 AM
- Marked as answer by Elvis LongMicrosoft contingent staff, Moderator Monday, May 19, 2014 2:29 AM
-
-
Hi Visakh16,
Thanks for your reply.
Do you have idea that can we use cast while defining column store index.
Thanks
Visakh16
Partner, MVP
22,700 Points1152Recent AchievementsNew Wiki CommentatorNew Wiki EditorNew Wiki ContributorAmit
Please mark as answer if helpful
http://fascinatingsql.wordpress.com/ -
CAST cannot be used in CREATE COLUMNSTORE INDEX.
USE AdventureWorks2012; GO CREATE NONCLUSTERED COLUMNSTORE INDEX [FactResellerSalesPtnd] ON [FactResellerSales] ( [ProductKey], [OrderDateKey], [DueDateKey], [ShipDateKey], [EmployeeKey], [PromotionKey], [CurrencyKey], [SalesTerritoryKey], [SalesOrderNumber], [SalesOrderLineNumber], [RevisionNumber], CAST ([OrderQuantity] AS VARCHAR), [UnitPrice], [ExtendedAmount], [UnitPriceDiscountPct], [DiscountAmount], [ProductStandardCost], [TotalProductCost], [SalesAmount], [TaxAmt], [Freight], [CarrierTrackingNumber], [CustomerPONumber], [OrderDate], [DueDate], [ShipDate] ); /* Msg 102, Level 15, State 1, Line 15 Incorrect syntax near '('. */
Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012