Chaos Theory, Compound Effects, and Consequences.

Straight away I want to apologise for the Nicolas Cage memes!

User Groups are great, aren’t they?

I just got back from the Reading User Group and I’m still in that post “User Group Glow”, also known as “Long Day Lethargy”, or “Twelve Hour Tiredness”.

They are great though! A chance to talk to other people in the SQL Server community, – a slight reminder that even if you work alone, people are still experiencing some of the same problems that you are (apparently everyone has to deal with multiple nested views, who knew!) – a chance to hear presentations on different topics, and pizza if you’re lucky (we were).

WishYouWereHere.PNG
They’re really great!

I realised during the session that the two presentations given during the User Group had a connection with a small issue with a table change I had been given with a developer.

Here’s what did not happen to me so you can watch out for it.

The Chaos Theory

NicCageChaos.PNG
Nic Chaos

 

Raul Gonzalez ( blog | twitter ) was first up with this presentation “Database Design Matters, Seriously”, showing us the chaos that can occur from not giving some serious thought into how you design your database.

His session is not yet up on his blog as I’m writing this but it will be soon so keep an eye out for that!

Now he had a lot of good points but, for brevity’s sake, the main chaos theory points here are what happens if you don’t take advantage of CHECK CONSTRAINTS, FOREIGN KEY CONSTRAINTS, and not specifying a columns NULLABILITY (yes, that’s a word!). SQL Server is a powerful program with many performance optimizations provided for you, but it’s not omniscient; it can only use the information that you give it!

His points on NULLABILITY (I mean, I think it’s a word) tied in nicely with the next presentation…

Compound Effects

NicCageChaos.PNG
Compound Effects

David Morrison ( blog | twitter ) followed up with his presentation on “Query Plan Deep Dives” (I had seen this at SQL Bits, but it’s a great session so I had no problems watching it again) and, as an aside, through his presentation he showed us the compound effects that can happen from not specifying a columns NULLABILITY (it’s got letters so it’s word-like…)

Now his slides and scripts are up on his blog and they do a great job of walking you through them so check them out and you’ll see the compound effects they create!

Here’s a little teaser…


-- now I want all people who's email isn't in the email table
SELECT /*C.FirstName ,
    C.LastName ,*/
    C.EmailAddress
FROM dbo.Contact AS C
WHERE C.EmailAddress NOT IN (SELECT E.EmailAddress
                             FROM dbo.Emails AS E)

GO
NULLABILITY.png
This should be A LOT simpler!!!

Consequences

Which brings us back around to consequences or as I like to put it “How I Pissed Off A Dev By Refusing A Simple Request”.

To be quite honest, it was a simple request. A requirement came in to expand a column datatype up to varchar(100), so one of devs wrote up a simple script and passed it onto the DBAs to check as part of the change control procedure.

ALTER TABLE tablename
ALTER COLUMN columnname varchar(100)

And I said no.

WHY???!!!“, you may shout at me (he certainly did), but I’m going to say to you what I said to him. “Give me a chance to explain before you take my head off, alright?”

ArgumentInvalid.PNG
Argue with a DBA, go on!

While there is nothing wrong with the above code syntactically (is that a word?) but I couldn’t approve it since that column was originally NOT NULL and the above script would have stripped the column of that attribute! Business requirements dictated that it should not allow NULLS, and hey, who are we to argue with that 😐

Double checking to see if the column is NULL or NOT NULL allowed me to see a problem with that code, one that many people would consider simple enough to just allow it through at a quick glance. Which could have opened up problems further down the line if it had run…

Thanks to the User Group, I now know that it could have a knock on effect with our query plans as well!

ALTER TABLE tablename
ALTER COLUMN columnname varchar(100) NOT NULL

There, that’s better!

DBAs deal with databases and consequences

YDS.PNG

 

DBAs get a lot of stick sometime, the “Default Blame Acceptors” or the “Don’t Bother Asking” but a lot of the time, it’s not that we want to say no, it’s just that we have to take into consideration a thousand little things that could snowball into 1 giant problem.

With the rise of DevOps, check out the latest T-SQL Tuesday, DBAs have gone from going

“No”

to somewhere along the lines of

“Not this second, let me check it out and see what we can do”

If pressed further, we may rely on the good, old “it depends” though. Hey, clichés are there for a reason; they work!

It just goes to show that, like the IT profession, DBAs are constantly evolving.
Continuosly learning, checking out new helping technologies, and going to User Groups are going to help us to deal with it.

Just remember, in the end,

LeFin.PNG

P.S. I should probably mention that the Nicolas Cage memes are because of this blog post by Nate Johnson ( blog ) that I enjoyed so much that I had to do something in response. I’m not normally this crazy, I swear!

Semi-Unique Constraints.

When full uniqueness is a bit too much.

What are Unique Constraints? How can we enforce them and finally, is there anyway that we can have modified uniqueness…can we have Semi Unique Constraints?

Unique constraints.

There are many use cases that require that a column in a table only have unique values. The main example of this is a table’s PRIMARY KEY which, needing to uniquely identify a record, needs to be unique by default.

In fact, SQL Server enforces this uniqueness by creating a unique index on whatever column(s) the Primary key is defined on.


-- Drop Table if Exists.
IF OBJECT_ID(N'dbo.SemiUniqueColumn', N'U') IS NOT NULL
DROP TABLE dbo.SemiUniqueColumn;

-- Create Table.
CREATE TABLE dbo.SemiUniqueColumn
(
ID int IDENTITY(1, 1),
UniqueColumn int,
SemiUniqueColumn int,
CONSTRAINT [PK_SemiUniqueColumn] PRIMARY KEY CLUSTERED (ID)
);

-- Check Primary Key Exists.
EXEC sp_helpindex N'dbo.SemiUniqueColumn';

SemiUniqueConstraints_PK
Index Description : unique.

Unique But Not Primary

Primary keys are not the only options that can be unique, SQL Server recognizes this, and so there is the option of marking other columns as unique as well. Whether this be actioned by a UNIQUE CONSTRAINT or a UNIQUE INDEX is user’s perogative.

I’ll be creating this using a UNIQUE INDEX but for the purpose of completeness, the syntax for UNIQUE CONSTRAINTs is


ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column(s))

Now say we are forced to ensure that the column UniqueColumn is unique, so we create a UNIQUE INDEX on this.

-- Ensure UniqueColumn is Unique by Creating Unique Index on it.
CREATE UNIQUE NONCLUSTERED INDEX [UIX_SemiUniqueColumn_UniqueColumn] ON dbo.SemiUniqueColumn (UniqueColumn);
SemiUniqueConstraints_UIX
Index Description : unique.

We insert values into this table and, as long as the uniqueness of these rows are satisfied, we’re going to have a good time.


-- Insert Data.
WITH RecordSet (SeqNum) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)
INSERT INTO dbo.SemiUniqueColumn (UniqueColumn) SELECT SeqNum FROM RecordSet ORDER BY SeqNum DESC;

-- Select Data.
SELECT ID, UniqueColumn, SemiUniqueColumn FROM dbo.SemiUniqueColumn;

SemiUniqueConstraints_TblSelect.png
All Unique = All Good.

When Is A NULL Not a NULL?

Short answer, when you try and compare it to another one.

Now you may think that I’ve lost the plot and gone off on a tangent but bear with me here a second and I’ll explain.

A NULL is UNKNOWN and an unknown value should not equal another unknown value. What happens if your first unknown turns out to be the number 1 and the second unknown is the number 2? 1 <> 2 so comparing them then would be pretty foolish.

If you ever look at some generated script and see at the very top of the script, hiding amongst the XACT_ABORTs and the NOCOUNTs is another option called ANSI_NULLs (although not anymore as it’s on by default and should be left that way IMHO).
This ensures that NULLs are treated as unknown and cannot be compared to another unknown except for some specific cases (GROUP BY, UNION ALL, INTERSECT, EXCEPT, etc)

What Does This Mean For Us?

Good question! Remember our Unique Index on UniqueColumn? What happens if we run the following?


-- Insert NULL into UniqueColumn.
INSERT INTO dbo.SemiUniqueColumn
( UniqueColumn )
VALUES
( NULL );

It inserts no problem, going all the way from 7442, 7441, … 2, 1, NULL. What about if we run it again?

SemiUniqueConstraints_UniqueColumn2ndNull
An Index is apparently exempt from NULL <> NULL.

Semi Unique Constraints.

Now ask yourself the question, what happens if we are told that our other column SemiUniqueColumn can have as many NULLs as it wants but if it gets a value, that value must be unique?


-- Generate Semi Unique Values.
-- Every Sixteenth Value is NULL.
UPDATE
original
SET
SemiUniqueColumn = IIF((original.UniqueColumn % 16) = 0, NULL, original.UniqueColumn)
FROM dbo.SemiUniqueColumn AS [created]
JOIN dbo.SemiUniqueColumn AS [original] ON created.ID = original.ID;

-- Select Data.
SELECT ID, UniqueColumn, SemiUniqueColumn FROM dbo.SemiUniqueColumn;

SemiUniqueConstraints_SemiUniqueColumn.png
Multiple NULLs But How to Force Uniqueness?

I used to think that this would be a complex requirement, possibly requiring a TRIGGER or two to check the inserted value against whatever is already there; but there is a way to have this functionality and have it the way that SQL Server normally would enforce a uniqueness on a column; by using a UNIQUE INDEX.

In case you’re thinking…

“Oh, a unique index doesn’t check what’s already there, is that it?”

I’m afraid that’s not the case.


-- Standard Unique Index Treats NULLs = NULLs.
CREATE UNIQUE NONCLUSTERED INDEX [UIX_SemiUniqueColumn_SemiUniqueColumn] ON dbo.SemiUniqueColumn (SemiUniqueColumn);

SemiUniqueConstraints_FailUniqueIndex.png
Won’t Create As Values Aren’t Unique

Yes, I know that we just showed that SQL Server treats NULLs as equal on Indexes but there is a way around this,  and that’s to not use a simple unique index.

We simply ignore the NULLs altogether by using a UNIQUE FILTERED INDEX.


-- Filtered Index.
CREATE UNIQUE NONCLUSTERED INDEX [UFIX_SemiUniqueColumn_SemiUniqueColumn] ON dbo.SemiUniqueColumn (SemiUniqueColumn)
WHERE SemiUniqueColumn IS NOT NULL;

Don’t trust me? Trust but verify!

First attempt to insert a duplicate value in SemiUniqueColumn:


-- Test it (Duplicate).
INSERT INTO dbo.SemiUniqueColumn (UniqueColumn, SemiUniqueColumn) VALUES (0, 7439);

SemiUniqueConstraints_FUI_Dup
7439 Is Already There So It’s Rejected.

Now we attempt to insert a duplicate value in SemiUniqueColumn but attempt to insert a duplicate NULL.


-- Test it (Not Duplicate).
INSERT INTO dbo.SemiUniqueColumn (UniqueColumn, SemiUniqueColumn) VALUES (0, NULL);

SemiUniqueConstraints_FUI_NotDup
NULL Is Already There But We Don’t Care.

Finally, NULL was chosen just for example purposes, filtering can be done on any value but, at the moment, there are a couple of got’cha’s with them…

Conclusion

I think you’ll agree that this would be so much easier to manage and troubleshoot than multiple triggers.

In fact, indexes are extremely useful aspects of databases and, once you start to realise how they work, you can start to use them for various different things than simple seeks and scans.

But the main aspect here is that complexity is not king. There are a multitude of ways to achieve something, so take a few moments to think about the problem you are trying to face and you’ll be thankful in the long run.