Options for scripting an index

Question

Post reply

Options for scripting an index

Adam Bean

One Orange Chip

Points: 26631
More actions
August 27, 2008 at 6:27 am

#189765

Hey all, working on a new procedure for more or less everything related to an index and one area I'm having a hard time with is finding specific properties of an index.
Are there any values stored for an index definition for: STATISTICS_NORECOMPUTE, SORT_IN_TEMPDB, DROP_EXISTING? All the others I was able to find in sys.indexes.
Thanks

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

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

shripati-659987 SSC Veteran Points: 273 More actions · Answer 1

Hi

By default all the above 3 options are set to OFF.

If you want to use iit should be turned ON manually

Shree

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Indeed, but when creating a new index, are these definitions for the index stored?

GSquared SSC Guru Points: 260824 More actions · Answer 3

Those three characteristics aren't persisted, because they only apply to the command being run on the index, not to the index itself. They're run-time options that have no affect on the index once the command is completed.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

GSquared (8/27/2008)
Those three characteristics aren't persisted, because they only apply to the command being run on the index, not to the index itself. They're run-time options that have no affect on the index once the command is completed.

Ok cool, figured as much but wanted to make sure.

Thank you