Rotating Header Image

My #SQLFamily and Community Battery is Charging Up!!


Every year around this time I feel so drained when it comes to my extra curricular SQL activities… and every year the PASS Summit rolls around to give that battery a recharge and gets me back excited about everything SQLish in nature.

This year is no exception… I find myself back wanting to blog more and present more and just be the Colin that I love to be.

Not sure what this means going forward but I’m definitely going to have to do my best to see my SQL Peeps more often!!

<3 #SQLFamily <3


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

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

PASS Summit 2013, Bullying, and Stuff

The PASS Summit is fast approaching and it felt even closer this week when I uploaded my PowerPoint slide decks and got the email from PASS HQ that Craig Purnell and I should get ready for the dump of presentations that our team will be going through over the next several weeks.

This is my first time as a member and leader of the PowerPoint review team so I’ll be relying heavily on Craig and his past experience as a review team member to make this a smooth and efficient process for our team.

As a first time Summit speaker I started thinking about how excited/nervous I am for this years Summit.

Excited cause the PASS Summit is awesome and I get to see so many #SQLFamily members who I only see once a year.

Nervous cause this is my first time presenting at the Summit and I hope the SQL community enjoys the sessions that I’m putting on.

I read Tom’s post on Bullies – Not Just On The Playground and it really hit home with me.

If you haven’t given it a read yet I hope that you do.  We have an amazing and supportive community and I hope it continues to be this way for years to come.


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

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

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
CREATE DATABASE [FunWithFunctions]
USE FunWithFunctions
CREATE TABLE dbo.tblCustomers
	CustomerName NVARCHAR(50) NULL,
	CustomerEmailAddress NVARCHAR(50) NULL

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))
	DECLARE @ReturnValue INT = 0
	SELECT	@ReturnValue = COUNT(CustomerID) FROM tblCustomers WHERE CustomerEmailAddress = @EmailAddress
	RETURN	@ReturnValue

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)

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('')

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)

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)
('Clark Kent', '')

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


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 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.


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

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