Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

If I create an index e.g

Create nonclustered index(Path, Value, Id) with(sort_in_tempdb=on);

It doesn't fail but when looking at the index in SMMS, it says False. Any ideas why?

EDIT: Can not find any schema to query for the value of this setting. Tried sys.Indexes but with no luck.


share|improve this question

migrated from Jan 8 '12 at 16:46

This question came from our site for professional and enthusiast programmers.

up vote 4 down vote accepted

Option 'Sort in tempdb' valid only while creating an index. And after index has been created it (an option, not index) becomes senseless. Any other operations like inserts, updates and deletes does not apply sorting in tempdb, or even apply, but this has no any relevance to this option value used while creating index

share|improve this answer
How about when it updates the statistics? – Daniel Jan 8 '12 at 12:19
And just to get confirmed I understood: creating an index like that on an empty table is pointless? – Daniel Jan 8 '12 at 12:20
Yes - about empty table – Oleg Dok Jan 8 '12 at 12:21
@Daniel: the level of logical (not physical of the MDF) fragmentation is a measure on all insers, updates and deletes because of page splits, row overflow, forward pointers, deletes etc. It can be used to identify when to rebuild: – gbn Jan 8 '12 at 12:59
@Daniel - Fragmentation and statistics updates are completely orthogonal is the point I was trying to make. An index could have zero fragmentation but still be due a statistics update. – Martin Smith Jan 8 '12 at 13:07

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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