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
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 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.
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.
