I have inherited SQL Server 2005 database with several tables without primary
keys. Should I add auto increment column to uniquely identify the record to
the tables without primary key before running the profiler and database
tuning advisor?

Can the database tuning advisor recommend adding primary key to tables with
them missing?

Please help me with this these topics.


Thank You,

On Mon, 2 Feb 2009 14:50:01 -0800, Joe K. <Joe
K. (AT) discussions (DOT) microsoft.com> wrote:

Hi Joe,

Far more important than performance is integrity and correctness. If
your tables have no primary keys, you can not guarantee correctness, as
there is no way to prevent duplicate data to be entered. Adding an auto
increment column will not fix this, for even though you now appear to
have a proper key, you only have a surrogate. You can still enter
duplicate data and the database will happily assign new key values for
it. The only advantage of the surrogate key is that cleaning up the mess
becomse slightly less awkward.

You should review your table design. For every table, look up the
underlying entity type in the data model. Find out what the identifying
attributes are. The column or combination of columns corresponding to
those attributes should then become the key of the table.

After that, you can consider adding surrogate key values for some of
your tables, but only for those tables where they are useful.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis