<div>
Why? The existence of the foreign key is not checked for 6 million rows but only a few rows of metadata in sys.foreign_keys and since your alter table adds a constraint with NOCHECK, this shouldn't take long, as it doesn't check the 6 million rows. <br />
<br />
If anything does take long, then it's not this code but something else you don't show here.<br />
<br />
Bye, Olaf.
</div>


<div>
Hey Olaf<br />
That was my thoughts, but there is nothing else in the script that affects this code just more of the same. &nbsp;and yet it runs for a weekend and no completion :( &nbsp; I have pretty much read around the subject as completely is I think possible &nbsp;I am hoping someone else has had a similar problem and found a solution.
</div>


<div>
Sure, you don't end with something like ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL?<br />
I still stand to this, a constraint added with NOCHECK does not check rows, so the size of the table will not matter. You need to look for something else.<br />
<br />
Bye, Olaf.
</div>


<div>
No Olaf I highlighted the code as written and execute it and I agree with you, I was hoping for someone to have had this experience and found a way around it. &nbsp;That may never happen, I just need to give this a fair amount of time before I decide what to do next.
</div>


<div>
A lock should be a good explanation but what make a table to be locked for a full weekend?<br />
Do you have any job running? Something like an Import/Export or even a maintenance job scheduled for weekends?<br />
<br />
Also if you don't want to use sp_WhoIsActive you can use the built in <b>sp_who2</b> and check for the rows with <b>blocked</b> column &lt;&gt;&nbsp;0.
</div>


<div>
Well hats off to you, &nbsp;Adam Mechanic tool soon found out that a user was simply ignoring the administrators instruction to cease and desist use. &nbsp;Many thanks for the Cluedo tool.
</div>


<div>
<div class="content wysiwyg-content">
I Have had to rebuild a database for a customer and whilst most of this process has worked well I have come up against a problem. &nbsp;On three tables when I run code similar to that below:<br />
<br />

<pre><code class="code-1 nolinks" id="code-10-28961441-1"><span>IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_GroupUserMap_User]') AND parent_object_id = OBJECT_ID(N'[GroupUserMap]'))</span>
<span>ALTER TABLE [GroupUserMap]  WITH NOCHECK ADD  CONSTRAINT [FK_GroupUserMap_User] FOREIGN KEY([UserInfoKey])</span>
<span>REFERENCES [UserInfo] ([UserInfoKey])</span>
</code></pre>
<br />
It is taking an inordinate amount of time to do 6 million rows. &nbsp;Is there a way top improve this performance?<br />
<br />
The SQL SERVER 2012 installation is on a virtual server which I have no administrative rights to and the customer has fairly limited IT resources too.
</div>
</div>


I Have had to rebuild a database for a customer and whilst most of this process has worked well I have come up against a problem.  On three tables when I run code similar to that below:

(CODE)

It is taking an inordinate amount of time to do 6 million rows.  Is there a way top improve this performance?

The SQL SERVER 2012 installation is on a virtual server which I have no administrative rights to and the customer has fairly limited IT resources too.

Applying constraints to a large table

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.