REPEATABLE READ

In SQL Server 2000, if you use Enterprise Mangler to script your tables, you need to watch out that your constraints don't get turned off. For example, here's a snippet generated from the Northwind.dbo.Employees table (just using the right click > All Tasks > Generate SQL Script wizard).

ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD 
    CONSTRAINT [PK_Employees] PRIMARY KEY  CLUSTERED 
    (
        [EmployeeID]
    )  ON [PRIMARY] 
GO

I find that that little injection of 'goodness' bites people all the time with SQL Server - so much so that I consider it a bug. Well, actually, I just consider it a bug merely because it's a horrible practice. (I can that you might want to create the table this way - so that you could load BAD data into it, but that should never be the default). MS obviously felt similarly about the problem, because it's fixed in SQL Server 2005. Actually, SQL Server 2005 generates the CONSTRAINTS with the WITH NOCHECK option in place, but then turns the constraints on as part of the object's definition - as follows:

ALTER TABLE [dbo].[Employees]  WITH NOCHECK ADD 
 CONSTRAINT [CK_Birthdate] CHECK  (([BirthDate] < getdate()))
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Birthdate]

Problem solved. (You may also notice that it creates PKs without the WITH NOCHECK clause - something that's in stark contrast to how SS 2000 did it...) Oh, and if you're curious to find out if you've got useless constraints in place, just run the following script which will tell you which, if any, constraints are not currently turned on:

SELECT
OBJECT_NAME(id) [Table],
OBJECT_NAME(constid) [Constraint]
FROM
sysconstraints
WHERE
OBJECTPROPERTY(constid, 'CnstIsDisabled') = 1