Add primary key to a table with many records
I have a table in SQL Server 2005 containing 10000054 records; these records are inserted through a bulk insert operation. The table does not contain a primary key and I want to have one. If I try to modify the table's structure, adding a new column, PK, set as int with isidentity, the management console gives me a warning:
"Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible."
then outputs error:
" Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. "
I want a PK into my table.
How can I add it?
Answer
If in Management Studio you set the primary key in Design view (without saving), when you next right click you have an option "Generate Change Script" - this option is also available on the "Table Designer" menu at the top.
That provides the raw SQL (safely wrapped in a transaction) which you can copy to clipboard, take that over to run as a New Query (button top left, or File > New > Query with Current Connection), paste it in, select the right DB and execute the query.