Create PK using temp db

Question

Post reply

Create PK using temp db

Sean Lambert

SSC Eights!

Points: 845
More actions
August 8, 2003 at 7:37 am

#160544

Does anyone know of a way to create a clustered primary key and have it use the temp db when it sorts to create the index like using SORT_IN_TEMPDB with CREATE INDEX? The ALTER TABLE syntax to add a PK constraint doesn't support anything like that. It would be nice to gain that added performance when creating clustered PKs on multi-million row tables.
August 11, 2003 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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

HenryTr SSC Eights! Points: 845 More actions · Answer 1

Do you really need it to be a defined PK, how about just a unique clustered index?

Sean Lambert SSC Eights! Points: 845 More actions · Answer 2

I guess the question has more to do with the index than the pk in that when you create a pk for a table whether it is clustered or not creates a unique index. When an index is created the intermediate sort runs are stored in the destination filegroup for that index, potentially creating an i/o bottleneck from having to read the data pages then write the results somewhere else on the same disk then go back and read again and on and on. The create index syntax allows you to specify that the database use the tempdb to store the intermediate sort results. The alter table syntax used to create the pk does not allow for this. My assumption is that even though the index is created implicitly the process is still the same which would mean that the intermediate sort results would be stored in the same filegroup as the index. When using very large tables it would be nice to get the added performance benefit of using the tempdb, which is on its own disk, to sort indexes. As well, though I'm more interested from a performance standpoint, using the tempdb to store sort results would be one less cause for incontiguous extents. Any thoughts?

jpipes SSChampion Points: 12230 More actions · Answer 3

I don't think it's possible with the ALTER TABLE syntax. But like Henry said, is there really any advantage to defining the PK in the ALTER TABLE statement instead of using CREATE INDEX for a unique clustered index?

Sean Lambert SSC Eights! Points: 845 More actions · Answer 4

How else would I create the primary key for the table? (other than when the table gets created) The only way to create the pk constraint would be to use the alter table syntax. It's the index that gets generated as a result of the adding the constraint that I was curious about. I guess that since the index gets generated by SQL Server behind the scenes there is no way to influence that action.

jpipes SSChampion Points: 12230 More actions · Answer 5

I think that's a fair assumption; that SQL Server performs the CREATE INDEX function in the background after the constraint is added, and that's why there's no control over where the index is created, which is unfortunate. Good discussion, and perhaps something to voice to the folks over on the Yukon project...heck, since SQL Junkies.com has direct access to them... (see: http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=14907&FORUM_ID=9&CAT_ID=1&Topic_Title=SqlJunkies%2Ecom%3A+Just+Launched+Web+Logs&Forum_Title=General)