Sign in
 
HomeLibraryWikiLearnGalleryDownloadsSupportForumsBlogs

none
Column Store Index in SQL SERVER 2012

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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/




    Friday, May 09, 2014 4:47 AM
    |

Answers

  • Question
    Sign in to vote
    2
    Sign in to vote

    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

    Friday, May 09, 2014 6:04 AM
    |

All replies

  • Question
    Sign in to vote
    2
    Sign in to vote

    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

    Friday, May 09, 2014 6:04 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Visakh16,

    Thanks for your reply.

    Do you have idea that can we use cast while defining column store index.

    Thanks

    Avatar of Visakh16

    Visakh16

    Partner, MVP

    22,700 Points1152
    Recent Achievements
    New Wiki CommentatorNew Wiki EditorNew Wiki Contributor

    Amit
    Please mark as answer if helpful
    http://fascinatingsql.wordpress.com/

    Friday, May 09, 2014 10:21 AM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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



    Friday, May 09, 2014 10:27 AM
    |
    Moderator