Post reply

Create index ... online=ON

  • October 1, 2019 at 8:34 pm

    #3685132

    Why is this INDEX setting not persistent ?  I'm testing this on SQL2019, but I've seen the same thing on SQL2016.

    I run this

    CREATE NONCLUSTERED INDEX [ImageActID] ON [dbo].[Image](

    [ImgID] ASC

    )WITH (SORT_IN_TEMPDB=OFF, DROP_EXISTING=ON, ONLINE=ON, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ON [PRIMARY]

    GO

    Then I refresh and right click on the index and select "Script index as CREATE to ..."  And this is the results

    CREATE NONCLUSTERED INDEX [ImageActID] ON [dbo].[Image](

    [ImgID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

    GO

  • October 1, 2019 at 10:45 pm

    #3685156

    If I had to guess, my guess would be that they're using the default settings instead of actual settings.  That's terrible, of course, but I see little that can be done about it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 2, 2019 at 7:57 am

    #3685216

    I would say that particular keywords of the create/alter index statement are part of that command definition, not part of the object that is object of the command

    object static properties - e.g. properties that are part of the object definition and therefore part of its metadata - see sys.indexes

    • PAD_INDEX
    • FILLFACTOR
    • IGNORE_DUP_KEY
    • ALLOW_ROW_LOCKS
    • ALLOW_PAGE_LOCKS
    • OPTIMIZE_FOR_SEQUENTIAL_KEY
    • DATA_COMPRESSION

    create/alter index only options e.g. only valid for the current statement

    • MAXDOP
    • SORT_IN_TEMPDB
    • STATISTICS_NORECOMPUTE
    • STATISTICS_INCREMENTAL
    • DROP_EXISTING
    • ONLINE
    • RESUMABLE
    • MAX_DURATION

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

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