The first thing to do is get the software. This guide will walk you through getting SQL Server 2012 and installing it. Microsoft has released their new version of SQL Server, SQL Server 2012. At least, that’s what my searches on REI.com tell me. Oh, and I think a bike blade might be a type of fender. 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. Moral of the story? Where and when you can, use logic and relational algebra, it will usually yield better database performance. A little bit of logic can save a lot of time. Imagine what this looks like at scale with millions of rows. This is a simple table with a tiny amount of data, 504 rows. The same unrestricted query looks like this:ġ13 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. So, instead the CHECK constraint might read: Instead of looking to a UDF to perform domain integrity, we can use a simple logical disjunction instead. 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. There was a special identity for IF THEN statements. Think back to that freshman level Introduction to Logic course you took so long ago. And using them in CHECK constraints can lead to performance issues and unexpected results as well. 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. Let’s see what happens when we do something simple like perform an unrestricted update: ![]() The conflict occurred in database “AdventureWorks2012”, table “Production.Product”. The UPDATE statement conflicted with the CHECK constraint “CK_PRODUCT_WEIGHT”. If we perform a simple unrestricted update such as the following: We add the constraint to the table (I dropped the previous check constraint for pedagogical purposes): A potential UDF CHECK constraint might look like the following: Perhaps then a user defined function or UDF might seem to be in order. 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. 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. We run a quick SELECT * against the Production.Product database and we can see that the blade has a ProductID of 316.Īt first blush, this may seem like an IF THEN statement would do just the trick. In fact, I’ve always wanted to be a little bit more handy with my bike. ![]() 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. If we look at the check constraint, we can see a very basic definition. In the AdventureWorks2012 database, turns out there’s example of such a thing in the Production.Product table. This is a fair thing because, at least in the world we operate in, weight isn’t a negative thing. Your business has a business rule that product weight must be greater than zero. For example, let’s say that you work for a bike company named ( *cough*) Adventure Works. This is particularly important because effective database design incorporates the enforcement of business rules into its design. Check constraints help play an important role in database design because they can extend domain enforcement beyond mere primitives. One of the tools SQL Server includes to help enforce domain integrity is check constraints. ![]() Domain integrity and its enforcement is a crucial component of good database design. I would recommend the book to any database professional. Date‘s book, “ SQL and Relational Theory: How to Write Accurate SQL Code“. I have to give credit where credit is due, this post is inspired largely by a chapter in C.J.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |