This blog is where I post about my consulting work with Microsoft Technologies, and other random tidbits that don't fit in my Photo Blog or my Iraq Blog.

Tuesday, July 15, 2008

My client doesn't allow the use of Foreign Key Constraints in SQL Server

I'm working on a project at a Fortune 500 Microsoft customer and ran across something interesting: They DO NOT allow foreign key constraints in production SQL Server databases. The theory being that they have negative performance impact, and that developers can check for related data as needed in stored procedure code.

This is new to me, but I wanted to double check with some SQL Gurus before I call their baby ugly... :-)

Here is a great response from my friend Brian:

"Their baby is UGLY! But you really can't tell a parent that!

They are correct, you do take a noticeable hit ( seen up to 5% on heavily updated DB's)
In particular you can see a lot locking and deadlocks.

However I've yet to see development staff that consistently check the data or deal properly with errors and/or partial rollbacks! I'll put money on the fact they have corrupt data!

I like FK's because they 'document' the DB and I use them to automate data archiving.

That said, I am in the process of converting my current app to ignore FK relationships.
(We do 24,000,000 + insert/update/deletes daily so performance is an issue)

I define relationships in the DB.
Enable then in Dev and Test to facilitate testing.
Disable them in perf and prod for performance

Once a day/week check the FK's. Not much more expensive than DBCC CheckDB
IF we find corrupt data, we enable the FK so we throw errors to facilitate route cause analysis.
Note this happens regularly after a code release ;) A little less often when new customer/data patterns hit the DB.

While this is not a 'Best Practice' it is a better than what they are doing today. "

Example code:

-- Create the tables and populate with valid data
use tempdb

if object_id('child') is not null drop table child
if object_id('parent') is not null drop table parent

create table parent (id int primary key)
create table child (id int primary key, pid int)
alter table child with nocheck add constraint FK_child_parent foreign key ( pid) references parent(id) -- Use 'with nocheck' so FK can be added even if existing data is corrupt

insert into parent values (1)
insert into parent values (3)
insert into child values (1, 1)
insert into child values (3, 3)

-- Try adding invalid data
insert into child values (2, 2)

-- Disable FK and try again
alter table child nocheck constraint FK_child_parent
insert into child values (2, 2)

-- Check the table
DBCC checkconstraints (FK_child_parent)

-- Enable the FK so code now throws errors
alter table child with nocheck check constraint FK_child_parent -- Use 'with nocheck' so existing corrupt data is ignored, but new data will error
insert into child values (2, 2)

1 comment:

Fred Wiersma said...

Many, many moons ago I worked as Oracle DBA - in Oracle6 the data dictionary was redefined, and you could specify primary & foreign keys - but they weren't enforced! That came with Oracle7 onwards.

I've never seen performance degradations BECAUSE of using FKs. I did see a lot of apps who weren't aware of (properly) using them. In my experience DBA's usually knew a lot about PKs and FKs, and developers next to nothing... In a lot of apps developers just happily re-coded FK functionality in PL/SQL, go figure...

I'm not familiar with SqlServer, so is this performance degradation a MS f*ckup?