Hi In a single table how many columns at maximum I can have as a PK, I hope we call it as composite key. How does it effect the performance? Thanks! "He laughs best who laughs last"
Hi Reddy, BOL says in "Maximum Capacity Specification" , "Columns per primary key : 16 " . Regards Hemantgiri S. Goswami ghemant@gmail.com "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
theorically yes it is 16 but try to limit this as there can be performance issues ---------------------------------------- Cast your vote http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka
You would want to keep your PRIMARY KEY as compact as possible. Remember that all column in the PRIMARY KEY *must* be present as FOREIGN KEY columns in all child tables. The more (and wider) those columns, the more you blow up space needed. Also, the more columns participate in a PRIMARY KEY, the more complex will be your code. You would have to specify all columns when JOINing tables. JOINs will also be slower when there are more columns to process. Another point arises when your PRIMARY KEY is also your clustered index. In that case, all clustered index keys (e.g. all your PK columns) are stored as part of the bookmark in each and every nonclustered index on that table. It depends, but when you have a natural composite PK on multiple columns, seriously evaluate the use of a surrogate PK. In terms of performance and maintenance you might be better this way. -- Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
