Delete and Constraint Handling Stored Procedure Using SQL Server 2000

This is an older article I wrote on Code Project. The solution stems from a very unique problem I encountered when assigned to fix a WinForms application created by a nightmarish codegen. It deals with SQL Server 2000 and is probably no longer applicable, but definitely an interesting read. If anything it may inspire a WTF.

Here is an excerpt:

I was providing consulting services, developing an application in C# and .NET 2.0 with a SQL Server 2000 database. The application framework was based on a custom code-generator, which created SQL scripts, business objects, and UI code. Because the previous consultant who designed the code-generator was no longer available, time constraints restricted us from discovering and modifying the generator, leading me to take a different approach to my solutions.

In this article, I describe how I created the ability to perform deletes. The framework did not include proper scripts for cleaning up and deleting records, and for reasons beyond the discussion of this article, I chose to do all of the constraint checks and deletes from a stored procedure, and return an error message if any constraint violations would occur from a delete.

The solution actually involves permanently modifying master database and system level objects. When I wrote the article I may not have clearly stated the reason for the solution in the first place. SQL Server 2000 does not do well when trying to cascade delete a record that has an unimaginable number of restraints on very large tables. If you look at the master level stored procedure, it actually checks the constraints, lets the delete process throw the error anyway. This results in very slow deletes. In this case it could take up to a minute to return an error. The solution is actually an aim to rewrite those stored procedures to behave correctly. I wouldn’t recommend it. I would never do it again. But in this case, I had no choice. Here is the article for a full implementation:

http://www.codeproject.com/KB/database/sql2kdeletesconstraints.aspx

Advertisements

About Jeremy Mullinax-Hill

I am a lead/senior developer with more than 10 years experience in both the public and internal applications for Fortune 500s and mid-sized View all posts by Jeremy Mullinax-Hill

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: