1

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.

//Daniel

Improve this question

1 Answer 1

Reset to default
4

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

Improve this answer
10
  • How about when it updates the statistics?
    – Daniel
    Jan 8, 2012 at 12:19
  • And just to get confirmed I understood: creating an index like that on an empty table is pointless?
    – Daniel
    Jan 8, 2012 at 12:20
  • Yes - about empty table
    – Oleg Dok
    Jan 8, 2012 at 12:21
  • 1
    @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: technet.microsoft.com/en-us/library/ms189858.aspx
    – gbn
    Jan 8, 2012 at 12:59
  • 1
    @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, 2012 at 13:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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