Check Constraints in SQL Server and First Order Logic

I have to give credit where credit is due, this post is inspired largely by a chapter in C.J. Date‘s book, “SQL and Relational Theory: How to Write Accurate SQL Code“. I would recommend the book to any database professional. Domain integrity and its enforcement is a crucial component of good database design. One of the tools SQL Server includes to help enforce domain integrity is check constraints. Check constraints help play an important role in database design because they can extend domain enforcement beyond mere primitives. This is particularly important because effective database design incorporates the enforcement of business rules into its design. For example, let’s say that you work for a bike company named (*cough*) Adventure Works. Your business has a business rule that product weight must be greater than zero. This is a fair thing because, at least in the world we operate in, weight isn’t a negative thing. In the AdventureWorks2012 database, turns out there’s example of such a thing in the Production.Product table.

If we look at the check constraint, we can see a very basic definition.

That’s great and all but what if businesses decided it wanted to something more complex? What if Adventure Works decided that they won’t allow bike blades to more than five pounds? I actually don’t know if this is true. In fact, I’ve always wanted to be a little bit more handy with my bike. We run a quick SELECT * against the Production.Product database and we can see that the blade has a ProductID of 316.

At first blush, this may seem like an IF THEN statement would do just the trick. That, if we were to modify the CHECK constraint for product weight that we may wish to have something along the lines of, IF ProductID = 316 then Weight > 0.00 AND Weight <= 5.00.

When we look at the documentation for CHECK constraints, there doesn’t seem to be a way to incorporate IF THEN logic into a CHECK constraints. Perhaps then a user defined function or UDF might seem to be in order. A potential UDF CHECK constraint might look like the following:

We add the constraint to the table (I dropped the previous check constraint for pedagogical purposes):

If we perform a simple unrestricted update such as the following:

We get the following error message:

Msg 547, Level 16, State 0, Line 3

The UPDATE statement conflicted with the CHECK constraint “CK_PRODUCT_WEIGHT”. The conflict occurred in database “AdventureWorks2012”, table “Production.Product”.

The statement has been terminated. Let’s see what happens when we do something simple like perform an unrestricted update:

The outcome looks like this:

SQL Server parse and compile time:

CPU time = 2 ms, elapsed time = 2 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Table ‘Product’. Scan count 1, logical reads 1012, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 110 ms.

(504 row(s) affected)

UDFs do not have a very good reputation. And using them in CHECK constraints can lead to performance issues and unexpected results as well.

Perhaps there is a better way. Think back to that freshman level Introduction to Logic course you took so long ago. There was a special identity for IF THEN statements. It was IF P THEN Q = NOT P OR Q. This simple logical equivalency can come in handy in a lot of places, especially where there isn’t support of IF THEN statements but there is for simple AND/OR operations. Here’s the truth table:


Instead of looking to a UDF to perform domain integrity, we can use a simple logical disjunction instead. So, instead the CHECK constraint might read:

The same unrestricted query looks like this:

SQL Server parse and compile time:

CPU time = 15 ms, elapsed time = 49 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

Table ‘Product’. Scan count 1, logical reads 1012, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 16 ms, elapsed time = 13 ms.

(504 row(s) affected)

113 ms compared to 13 ms?!? The difference between the CHECK constraint using an UDF and one using a simple NOT P OR Q is separated by almost a factor of 10. This is a simple table with a tiny amount of data, 504 rows. Imagine what this looks like at scale with millions of rows. A little bit of logic can save a lot of time. Moral of the story? Where and when you can, use logic and relational algebra, it will usually yield better database performance.

I know I linked to it in this blog post already but I want to call it out again, please read Gail Shaw’s post on UDFs. It’s very good:

Oh, and I think a bike blade might be a type of fender. At least, that’s what my searches on tell me.

Installing SQL Server 2012

Microsoft has released their new version of SQL Server, SQL Server 2012. This guide will walk you through getting SQL Server 2012 and installing it. The first thing to do is get the software. To do this, just follow this link and click on the Download SQL Server 2012 button. If you’re on a 64-bit computer, you’ll want to download ENU\x64\, ENU\x64\, and ENU\x64\SQLFULL_x64.ENU_Install.exe.

If you’re on a 32-bit system, you’ll want the 32 bit equivalents available below these options. Once you’ve downloaded those files, find SQLFULL_x64.ENU_Install.exe and execute it.

The software will unpack and create an install directory.

Find the install directory that the software unpacked to, named SQLFULL_x64_ENU, click inside it and double click on the bootstrapper file called SETUP.

A window called the SQL Server Installation Center will open up. The first option is Planning. If you are not familiar with SQL Server and want to go through a few of the options there, feel free. Once you are ready to install, click on the Installation link on the left hand portion of the window. The usual selection here is select, “New SQL Server stand-alone installation or add features to an existing installation”. Select that option.

The next window will just show the progress of SQL Server performing some sanity checking. If there’s any errors here, you’ll want to view the detailed report and see what is wrong with your setup. This isn’t that common.

Once done, click OK. The setup process will ask you for a key. If you have a key, you may enter it, otherwise you may select either “Evaluation” or the SQL Server Express option. For our purposes, just select the evaluation option and hit Next.

Accept the license terms and hit Next.

After that, more preparation in the way of setup files will occur. Once that is done, hit Install.

SQL Server Setup will perform some additional sanity checks to make sure there’s no problems with your computing environment. Everything should check out. If it does, hit the Next button to continue.

Now we’re getting to the point where we get to start making some choices with respect to our installation. Since we want to get the full SQL Server experience, select the first option, “SQL Server Feature Installation” and hit the Next button.

Since this is our first installation of SQL Server 2012 (that is why you’re following this guide, right?), we’ll likely want to play with all the available options. If that is the case, hit the “Select All” button below the Features window. If not, you’ll want to scan through the features and cherry pick the ones you wish to install. When you’re comfortable with your choices, hit the Next button.

More sanity checking. If you’re curious, click, “Show details”, elsewise hit Next to continue.

If you have more than one copy of SQL Server installed (in my case, I have both a copy of SQL Server 2008 R2 and a copy of SQL Server Express), you’ll need to create a named instance for your SQL Server 2012 installation. In this case, I imaginatively called mine SQL2012. If you’re installing SQL Server for the first time, it will make your life a lot easier if you just use the default instance.

After you hit Next, SQL Server will want to make sure that you have enough room on disk for your installation. Review the disk usage summary and hit next if you have enough room. You should.

SQL Server 2012 Setup will now ask you for the account names and collation specification. If this is a production box, you’ll want to follow your organization’s service account best practices. If you’re installing this on your home computer for testing and learning purposes, you may just wish to accept the defaults here and hit Next.

The database engine configuration options are up next. Under the Server Configuration tab, select the Authentication Mode you wish to have. I usually pick Windows authentication mode for my test installations unless I need Mixed Mode. You’ll want to add whatever accounts you wish to have administrative access to your SQL Server 2012 installation. Typically, it’s at least the account you’re installing the software from if not others as well. Once that is done, click the Data Directories tab.

Here you may change where SQL Server stores various files as well as the defaults for when you create new databases. I’m on a laptop with just once hard drive, the C:\ drive, so I’m going to keep the folders as they are. Many times on a production server, you’ll be changing these fields here. Once you’re done, click the FILESTREAM tab.

Starting with SQL Server 2008, SQL Server has been able to include unstructured data as part of a logical database structure via FILESTREAM. This addresses the common situation where images or documents may be held on the filesystem while referencing paths and attributes are held within the database. Learning how to work with FILESTREAM is highly recommended, so I would suggest turning it on. Once done, click the Next button.

Since were installing SSAS as well, select the mode you wish, Multidimensional and Data Mining Mode or Tabular Mode. Make sure to add an account that will have administrative permissions as well, usually at least the one you’re installing from. Then click the Data Directories tab.

Here, if you wish to change where SSAS files are located and default locations for new ones, modify the paths to where you wish those files to be located. Once finished, click the Next button.

Next up is the Reporting Services Configuration window. I typically just select the defaults here, “Install and configure” for Reporting Services Native Mode and, “Install only” for Sharepoint Integrated mode. Select the option that makes sense to you and hit Next.

Next up, SQL Server 2012 Setup will ask you about how to set up the Distributed Replay Controller. I have yet to explore Distributed Replay but from the documentation, you’d want to use it when:

  • You want to evaluate application compatibility. For example, you want to test SQL Server and operating system upgrade scenarios, hardware upgrades, or index tuning.
  • The concurrency in the captured trace is so high that a single replay client cannot sufficiently simulate it.

Specify the users you wish to have permissions for the service and hit Next.

Next up, specify the distributed replay client. I just set up my own computer in this case since this is a stand alone installation.

Next up, select the options you wish to have for error reporting. I tend to give Microsoft access to my errors. I’m not particularly paranoid about that kind of information. Select the option that aligns best with your own personal persuasion.

SQL Server will then wish to perform more sanity checking. If your system passes muster, click Next.

SQL Server will give you one final review window before it installs. Review everything here and then click Install and it’s off to the races.

Walk away, take a nap, go on a walk, do something and once you’ve returned, hopefully the window below will be waiting for you. Congratulations! You’ve successfully installed SQL Server 2012. Smile to yourself and click the Close button.

Just to make sure that everything is in working order. Find your SQL Server 2012 installation and select SQL Server Management Studio.

Once it opens, it’ll ask what server you wish to connect to. Here, I type in the name of SQL Server 2012 instance name and click Connect. If you have a default instance, you can just go to localhost to connect.

Once you’ve successfully connected, you’ll have a screen like mine below sans the AdventureWorks2012 database. To get the AdventureWorks2012 database, go to the Adventures Works for SQL Server 2012 Codeplex page. You’ll want to attach the database file once you’ve downloaded it. If you’ve never done that before, follow the tutorial here.

Once you have the AdventureWorks2012 database installed, it’s time to play! If you’ve never played with SQL Server before, you may wish to get started here. Happy querying!

Building a Simple Map in SSRS 2008 R2

In a previous post we discussed mapping the SQL Server geography data type in Mathematica. Now we’re going to build a simple map in a SSRS report using the same query as before. To start, we’ll open Visual StudioBusiness Intelligence Development Studio (BIDS) and create a simple data source connection. Right click on Shared Data Sources and click on, “Add New Data Source”.

In the next window that pops up, name the connection (here we just called it Localhost) and then click on the Edit button.

In the window that pops up, enter the server and the database that you would like to connect to.

You can click the Test Connection button if you wish but really, if you can select the database, you can connect to the database. Click OK twice to create the shared data source.

Once that is done, we will want to create a report. As usual, you can just right-click on Reports to get the menu. If you click on, “Add New Report”, it’ll take you to the Add New Report Wizard. I prefer to just create a blank report and create from there. To do that, click on Add -> New Item…

Select the Report option and give it a name. I named mine, Top 500 Sales Around the World.

Next, click on your Toolbox pane, select the Map object, and drag it over to your report.

When you drag it over, a new window will pop up with options for adding a new map layer. Select the option for a SQL Server spatial query and hit, “Next”.

The next window asks if you wish to choose an existing dataset or create a new one. We will want to create a new one (it’s at the bottom). So select that option and then hit the Next button.

Click the “New…” button on the next window that pops up to create a new data source.

On the Data Source Properties window, name the connection something sane and then select the, “Use shared data source refence” option and in the dropdown list, select the shared data source we created before. Click OK.

Then click Next.

The next window that pops up will want you to create a query. Select, “Edit as Text” and enter the following query:

SELECT TOP (500) Address.SpatialLocation

FROM Person.Address AS Address INNER JOIN

Sales.SalesOrderHeader ON Address.AddressID = Sales.SalesOrderHeader.ShipToAddressID

Click Next. SSRS will detect that the values we are using are points. With the points themselves just being points in space, you’ll want some form of underlying map to plot them on to. At the bottom, select the option to add a Bing maps layer and click Next.

Select the type of marker map you want to use and click Next.

Select your final options such as theme, marker types, and color options and then click Finish.

Once that is done, you can click on the Preview pane and see your shiny new SSRS map!

Please note that while my map is called Top 500 sales locations, it isn’t a top 500 in the usual sense (500 highest grossing sales). It’s merely the first 500 rows in the Person.Address table that have matching values in the Sales.SalesOrderHeader table. Building a map in SSRS is impressively easy.

Next Page »