Set primary key in very large database

Question

I have a very large table (around 13 million rows) and I want to set the primary key for the tables. The problem is that given the large size of the database my computer crashes when I try to set the primary key for a table.

I suppose that SQL Server tries to set this key in every row it finds, so the memory consumption reaches the limit of my computer (6gb of memory). Is there any effective and alternative way to set the primary key without having these issues?

Answer 1

Here are a couple of options that might work:

1. Create a new table with the same columns and a primary key, and select into it from the other table.
2. Create a change script and and disable the execution timeout in SSMS

Here's a change script to point you in the right direction:

ALTER TABLE tableName WITH NOCHECK 
ADD CONSTRAINT PK_tableName PRIMARY KEY CLUSTERED (columnName)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON)