Create index ... online=ON

• Budd

  Hall of Fame

  Points: 3781

  More actions

  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

• Jeff Moden

  SSC Guru

  Points: 1004601

  More actions

  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)
  

• frederico_fonseca

  SSCoach

  Points: 16226

  More actions

  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

  • This reply was modified 5 years, 8 months ago by frederico_fonseca.