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?
ONLINE
andSORT_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.DROP_EXISTING = ON
removes settings such as a customFILL FACTOR