SQL Server Performance

primary keys

Discussion in 'Getting Started' started by lara99, Oct 7, 2009.

  1. lara99 New Member

    hi all, i am using sql 2000 is there a way in sql 200 to create the primary key on a large tableefficiently,right now i do it through t-sql and it takes lot of time.please through some ideas.lara
  2. ndinakar Member

    when you create the PK SQL Server will run the unique constraint check to ensure the PK'ness for existing data.. which will take time depending on how much data you have.. one work around could be to create a new table with PK, do a bulk insert into it, drop the original table and rename the new table...
  3. FrankKalis Moderator

    [quote user="ndinakar"]
    when you create the PK SQL Server will run the unique constraint check to ensure the PK'ness for existing data.. which will take time depending on how much data you have.. one work around could be to create a new table with PK, do a bulk insert into it, drop the original table and rename the new table...
    [/quote]
    ...and even in this case you risk that the operation may be aborted if a violation of the PK is detected. So, either way the data has to be checked at one point.
  4. Adriaan New Member

    Couple of factors that come to mind ...
    (1) the number of columns in the PK
    (2) do you already have a clustered index on the table
    (3) are you creating the PK as a clustered index
    (4) does the table already have non-clustered indexes
    (5) fill factors
    If the table already has other indexes, but no clustered index, then the PK will be created as a clustered index (unless you specifically mention nonclustered on creation). Any existing non-clustered indexes will be rebuilt, based on the new clustered index.
    With a multi-column PK, it is better to create a clustered index on a "narrow" key, like an identity column. If you create a multi-column PK as clustered index, this can add a lot to the build time.

Share This Page