Post reply

Create COLUMNSTORE index on XML based column

  • April 17, 2018 at 8:52 am

    #328459

    Hello SSC,

    I am looking to optimize a bunch of SQL Stored procedures. A few of the tables have columns of the XML datatype. The data in the XML columns are very wide and the tables have over 1 million rows.

    My plan was to throw a COLUMNSTORE index on the XML column, but I have never used them before. I ran a few searches on Google about Columnstore indexes, but nothing was said about XML.

    My question... Is this allowed and/or would it make sense to do this? What is the overhead? What will I have to do to maintain the columnstore index?

    Any help would be greatly appreciated.

    Everyone has a plan until they get punched in the mouth. --Mike Tyson

  • April 17, 2018 at 12:17 pm

    #1987226

    Microsoft documentation on Columnstore:

    Columns that use any of the following data types cannot be included in a columnstore index:

    • ntext, text, and image
    • nvarchar(max), varchar(max), and varbinary(max) (Applies to SQL Server 2016 (13.x) and prior versions, and nonclustered columnstore indexes)
    • rowversion (and timestamp)
    • sql_variant
    • CLR types (hierarchyid and spatial types)
    • xml
    • uniqueidentifier (Applies to SQL Server 2012 (11.x))

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 17, 2018 at 12:40 pm

    #1987236

    Thanks Grant.

    Everyone has a plan until they get punched in the mouth. --Mike Tyson

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply