Rotating Header Image

Check Constraints That Call Functions

Originally this blog post was going to be about how using check constraints that call functions basically eliminates SQL Server’s ability to do set-based operations and I was going to setup an example that inserted 10 rows to show that because of the check constraint that calls a function SQL Server ends up calling the function 10 times (once per row inserted).  Once I started building my test scripts to show this I stumbled and fell face first into what I think is almost a worst side effect of using check constraints that call functions.

Disclaimer: The use case I’m going with (checking the uniqueness of a column with a function) I understand can be “solutioned” much better with a unique constraint but this is not to show the best way to do something, rather a tricky little problem with doing it with a check constraint that calls a function.

OK first lets build ourselves a test db and table:

USE master
GO
CREATE DATABASE [FunWithFunctions]
GO
USE FunWithFunctions
GO
CREATE TABLE dbo.tblCustomers
	(
	CustomerID int NOT NULL IDENTITY (1, 1),
	CustomerName nvarchar(50) NULL,
	CustomerEmailAddress nvarchar(50) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.tblCustomers ADD CONSTRAINT
	PK_tblCustomers PRIMARY KEY CLUSTERED
	(
	CustomerID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Now we’re going to build our function that returns the count of CustomerIDs that have the same email address:

CREATE FUNCTION dbo.fnEmailAddressUniqueness(@EmailAddress AS nvarchar(50))
RETURNS INT
AS
BEGIN
	DECLARE @ReturnValue INT = 0
	SELECT	@ReturnValue = COUNT(CustomerID) FROM tblCustomers WHERE CustomerEmailAddress = @EmailAddress
	RETURN	@ReturnValue
END
GO

OK now we’re going to add a check constraint that uses this function to make sure that there are no other Customers with that email address (CustomerEmailAddress = 0)

ALTER TABLE dbo.tblCustomers ADD CONSTRAINT CK_fnEmailAddressUniqueness CHECK (dbo.fnEmailAddressUniqueness([CustomerEmailAddress])=0)
GO

Fantastic… now this was where I was going to do my insert of 10 rows and show through a profiler trace that my function was going to get called 10 times (once per row) but this happened:

(click to enlarge)

Ummmmm weird…. what happened?

I thought maybe there was something wrong with my function at first so I decided to call the function manually:

SELECT dbo.fnEmailAddressUniqueness('Superman@gmail.com')

That returned 0 (zero) as expected so I was left scratching my head for a while trying to imagine how a check constraint that is checking to make sure a value is 0 would fail (especially on an empty table) then it hit me….

This check constraint that is calling a function is not getting evaluated BEFORE the insert… but DURING/AFTER.

To test this theory I changed the ADD CONSTRAINT statement to this (after I dropped it):

ALTER TABLE dbo.tblCustomers ADD CONSTRAINT CK_fnEmailAddressUniqueness CHECK (dbo.fnEmailAddressUniqueness([CustomerEmailAddress])=1)
GO

And wouldn’t you know it… I could now insert data.

I wasn’t satisfied with just that… I wanted to actually see with my own two eye that it was actually inserting the row, checking the constraint (that calls the function), and removing the row because of the failure.

As dirty as this is, in one SSMS window I did this (using the GO 100000 statement to execute the following statement 100,000 times):

INSERT INTO dbo.tblCustomers(CustomerName, CustomerEmailAddress)
VALUES
('Clark Kent', 'Superman@gmail.com')

And then in a different SSMS window I ran this statement continually:

SELECT * FROM tblCustomers WITH(NOLOCK)

and I did this till the planets aligned and I was able to execute it at the exact split second I needed to capture this:

and then the next time I ran the same SELECT it was gone! (notice the CustomerID 5601)

So I stopped the “GO 100000″ statement that was continually trying to insert that single row and ran the rest of my insert (without the Superman@gmail.com row) and the rest of the data went in fine (with the CustomerID being in the 9000’s even though those IDs really never were actually used”)

Neat/Scary huh?

Anyways I found this to be quite surprising and I figured that I wouldn’t be the only one who would so I thought I’d do a post on it.

Have a great day!

If you’d like to chat with me about this or anything else (SQL or other) please leave a comment or hit me up on my Twitter: @ColinStasiuk

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

6 Comments

  1. Grady Laughlin says:

    Thanks for posting this! I was pulling my hair out.

  2. Mike says:

    Thank you for this. I needed a constraint that prevents adding a duplicate row for 48 hours. My function worked fine but the constraint not so much.

  3. FenixR says:

    Albeit and old post, i found a “workaround” or “solution” for this problem, you just have to include an identity column in the table, and pass the identity of the added row as a parameter to the function, so you can exclude it with a where clause during the select statement.

    Considering Identity Columns are pretty much 100% unique and will never repeat themselves (Unless you reseed the whole thing), it will only exclude the inserted row.

  4. Tom says:

    I second Grady’s sentiments. I knew the check constrain was my issue, but could not prove it to my DB, who created it. I did resist saying I told you so though.

  5. RaMaS says:

    Constraint checks take place at row level. Maybe it’s useful to check out this about limitations of the constraint checks:
    http://msdn.microsoft.com/en-us/library/ms188258%28v=sql.105%29.aspx

  6. In day-to-day smartphone usage, zoom isn’t that important and you can easily add bokeh effects in post with a variety of photo apps if it’s something you really, really need.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.