Post reply

Query to find relational index options in sql server

  • February 8, 2016 at 4:44 am

    #324638

    Hi,

    I want to find the all the relational index options for a table, is there a query to find all these options?

  • February 8, 2016 at 5:04 am

    #1857089

    What exactly do you mean by 'relational index options'?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 8, 2016 at 5:19 am

    #1857092

    These are the index options

    IGNORE_DUP_KEY

    STATISTICS_NORECOMPUTE

    DROP_EXISTING

    ONLINE

    DATA_COMPRESSION

  • February 8, 2016 at 5:22 am

    #1857093

    DROP_EXISTING and ONLINE aren't properties of an index. They're just options you put onto create or alter index to change the way that statement behaves. They don't persist, they have no impact once the statements they're on are finished.

    IGNORE_DUP_KEY you'll find in sys.indexes.

    STATISTICS_NORECOMPUTE you'll find in sys.stats

    DATA_COMPRESSION you'll find in sys.partitions

    All of them are per index, not per table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 8, 2016 at 5:48 am

    #1857104

    yes, you were right, they are set options while creating/altering each Indices, I could see few are persisted and others are not (EX: ONLINE, SORT_IN_TEMPDB, STATISTICS_INCREMENTAL, MAXDOP). They are just set for the run time.

    Thanks for ur help 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

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