June 7, 2004 at 9:49 am
This is probaby a basic question. I want to drop a primary key from a table and basically add a clustered index (with the UNIQUE option applied) that would take its place. This is basically providing the same unique contraint function. Right? There is no reason to add primary keys to a unique clustered index, right? Our analysts always require a primary key. However, I don't really need a primary key in this case?
Specifics on the table: The table currently has a single primary key defined on columns 1, 2, and 3 (date,time,seq; an the date and time are CHAR data types! but don't go there for now because this can't change anytime soon). There are no indexes on the table; that primary key is it. I want to drop the primary key and create a clustered index (with the UNIQUE option) on columns 1,2,3.
Wouldn't the clustered index be more efficient, since less data would have to be read into memory (reduce paging). Thanks for any advice!
smv929
June 7, 2004 at 11:47 pm
By default, a primary key is supported by a unique clustered index, so by dropping the key and creating the index you won't be gaining anything.
You will gain if the key is nonclustered (check in enterprise manager. Right click table-> manage indexes) If so, recreate the key as clustered, but you will gain nothing from dropping the key if it is already clustered.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply