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

SQL Server Showdown (EDMPASS July 25, 2013)

Next week I will have the pleasure of presenting for my local PASS Chapter (EDMPASS) on a new presentation I have put together called: SQL Server Showdown.

This is a presentation that I will be giving at the PASS Summit this year in Charlotte.

Below is the abstract for this presentation.

If you are a member of a PASS Chapter or SQL Server User Group and are interested in getting a “sneak-peak” at this presentation before the PASS Summit please feel free to drop me a line and we can try and setup a meeting :)

SQL Server Showdown

If you are feeling pressure to get into the cloud but don’t know where to start, you are not alone. Microsoft has offered some form of “cloud” SQL since 2010, but many DBAs and developers are still in the dark about what the cloud really offers. This introductory session will give you an understanding of the different SQL Server platforms available and what they can do for you, turning any fear and uncertainty you might have had into confidence and excitement.

We’ll begin with a review of the three SQL Server platform offerings:

• Traditional “out of box” SQL Server

• Windows Azure SQL Database

• Windows Azure VM-hosted SQL Server

We’ll compare the platforms against each other, going over the pros and cons of each, and then work through some use cases and demos on how each offering can help you meet the needs of your business. We’ll see that the cloud platforms need not be seen as replacing traditional “out of box” SQL Server deployments but rather as additional weapons in your arsenal.

Thanks!!

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

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

My First PASS Summit (as a speaker)

It’s official… I will be presenting two sessions at PASS Summit 2013 in Charlotte, North Carolina.

I’m honored to have been selected and I can’t wait to see my #SQLFamily members who will be in attendance this year.

I don’t want this to sound like a cheesy acceptance speech at some awards show but I do want to thank all the people who I was able to bounce ideas and abstracts off of to get their suggestions and feedback.  Your comments and critiques were invaluable.

See you all in Charlotte!!!

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

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

Now What?

The PASS Summit 2013 call for speakers has come and gone and from the looks of things there were a TONNE of last minute abstract entries.

I am a first time abstract submitter for the Summit.  I’ve graduated from talking to myself, to speaking at PASS Chapters, to speaking at SQL Saturdays and Microsoft events so this felt like my next logical step in my speaking career.

I’ve been lucky enough to be “behind the scenes” in the Program Committee a couple times in the last few years so I know what happens now but if you’re a first time abstract submitter here is the scoop:

The first part of the process is the Speaker and Abstract Reviews.  (I believe) for the first time PASS is separating the speaker from the abstract which in my opinion has a bunch of pros and a bunch of cons.  A good reason for doing this is to not be “star struck” by an amazing speaker and automatically put them in, the flip side of that is that there is a reason you are “star struck” and that’s because they are an amazing speaker LOL.  My concern in this structure is that if an amazing speaker’s abstract doesn’t really POP (even though you know they always deliver) there is a chance they will not get in. There is obviously going to be differing opinions on this new change but it’s worth a shot and I’m going to try and keep an open mind about it.

(I’ve been on the Abstract Selection Team before and it’s no easy task and you potentially can take ALOT of abuse from speakers who are not chosen so please remember that everyone is a volunteer and no one single person makes a decision so be nice!! :)   )

This process takes us into late May / Early June-ish which is when the speakers find out if they’ve been selected and around mid June the presentations are revealed publicly.

From there everyone who is selected works on tweaking their presentations and PowerPoint decks so that the PowerPoint Review Team (which I am a co-leader for this Summit) can start reviewing the decks/demos around August up until the Summit to make sure there are no issues (not complete, not matching the abstract, identify spelling/grammatical errors, etc)

Hopefully this very high level view of the process helps the first time speakers / submitters know what to expect in the coming months leading up until the 2013 PASS Summit

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

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

Summit 2013 – Team Lead for the PPT Review Team

I’m happy to announce that I’ve been selected to the Program Committee for the PASS Summit in 2013!!

I’ll be taking on the role of Team Lead for the PPT Review Team.

I’ve served on the Program Committee before as a member of the Abstract Selection Team and it was a great experience and a way to help shape what the Summit looked like for that year.

I can’t wait to meet my team and get the schedule ahead of things to come.

SPEAKERS – April 3rd is the deadline for abstract submissions so get them in ASAP!! (and I’m talking to myself here as I’m still putting the finishing touches on my abstracts hahaha)

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

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

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