Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

migrated from stackoverflow.com Jan 8 '12 at 16:46

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

1 Answer 1

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
    
What do you mean about statistics? –  Oleg Dok Jan 8 '12 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 '12 at 12:59

Your Answer

 
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.