SORT_IN_TEMPDB index option

Question

Post reply

SORT_IN_TEMPDB index option

rob.patterson-1085747

SSC-Addicted

Points: 434
More actions
June 22, 2009 at 11:23 am

#384818

Can anyone tell me where SQL Server stores this option?
I have looked in sysindexes, sys.indexes and also used indexproperty() and indexkey_property() and have found every other index option EXCEPT sort_in_tempdb.
Where the heck is it?????

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Sorry, but according to BOL (Books Online) that information is not stored in metadata.

rob.patterson-1085747 SSC-Addicted Points: 434 More actions · Answer 2

Lynn Pettis (6/22/2009)
Sorry, but according to BOL (Books Online) that information is not stored in metadata.

Yeah, I saw that in BOL also. But it HAS to be stored somewhere. How else does it know when you right click on an index and select "Script Index as" and then "Create to" if SORT_IN_TEMPDB is ON or OFF?

This has to be stored somewhere, right?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

rob.patterson (6/22/2009)
Lynn Pettis (6/22/2009)
Sorry, but according to BOL (Books Online) that information is not stored in metadata.
Yeah, I saw that in BOL also. But it HAS to be stored somewhere. How else does it know when you right click on an index and select "Script Index as" and then "Create to" if SORT_IN_TEMPDB is ON or OFF?
This has to be stored somewhere, right?

No. From BOL:

The SORT_IN_TEMPDB option affects only the current statement. No metadata records that the index was or was not sorted in tempdb. For example, if you create a nonclustered index using the SORT_IN_TEMPDB option, and at a later time create a clustered index without specifying the option, the Database Engine does not use the option when it re-creates the nonclustered index.

rob.patterson-1085747 SSC-Addicted Points: 434 More actions · Answer 4

rob.patterson-1085747

SSC-Addicted

Points: 434

June 22, 2009 at 1:15 pm

#1013535

okay, I get it. thanks for your help.:-)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Pretty poor that this value ISN'T stored and reused if you ask me - or even if you don't!! 😛

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ashish_vbtecno-598437 Valued Member Points: 51 More actions · Answer 6

HI ,

Go to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource..

open it on notepad and search your index name over here.

Regards

Ashish Gupta

Abhijeet Chavan SSCommitted Points: 1511 More actions · Answer 7

Hi ashish

thanks for the reply.

I only found 1 mdf and 1 ldf of the same name. I attached it but i didnt find anything useful in it.

chetanr.jain Hall of Fame Points: 3938 More actions · Answer 8

@Ashish

Go to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource..

open it on notepad and search your index name over here.

We cannot open the database File ( data or log) in a notepad or any text editor. IT can be read only by database engine.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

chetanr.jain (8/24/2010)
@Ashish
Go to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource..
open it on notepad and search your index name over here.
We cannot open the database File ( data or log) in a notepad or any text editor. IT can be read only by database engine.

Not that I am saying you SHOULD do this, but if you turn off the sql server process you can open this file with a hex file reader.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service