xgcmcbain (2/19/2009)
1) When creating a primary key on a column, is it still necessary to create an index on that column? or would that index serve good another purpose?
No. Primary keys are enforced by an index
2) if you create a primary key on two fields combined, is it still necessary to create index's on them individually? or would that index serve good for another purpose?
Not usually. Certainly not on the leading column of the primary key. Maybe on the second, but it depends on queries
3) does creating foreign keys (relationships) have any cons? how much do they increase performance?
Foreign keys are for data integrity and usually don't have an affect on performance
4) at what point do u have to rebuild indexes?
Rule of thumb is 30% fragmentation. In some cases that may be high, in some cases low.
5) are there any advantages or disadvantages to creating indexes as constraints instead make a difference or have pros and cons?
If your index is not unique, it can't be a constraint. Constraints can't have include columns, indexes can.
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