4

I'm in a situation where I want to add another included column to an existing index, but keep the rest of the settings (eg. FILLFACTOR, ONLINE, SORT_IN_TEMPDB, etc.) the same.

  • I do not have the previous version in source control
  • I didn't find most of them in sys.indexes DMV (FILLFACTOR is there, but not ONLINE)
  • Finding the index in Object Explorer and scripting it out fills default values for the ONLINE setting (and omits DATA_COMPRESSION)
  • I didn't find the property in the OBJECTPROPERTY DMF
  • Brent Ozar's sp_BlitzIndex also doesn't provide some of these advanced settings
  • sp_helpindex provides me with the same limited information as sp_help over the parent table.
  • I cannot test if WITH (DROP_EXISTING = ON) recreates the index with the same settings, because I can't find the settings anywhere.

Any ideas?

|improve this question
  • Have you tried with INDEXPROPERTY? – McNets Aug 28 '19 at 8:22
  • @McNets I have not as I was not aware of it :). But reading the documentation it doesn't provide the properties I mentioned in my post (eg. ONLINE). – Zikato Aug 28 '19 at 9:30
  • 2
    I don't think things like ONLINE and SORT_IN_TEMPDB are properties of an index, but more like instructions on how to build/rebuild the index. Once the index is built, those options are meaningless. – Scott Hodgin Aug 28 '19 at 10:29
  • 1
    FYI DROP_EXISTING = ON removes settings such as a custom FILL FACTOR – Randi Vertongen Aug 28 '19 at 10:55
5

You should probably play with scripting options in Tools-Options-SQL Server Object Explorer-Scripting in SSMS menu.

There you can define what you would like to script and what not. Compression is there.

As for the other properties - have a look at this StackOverflow question - it will probably shed some light:

SORT_IN_TEMPDB is not a property of an index, but rather an option that you can specify that says how the index is created. Once the index exists (i.e. the create index statement completes), whether or not SORT_IN_TEMPDB was specified at creation time isn't stored anywhere because it doesn't matter.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.