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? Commented Jan 8, 2012 at 12:19
  • And just to get confirmed I understood: creating an index like that on an empty table is pointless? Commented Jan 8, 2012 at 12:20
  • Yes - about empty table Commented 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 Commented 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. Commented 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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.