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:
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”)
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