up vote 1 down vote favorite

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

share|improve this question
75% accept rate
feedback

migrated from stackoverflow.com Jan 8 at 16:46

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

1 Answer

up vote 3 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 at 12:19
And just to get confirmed I understood: creating an index like that on an empty table is pointless? – Daniel Jan 8 at 12:20
Yes - about empty table – Oleg Dok Jan 8 at 12:21
What do you mean about statistics? – Oleg Dok Jan 8 at 12:23
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 at 12:59
show 5 more comments
feedback

Your Answer

 
or
required, but never shown
discard

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.