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!

Connecting Mathematica to SQL Azure Using Microsoft’s JDBC Drivers

This post will walk you through establishing a database connection in Mathematica to SQL Azure using Microsoft’s SQL Server JDBC driver. SQL Azure is Microsoft’s cloud based database service. I’ve personally enjoyed using SQL Azure for working with data where latency isn’t a factor. A personal example of where it’s made my life easier is that I’ve used SQL Azure in the classroom to expedite the grading process, dropping the amount of time from days to hours between work done by the students and their reflected grade. I’ve received a lot of compliments from my students for being able to do so.

This walk through connects to the SQL Azure AdventureWorks database which you can download here.

The first thing to do is download the JDBC driver and unpack it to C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\.

Once the driver is unpacked, I navigated to sqljdbc_3.0 and removed the sqljdbc.jar file and left sqljdbc4.jar. When I was working with Wolfram support (who, by the way, are extremely knowledgable and professional), we had some issues with ClassPath and the two drivers. The way we resolved this is by removing that file.

Once you’re just left with sqljdbc4.jar, open up Mathematica and create a new notebook file. The notebook will require both Jlink and DatabaseLink so use the Needs[] command to import both of those.

Next, we’ll need to add the Microsoft JDBC driver to the class path so execute:

AddToClassPath[“C:\\Program Files\\Microsoft SQL Server JDBC Driver\\sqljdbc_3.0\\enu\\sqljdbc4.jar”];

Like so:

Then execute:


Like so:

Now it’s time to connect to SQL Azure with Mathematica. The command and parameters you’ll use look like the following:

queryazure = conn=OpenSQLConnection[JDBC[“”,”jdbc:sqlserver://<SERVER>;databaseName=AdventureWorksLTAZ2008R2;user=<USERNAME>@<SERVER>;password=<PASSWORD>;”],”Username”->”<USERNAME>@<SERVER>”,”Password”->”<PASSWORD”]

My connection appears as the following (I’ve blotted out the sensitive information):

Once the connection is made, you can confirm your connection by executing


Finally, let’s query for something specifically. Below is a screenshot looking at the SalesLT.Product table via the web interface:

One may can also connect with SQL Server Management Studio 2008 R2 as well:

The query we will want to execute is a simple one: “SELECT * FROM SalesLT.Product”. To do so, we just execute the following:

query =SQLExecute[queryazure, “SELECT *

FROM SalesLT.Product”]

The output is as follows:

That’s it! Enjoy playing with Mathematica and SQL Azure! There’s lots of blog posts on different things to do and they can be applied to SQL Azure just the same. Some of my favorites are: Graphing a Hierarchy from a Common Table Expression Using TreePlot, Graphing Foreign Keys in SQL Server Using Mathematica, and Foreign Keys as an Incidence Matrix: A High Level Look.

Finally, I want to credit Wolfram support for their assistance in getting me to connect. They worked with me in fixing my erronious JDBC configuration in Mathematica. Without them, this post wouldn’t be possible. Thank you Wolfram support!

Next Page »