SQL Server Performance

Primary Keys

Discussion in 'Performance Tuning for DBAs' started by varriam, Jul 31, 2003.

  1. varriam New Member

    I just inherited a database with about 300 tables. None of these tables
    have a primary key. How would I go about defining primary keys for these
    tables? How do I decide which columns to put the key on?
  2. Jon M Member

    Hello varriam,

    I suggest that you interview your client or users, gather some necessary information regarding the rules that govern the use of the data and existing Business Rules. I think the important thing here is that you need to know and understand why a specific table has been created because from this you will be able to at least identify their relationships. After doing so, this will give you an idea which should be the parent or reference table and child table.

    Jon M
  3. gaurav_bindlish New Member

  4. bambola New Member

    Take a close look at the stored procedures/queries that are running on this tables, the way they are joined. It can give you a better idea of how the tables relate to one another.

    Bambola.
  5. bradmcgehee New Member

    As you can see from the advice already given, there is no easy to way just to add clustered indexes to each of these tables. For the best performance, you will probably have to deal with them one at a time. One option you might consider to get your started is to capture a representative Profiler trace and run the Index Wizard against it to get some starting recommendations.

    From another perspective (and hopefully I won't generate too much hate mail for suggesting it), if you don't have time to carefully analyze each table, make the primary key a clustered index, and if there is no primary key, then add a clustered index to any column that increments. While this is very general advice that may or may not work in all cases, it is better than not having any clustered indexes at all, as you do now.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  6. gaurav_bindlish New Member

    Just to add to what Brad has said, most probably you know this, Primary Key is used to identify a unique record int the table. So find the list of unique columns in the table (combination or single) and then decide about the columns, combinations and order of columns in primary key.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Share This Page