Jeff's SQL Server Blog

Why Single-Column Primary/Foreign Keys Can't Always Accurately Model/Constrain Your Data

A Simple Single-Child Model

Consider the following logical data model:

• There are multiple Companies
• Each Company has many Projects
• Each Project has many Tasks
• Each Task has a Status, selected from a global list of pre-defined Statuses.

Let us say that we decide that the primary key of Companies, Projects, Tasks, and Status are all Identity (auto-number) columns, since we would like to auto-generate primary keys for those tables.

So, we have something like this:


At this point, things work fine -- we can enforce full referential integrity with our identity primary keys. We can't necessarily eliminate duplicate data, but suppose that is not an issue and/or we have unique constraints in place.  Choosing to follow the "every primary key must be an identity rule" did not have any adverse affects and we have a nice, clean data model.

Parents with Multiple Children

Now, allow me to add one little wrinkle.  Suppose that the available Statuses for each Task is not defined globally, but at the Company level.  That is, each Company has its own list of Statuses to which a Task can be assigned.


This means that the Status table now needs a Foreign Key reference to the Companies table (to indicate which Company each Status belongs to), giving us:


Are there any other changes we need to make to this data model?  Or is simply adding a CompanyID column to the Status table enough to facilitate this change?  Remember, our goal as always is full referential integrity using primary and foreign key constraints wherever possible.

Well, there is an issue:

Nothing in this data model stops us from assigning a Status to a Task that is not defined for that Task's parent company.   We have no way of enforcing this right now with our current constraints.  Our physical data model is flawed.  (This is also described a bit here.)

Identities Are Part of the Solution

This is very easily fixed, but it can only be done by violating the "all tables just need an identity primary key" rule.

First, remember this: just because an identity column is unique does not mean that that column cannot be part of a primary key.  So, even though maybe you decide that you really like integer primary keys for your Status table (when probably a simple CHAR(2) code is all you need), if Status codes are child entities of Companies then we can instead create a composite primary key like this:

So now, with the two column primary key and one of those columns referencing another table, Statuses are truly a child entity of Companies.  Even though it seems that having a two-column primary key is not necessary since the StatusID column in itself is unique, you will see that to physically enforce the very simple logical model we are working with, it's required!

Now, just as Statuses are child entities of Companies, so are Projects.  And as with the StatusID column, we have a nice unique identity ProjectID column that is unique enough that it could be our primary key -- but it shouldn't be.  Once again, being a child entity of a Company, and with the goal of wishing to enforce integrity throughout our entire model, we find that the true primary key of our Project table needs to be a composite of CompanyID/ProjectID:

None of this seems necessary yet -- why are we messing with our perfectly fine keys? -- but soon it will all make sense.

Looking at Tasks, we know we have a foreign key reference to Projects, and we know the primary key of Projects is now (CompanyID, ProjectID).  Also, remember that we changed our Status table as well to have a composite primary key including the CompanyID column.  Thus, we now know that we need to add a CompanyID column to our Tasks table to enforce these FK constraints:

OK, so we are making our primary keys bigger when it doesn't seem necessary, and now our foreign key constraints are getting more complicated as well since they now involve multiple columns.

Why did we bother doing all this?

Because now we have full, complete referential integrity, that's why!

Examining the Results

With these changes in place, only Status codes belonging to a Task's Company can be assigned to that Task.  By propagating the CompanyID column down to our child tables, we have the column necessary to enforce full referential integrity as dictated by our logical model.

So, in the end, it looks like this:


Just this little tweak in our logical model -- that each Company has their own list of Statuses -- required all of these changes in the physical database schema!  What gives?

Well, remember: if the logical model changes, the physical data model should change. If it doesn't, then you are probably not modeling it correctly or accurately to maintain strict data integrity in the first place.

"All-identity-all-the-time" data modelers often claim that only using identities makes things easier and "more flexible". As we can see from this example, it can work in some cases (back when Statuses were global), but not in every case.  Sometimes, if your goal is to enforce strict data integrity, you simply cannot create your physical schema that way.  If you want to approximately model your data, then all identities can be nice. If you want to accurately model your data, then it simply is not always possible with only single-column identity keys as clearly demonstrated here.

"Identifying" When Using Only Identities Will Not Work

The Companies table originally had just one child: Projects.  But once we determined that Status codes are  also a child entity of Companies, we now had multiple children for the Companies table: Projects and Statuses.

Now, that in of itself is fine; at this point, there is no reason to change anything.  We can have those entities both references the Companies table via CompanyID, and both of those tables can have single column identity primary keys.

However, also in our data model, we see that at some point a child entity of Projects (Tasks) requires that we relate Projects to Statuses.  Now, suddenly the fact that Parents and Statuses have a common parent -- CompanyID -- is very important.   This is because we cannot allow a Project from one company to be assigned to a Status from another.  Once we identify this situation, we realize that the way to handle this is that the primary keys of both the Projects table and Status table must also include the CompanyID column.

With the composite primary keys to those children with the common parent in place, we can now relate them to each other as needed and we will have full data intregrity between those two entities and any of their children.  You can see this in our new data model, as it will not allow you to assign a Status to a Task unless it has been defined for the Task's company.  We would not be able to enforce this without ensuring that the Task table had a full normalized, non-redundant CompanyID column, and the way to do that was to ensure that the primary key of Projects was CompanyID/ProjectID and that the primary key of Status was CompanyID/Status.   The rest follows naturally when assigning the foreign key constraints.

Another Example

Suppose we have Stores, and each Store has Departments, and each Department has Employees. That is a straight-forward, simple data model where no two entities share the same parent, and we can just use single-column identities all the way throughout and be fine.

But what if each Store has multiple Locations?  And each Employee works at a Location.  We cannot juse use LocationID as a primary key of Locations, otherwise we can assign any random Employee to any random Location, regardless of the Store they belong to.

So, our Location table needs to have a primary key of StoreID/LocationID.  Now we also need the StoreID column in the Employee table to relate Employees to Locations.  To get this, we need to also include StoreID in the primary key of the Department table. With that in place, we can now properly relate Employees to Locations and we are guaranteed they will be for the same Store.

Once again, once we saw that two entities had the same parent (Locations, Departments), and that somewhere "down the line" those two entities would be related (via Employees), it becomes clear that we need to incorporate the common parent's primary key (StoreID) into the primary key of those two child entities (Locations and Departments) to enable that relation (in the Employees table) to function properly.

Summary

There are 3 main points I am trying to make here today:

1. The "all-identities-all-the-time" approach can work well in some cases, but not in all.  If you insist on only using all identities for all of your primary keys, you're not always going to have strictly accurate data integrity.
   
2. Don't forget that Identity columns can be part of a composite primary key.
   
3. When a table has multiple child entities in the database, and there are any relations between any of those child entities, then all of those child entities should have a composite primary key that includes the parent table's primary key columns to facilitate those relations.
   

This is not to say that the "all-identities-all-the-time" approach is always wrong, just that it is not always right.  And it certainly is not always "easiest", unless you find continually scrubbing invalid data to be an easy task.

posted @ Wednesday, August 13, 2008 11:06 AM | Feedback (41)

Foreign Key Constraints: SET NULL and SET DEFAULT

Most people know about cascading updates and deletes, but did you know there are two other foreign key constraint options you can use to maintain referential integrity?

Read all about them in my latest article over at SQLTeam.com.

These features, introduced with SQL Server 2005, haven't got a lot of publicity, but they can be very useful.  I just used the SET NULL option recently for the first time (inspiring me to put together an article on it) and it works great.

posted @ Wednesday, August 13, 2008 9:56 AM | Feedback (0)

Database Column Names != Report Headings

Always remember that the column names returned in a result set do not have to be the same as what you eventually output at your presentation layer.  

For example, suppose you have stored procedure that accepts a @CurrentYear parameter and returns a sales variance between the current year and the previous year for each customer.  I often see programmers struggling with writing dynamic SQL to produce output like this:


That is, the names of the columns vary based on the data; that is not a good way to return data from your database!  A much better result set to return is simply this:


Notice that with that set of columns, no dynamic SQL is needed, and the column names returned are always constant regardless of the value of the @CurrYear parameter.  

As mentioned, the fact that your data set has columns labelled "CurrentYear" and "PrevYear" does not mean that you cannot re-label them any way that you like on your report or web page. 

If your client code called the stored procedure and provided a @CurrentYear parameter, then it knows exactly what "CurrentYear" and "PrevYear" represent, and you can easily label the columns in the final result exactly as needed with simple formulas or a few lines of code. 

Remember that in the world of relational database programming, table names and column names should be constant -- only the data itself should change.   Focus on returning consistently structured data from your database, and let your client applications handle the labeling of columns to make them look nice. 

posted @ Wednesday, August 06, 2008 11:43 AM | Feedback (2)

How To Calculate the Number of Week Days Between two Dates

If the start date and end date are both week days, then the total number of week days in between is simply:


or

... since the DateDiff() function with weeks returns the number of week "boundaries" that are crossed; i.e., the number of weekends.

If you have a table of holidays, then you can simply subtract them out as well:


Now, what if the start day or the end day is on a weekend?  In that case, you need to define what to do in those situations in your requirements.

For example, if the start date is Sunday, Nov 20th, and the end day is Monday, Nov 21st -- how many week days are between those dates? There's no universal correct answer; it could be 0, or 1, or perhaps even "undefined" (null) depending on your needs.

posted @ Thursday, July 31, 2008 12:39 PM | Feedback (2)

Convert input explicitly at your client; don't rely on the database to "figure it out"

A common mistake beginners make when working with SQL is trying to format things at the database layer. I've covered that quite a bit in various blog posts, but I've only touched upon another similar issue which I feel is equally as important and also very often mishandled. read more...

posted @ Thursday, July 24, 2008 9:12 AM | Feedback (10)

The MailBag --- Super-Sized Edition! String Parsing, Crosstabs, SQL Injection, and more.

OK, boys and girls, it's time for the mailbag!  There's lots of stuff to cover, so let's get to it!

---

Greg E writes:


Greg -- A simple UDF should do the trick for you.  For example, something like this:


With that, you can write something like this:


Over at SQLTeam's script library forum, there is a thread with a bunch of parsing functions that you may find useful if your needs are more complex.

And, in case you missed it, be sure to read this post.

---

In response to my blog post on passing arrays to stored procedures, Juan writes:


Great point, something I missed in my article entirely.  Sometimes, it may make sense to declare @Val1, @Val2, ... @ValN parameters if there aren't too many and there's a clearly defined limit.  Thanks for bringing that up, Juan.  The simplest solution is usually the best, and in some cases that's probably all you need.  You still have clearly defined parameters with strong typing and no parsing, and those are the main issues with CSV parameters that I wanted to avoid.

---

Marc writes:


Marc -- I think what you are looking for is described here.  The key is to OUTER JOIN to all of your tables, and then use a CASE expression to determine which of those joined tables has the data you need.

--

Mary writes:


Thanks, Mary!  I'm glad I would be of assistance.  The "moral" that you wrote says it all. 90% of programming isn't writing code at all, it is simply defining what your code will do -- and that's always the hardest part! 

---

In response to my post on grouping by month, Mark writes:



Hi Mark -- First off, never be afraid to use a calendar table!  There is nothing hacky or unusual or tricky about them, they can make your life much easier, your code much shorter, and everything much more efficient.  If grouping by month or some other time period is important to your reporting,  then defining those months in a permanent, nicely indexed table makes perfect sense.

In this case, though, since you are outputting one column per month for a single year, I recommend to simply use CASE expressions to "cross tab" your data.  You can alias your columns as M1,M2,M3...M12 and Q1-Q4 so that no matter what year you are running the report for, your columns will be consistently named, and you can let your presentation layer handle outputting nice column headers with the current year/month for each one.

So, all you really need is something like this:


Of course, you'd define @StartDate and @endDate as '01-01-2008' and '01-01-2009', respectively.

---

In this comment, Stewy writes:


Stewy -- Relational databases have no obligation to store data in any specific order, or to keep track of the order that things were entered, or to return things "as they are in the database."  There is no such thing as getting data out "the way it is stored" because a relational database may move or re-order data temporarily to efficiently execute a query depending on indexes available.   You must always explicitly specify how you want your results using an ORDER BY clause.  If you want to keep track of the order that you added data to a table, you should have a "timestamp" column that records the exact moment each row was added via a DEFAULT value or a trigger.  Or, at the very least, you can use an IDENTITY.  Then, you can simply order by that column.  This is a very important concept to understand when working with relational databases.  Things are returned based on the data itself, not based on physical storage characteristics.  I hope this helps.

---

In response to Always Use Parameters, Karuna writes:

Hi Karuna -- you are absolutely 100% safe from SQL Injection by doing this.  Remember, SQL Injection is not about genereal SQL concatenation or about building a SQL statement dynamically.  It only can happen when you concatenate user input into a SQL string and execute it.  If you put together a big SQL statement via concatenation but you only incorporate user input via parameters, there's no need for scrubbing data or worrying in any way about SQL Injection -- it will never happen, under any circumstance. 

Avoiding SQL Injection is the easiest thing in the world -- simply do things the easy and correct way and you'll never need to worry about it.   It's like if there was a big controversy in the news about thousands of people crashing their cars because they are driving them with decorative tin foil covering their windshields, and asking the experts "how can we solve this crisis?"   Should we cut holes in the tin foil, or add mirrors, or incorporate a camera and a tv monitor?  Uh .. no.  You should just take the tin foil off of your windshield and do things the easy, simple and correct way and don't make things over complicated.   That's basically what this whole SQL Injection thing is about -- bad programmers doing stupid things when all they need to do is write decent code the easy way -- simply by using parameters.

---

Gocs writes:


Gocs -- I think you really need to read this very carefully.  I'll be waiting!

posted @ Wednesday, July 16, 2008 4:32 PM | Feedback (0)

The Golden Rule of Data Manipulation

Introduction

There is a very simple rule when it comes to storing (and returning) data, which I see violated all the time, making life so much more complicated for everyone involved.  In case you haven't noticed, that's a common theme I discuss here on this blog -- different ways programmers make life more difficult for themselves, instead of simply following good practices and doing things the easy way.  This is yet another example of that situation.

The "Golden Rule of Data Manipulation" is a simple, but important rule that you should always follow when designing a database,  writing database code, or really writing any application code at all for that matter:


In other words: Concatenation is easy. Parsing is hard.  Often, very hard -- or even impossible depending on the data.

Problems with Parsing

It is amazing how often I see people struggling with "difficult SQL problems" such as:

• Working with CSV lists of values in a single column, such as "1,3,56,2"
• Breaking out a FirstName/MiddleName/LastName/Suffix from a single "Name" column
• Parsing address strings into City/State/ZIP, or Number/Street/Unit
• Parsing Phone Numbers to get just an area code, or to take different phone formats and present them all uniformly
• Figuring out how to calculate the Day,Month, and/or Year from different string values such as "23-Jan-08", "2008-02", "20070303", "03032007"

And on and on it goes....

Now, sometimes you inherit or import data that needs to be parsed -- that's a fact of life.  You've got to figure out how to do it, and the key in those cases is to accept that because the data itself is essentially random, nothing you can write will perfectly work 100% of the time on all of it.  Often, the best you can do is handle most of the data, and then do some manual clean up. 

Parsing strings can be a very difficult task for any programmer, and the challenge isn't writing the code, it's coming up with the algorithm (another common theme on this blog).  Consider my new favorite example of why parsing a single Name column into a First/Middle/Last is not as easy as it seems:

    Oscar De La Hoya

How would your algorithm parse that one?  Never mind prefixes such as "Dr." and suffixes such as "Jr."! 

Please don't interpret what I am saying as a programming challenge -- I understand that it is possible to write long code with a list of exceptions or rules and have that algorithm work pretty well in most cases.  The point is that writing that algorithm is a lot of work, running it will be inefficient, and it will never be exact because the data itself that is being processed is essentially random.  It's just like the old saying: "garbage in, garbage out".  Still one of my favorites, after all these years, and it still applies!

A Data Model that requires Parsing = A Poor Data Model

So, we need to accept that sometimes you've got to parse data like this.  And that's OK; it happens, it can be done, even if some manual work is often involved.

However, there's no excuse when you design your database, your SQL code, or applications so that free-form data must be parsed, when you can simply design it correctly in the first place and store your data already broken out into the smallest possible units with the correct data types.

If breaking out a contact's name into First, Last, Middle, etc is important to your application, then you should force the point of data entry to accept input broken out into those columns.  The same goes for phone numbers, addresses, and so on.  Any time you have the option of accepting  input as clean, short, raw, segments of data you should always do it.   Once you have data at that smaller resolution, it is trivial to combine it any way that you want for presentation, formatting, filtering, and so on.

It may seem like overkill to break out a phone number into 4 columns:

   
And, in fact, it might be more complicated than that if you need to deal with international phone numbers.  You may look at your tables, and your code, and even the UI that accepts these fields and think "that is way too precise and unnecessary, breaking out phone numbers like this sure makes things complicated!"

But by doing this, and only accepting user input that follows precise rules of what is allowed in these fields, and storing each of them in their own column, you can now easily and efficiently:

1. Sort these numbers any way you want, without worrying about extra characters like parenthesis or dashes, or leading 1s, messing things up
2. Filter quickly on an area code without the need to use LIKE, and again worrying about extra characters getting in the way
3. Present the phone number quickly and easily any way you want without any parsing, be it as 123.123.1345 x123 or "(123) 123-1345 extension 123", or anything you want.
4. Validate your phone numbers, ensuring you have all the necessary parts and they are the proper length, without worrying about parsing strings

Considering doing any of those things if your data is stored in random strings like:

   
and so on ...  Not so easy in that case, just as parsing simple "Name" columns into First/Last, or addresses into  Number/Street/Unit is not so easy as well. 

Again, this is not a programming challenge -- I am sure it can done. (In fact, phone numbers are generally the easiest because you can usually just ignore anything other than digits.)  Most of us have done it before.  But designing something in such a way that parsing is required to do simple filtering, sorting, or formatting, is a bad design. 

It's Not About the UI

As I wrote here, you should never think "I want to display phone numbers like 123.123.1234, so I should store them and return them that way."  You should always think "How can I break this down into small, concrete parts that are easily validated and easy to combine any way I want at any time?"

So, what if you need fine detail when storing addresses, but you don't want your UI to present Street Number, Street Name, Unit Type, Unit Number as different data entry fields for usability or aesthetic reasons?  That's fine, but that doesn't mean you should not set up your database properly.  Your UI can certainly still present that one single "Address" text box for the user to fill out, parse that text at data entry, show the user the parsed result in multiple fields, and ask "Please verify for your address" or something along those lines.   Then, if not, the user can tweak the results and save it.  If you do things along those lines, and focus on getting the data parsed and stored correctly at the earliest point possible, every other part of your code will be that much more efficient.

All of this applies not only to data storage, but to how data is returned and passed between tiers as well.  Again, if you just return separate columns to your client application, instead of focusing on making them "look nice" in your database code by returning nothing but long, "pre-formatted" strings, your client can simply concatenate and format those columns any way it needs.  And, different clients can format that same database output in different ways -- all without ever altering any database code! 

Conclusion

In short, remember that writing concatenation is easy, efficient, and exact.  Writing a parsing routine, on the other hand, is often none of those things.   You may not always be able to control the design of the data you are working with, but be sure that when you can, you do it right.  If you find yourself using lots of LIKE expressions, or string parsing for simple data retrieval operations, something is wrong.   Time to fix up your database and your code, store the parsed and validated data permanently, and make things easier and cleaner for everyone.

Whether you are designing a schema, writing a SELECT, or writing code in any other programming language, remember that the Golden Rule of Data Manipulation always applies.  Accept this rule, learn from it, and practice it, and you might be surprised to find that programming isn't quite as hard as you thought it was.

posted @ Wednesday, June 11, 2008 11:48 AM | Feedback (7)

The Joy of Blog Feedback

Introduction

I have been writing my little blog here for some time now, and my favorite part of doing this is of course the feedback.  It's always great to hear from the readers, to have mistakes corrected, to debate various topics and techniques, and to learn a lot about SQL and the various topics I discuss here. 

At this point, I have received over 1,700 comments over the years, and while all of them are truly appreciated, I have noticed that unfortunately many of the, uh, less helpful comments do seem to consistently fall neatly into various categories. 

Let's take a look at an example of a simple, typical blog post and some of the responses that often come back.  If you write a blog of your own, or often read the feedback from other blogs, many of these may seem familiar to you. 

A Typical Blog Post


Some Typical Responses

The subtle blog spammer (that you initially mistake for a nice complement):


The person who doesn't seem to get it:


The person that really doesn't get it:

The person that somehow takes away the exact opposite of what you wrote:


The script kiddie (who just wants to cut and paste your code, not read or learn anything):


The very clichéd, mindless "anti-Microsoft ranter":


The "skimmer" (who just skims the post missing most of it):


The "repeater" (who just repeats what you've already written):


The "know-it-all complainer":


The random, unrelated question asker:


The "misunderstander":


The very rare polite and helpful typo alerter:


The much more common typo alerter:

Summary

Please, don't misunderstand, I mean this all in good fun.  I love feedback, and please, keep it coming.  It's what makes this and every other blog a fun place to visit.

In fact, I realize that I left out the most annoying feedback of all!  That's right, the Thin-Skinned, Overly-Defensive Blog Author Who Feels the Need to Respond to Everything:

Yeah, comments like those are definitely the worst of all!  Thank you for putting up with my feedback, now that I think of it!

posted @ Friday, June 06, 2008 3:43 PM | Feedback (11)

The Truth about "Cursor Busting" in SQL

Let's say you are called in to troubleshoot a stored procedure that is performing poorly.

You dive in to investigate and this is what you find:


Ah ha! A cursor!  It seems we have identified the bottleneck: Clearly, the performance problems are because the code is not doing things in a set-based manner, but rather by processing rows one at a time using a dreaded cursor.  This cursor is opening up the Products table, looping through the rows one at a time, and calling the "DoSomething" stored procedure for each ProductID.  As we all know, cursors are not the way to go when writing SQL code; this cursor should eliminated and replaced with a cleaner, more efficient (and more socially acceptable!) solution.

So, how we do optimize this?  Well, a commonly suggested approach is to eliminate the CURSOR by replacing it with a WHILE loop:

    declare @ProductID int
    set @ProductID = -99999

    while (@ProductID is not null)
        begin
        set @ProductID = (select top 1 ProductID
                          from Products
                          where ProductID > @ProductID
                          order by ProductID asc)

        exec DoSomething @ProductID

        end

Instead of declaring a CURSOR to loop through the table, we now are using "set-based" code and our problems seem to be solved.  The cursor is gone, our code looks much cleaner, we've tested it and it works properly, so off to production it goes.  Another cursor has been busted!

Right?

Actually ... no.

You see, eliminating cursors is not about syntax.  It is not about searching for the word "cursor" in your code and just replacing it with a WHILE loop that does the same thing.  Optimizing and replacing cursors involves much more.  We can never optimize any cursor code until we look deeper into what exactly is happening when we "process" each of those rows.  In this case, we need to find out what that "DoSomething" procedure is actually doing. 

Suppose the DoSomething procedure is generating a report and sending an email to the "Product Manager" for each product that contains status information, and then logging this email message into a table somewhere.

If that is the case, what have we just gained by replacing our CURSOR?  

Honestly -- not much,  if anything at all.  Because of the task at hand, we may very well need to process rows in the Product table one-by-one to send our emails and generate the report, and the bottleneck here is not the cursor code at all, but rather the report generation and maybe sending the email.   Eliminating the cursor code probably gains us nothing here.  If you need to process rows one at a time, go ahead and use a cursor -- that's what they are there for!   Replacing a perfectly fine, simple cursor with a WHILE loop might even make your code longer, or more confusing, or even less efficient depending on circumstances. 

For example, what if we need to process the Products ordered by Region, then Product Name, for whatever reason.  Our cursor code is simple:


All that we needed  to change was our ORDER BY clause.  Now, how would we write this as a WHILE loop?  Is it possible?  Sure.  Will it be as simple and clean as using a cursor?  No, it won't. (Though ROW_COUNT() makes this much easier than it used to be)

Now, I am not here to say that cursors are "good", but if you really need to process rows one by one, go ahead and proudly use a cursor.   Replacing cursors isn't about processing rows one-by-one in a different way (i.e., using a WHILE loop instead), it is about not processing rows one-by-one at all!   

Let's consider another scenario: What if the DoSomething stored procedure is checking to see if the Product's ExpireDate is greater than today's date, and if so, it is updates the Status column for that Product to 'X'.

In that situation, what have we gained by rewriting ProcessProducts without a cursor, and using a WHILE loop instead?   The answer is, once again: nothing!  In fact, we potentially have once again made our code more confusing or even less efficient than a cursor might be!  Remember, the bottleneck isn't the cursor syntax -- it is the fact that we are processing rows one at a time.  Replacing the cursor with the WHILE loop didn't solve this problem, did it?  

So, looking now at both of the scenarios I presented for the DoSomething stored procedure, it should be clear that we did not fix anything by replacing the cursor in either case simply by writing a WHILE loop.  If that's all you are doing, don't bother replacing the cursor at all.  You haven't optimized anything.

As I said before, the art of replacing a cursor is not a find-and-replace syntax change operation -- it is a fundamental change in how you process your data.  As in the Product report generation and email example, it may be that we simply need to process rows one by one, and thus no further optimization is possible from a SQL point of view.  In situations like updating the Product table, however, we do not need to process the rows individually -- we can do everything in one single UPDATE statement.  Thus, in order to determine how to optimize the ProcessProducts stored procedure, we needed to dig deeper into entire process as a whole, which included examining the DoSomething stored procedure and determining the full scope of exactly what this "ProcessProducts" stored procedure is doing. 

So, if "DoSomething" is updating the Products table as specified, we now know that a good replacement for our cursor code doesn't result in a WHILE loop and calling a separate stored procedure over and over at all -- it results a true, set-based solution:

And THAT is how you optimize a cursor! No loops, no calling of another stored procedure for each row in a table, no "find-and-replace" cursor code removal.  We examined the entire process, and rewrote the entire process, to get it done quicker and shorter and faster without cursors or loops. 

Always remember: Replacing a cursor isn't about rewriting your syntax, it is about redesigning your algorithm.

posted @ Thursday, June 05, 2008 10:56 AM | Feedback (10)

Log Buffer #98

Hello and welcome to the 98th edition of Log Buffer. My name is Jeff Smith and I will hosting this week's exciting episode. If, for some reason, you are not completely satisfied with this edition, simply write in and complain to Dave over at The Pythian Group and you will receive Log Buffer #99 absolutely free! Now that is a guarantee you can feel good about. OK, let's get to work.

I have only limited exposure to both PostgreSQL and MySQL, but I have often wondered why MySQL is so popular while it seems that PostgreSQL has the superior features.  Over at Xaprb, they attempt to answer that very question.  Be sure to read the comments from that post, and check out the big discussion from that article over at reddit as well.  The theory I like the best?  MySQL is easier to pronounce!  (How do you pronounce "PostgreSQL" anyway?)

Speaking of MySQL, Sheeri Cabral points out that MySQL's website certainly doesn't do the product any favors, and there's also a good discussion at Xaprb on why MySQL is Free Software but not Open Source.  If you ever wanted to add a new Unicode collation to MySQL, Alexander Barkov and Peter Gulutzan provide all the information you'll need.  Peter at the MySql Performance Blog tells us that MySQL lacks a good memory profiling tool, and based on his feedback, others seem to agree.  (No, not those Others!)  Speaking of MySQL feature requests, Justin Swanhart asks "Why does INFORMATION_SCHEMA fail to show information about TEMPORARY tables?"  and also lets us know that his materialized view stored procedures for MySQL have been OKed for release. Sunny Walia (what a great name -- is it possible to not be a fun person with a name like that?) tells us how to install innotop to monitor innodb information in real-time and wonders "Oh dear MySQL slave, where did you put those rows?"  Going back to the MySQL Performance Blog, Vadim warns us of a dangerous MySQL command; be sure to keep that one locked safely away from the kids.

Regarding a product I actually know a little about, Kalen Delany has a nice list of Free SQL Server Troubleshooting Tools to check out.  If you haven't seen it yet, my co-blogger here at SQLTeam Mladen has an amazingly popular list of Free SQL Server Tools that might make your life a little easier that was published a while back but it is always worth mentioning.  While you are visiting Mladen's blog, don't miss his latest post on getting immediate deadlock notifications for SQL Server 2005.  Also, be sure to leave him lots of comments telling him that his blog is great but that he is your second favorite SQL Server blogger -- after me, of course!

Still on the topic of SQL Server, Jamie Thomson provides us with a tip for ensuring that your root folder is valid when using SSIS.  Denis Gobo asks: What did you do to master SQL?   (Interestingly enough, for me it was by learning MS Access first!)  Tony Rogerson warns us of the performance implications of using Row_Number() in non-recursive CTE's.  And Paul S. Randal describes a CHECKDB bug that people are hitting; thankfully, he says that "you can only hit this bug if you ALREADY have corruption, that it's quite rare, and that there is a workaround."

Everyone enjoys a good analogy, right?  After all, a good analogy is like an ice cream cone: they both are ... hmmm ... OK, well, that's not a good analogy at all.  Never mind. Speaking of bad analogies, I bet that unlike Peter Gulutzan you never really thought about the expression "half baked" before and how it relates to MySQL features.  Well, now there's your chance!  (Of course, a pessimist would prefer "half un-baked", but that's a discussion for another time.)

A big topic lately has been SQL Injection attacks.  I always find this funny because this is the easiest problem to avoid in the history of programming; as CodeAssembly tells us, "Never concatenate user input to your queries, without exceptions."  That's really all there is to it -- do that, and you are good to go.  As I've written before, using parameters is not only safer, but your code is much shorter and simpler than if you concatenate strings all day long. 

Federico Cargnelutti gives us an introduction on managing and applying database changes with LiquiBase, an "open source, DBMS-independent library for tracking, managing and applying database changes."  I have never used LiquiBase, but sounds like something worth looking into.

While reading Magnus Hagander's PostgreSQL Blog, I found out that Yahoo claims it has the largest SQL database in a production environment -- and they use PostgreSQL.  Impressive!  Peter Eisentraut checks in from PGCon Day One, which included a presentation of his on porting Oracle Applications to PostgreSQL.   For those of you out there using Max OS X, Perldiver has summarized instructions on building PostgreSQL on Mac OS X.  Going off on a tangent, they just opened a new Apple store here in Boston on Boylston street.  I visited it this weekend after getting my usual bad haircut next door.  My verdict on the store?  It sure looks nice, but I had no luck finding a new 5 1/4" floppy drive for my Apple II.  Try to do better next time, Apple!

James McGovern offers some praise for Mark Wilcox of Oracle.  Why? Because Mark has been doing some must-read blogging over at the Oracle.com blogs.  Getting back to my favorite topic, which is coding SQL, Michael Armstrong-Smith instructs the Oracle crowd on using CASE to solver Outer Join issues. Shay Shmeltzer provides some tips on creating a master with two details on the same page when using ADF. 

Sticking with Oracle links, Pete Finnigan ponders read only tables or read only users, and notes that in Oracle a read-only user "has approximately 27,000 other privileges because of grants to PUBLIC. This is the killer issue as because of this it is in fact not possible to create a read-only user." Hmm ... only 27,000?  Come on, that doesn't seem that bad to me!  Eddie Awad tells us about the Lazy Developer's way to populate a Surrogate Key and over at the Oracle Scratchpad, Jonathan Lewis provides some helpful links on Index Efficiency.  Finally, if you are looking to install Oracle Database 11g Release 1 on Fedora 9 (and who isn't?), everything you need to know is covered over at Oracle-Base. 

Now, if you're like me, you hate DBAs.  Ah, just kidding, of course we all love our Database Administration Overlords (and I'm not just saying that because most of the people reading this probably are DBAs.)  However, even the best DBAs out there occasionally make mistakes.  If you have some horror stories of your own to share, or if you simply want to take pleasure in the misfortune of others, be sure to check out Kalen Delany's call for DBA Blunders.  (Of course, to be fair, even us developers occasionally make mistakes.) To help avoid future blunders, consider this advice: Do not use Windows System Restore as a backout plan for SQL Server Service Packs, Cumulative Updates, or HotFixes.  Also, Tara here at SQLTeam reminds us to optimize your tempdb and even provides a helpful script.  I'd like to add my own helpful tip for DBAs:  Schedule regular database backups!   Remember, you read it here first.

Previewing the upcoming SQL Server 2008, SQLTeam's Derek discusses the Data Profiling Utility with SQL Server 2008.  It sure seems like a nice tool, but that still may not make people any less nervous about SQL 2008.  (Heck, my team is still nervous about SQL Server 2005!)   Aaron Betrand urges people to vote if you want IntelliSense in SSMS 2008 to also support SQL Server 2005, which seems like a great idea to me, and Linchi Shea has a quick analysis of SQL Server 2008 Page Compression and its performance impact on table scans.  Finally, Jamie Thomson dissects the fuzzyness of SQL Server 2008.   To me, "fuzziness" is what happens to my vision after drinking too many mojitos, but Jamie is discussing a new feature in SSIS 2008 so give it a read.

I've always believed that you don't truly know all there is to know about databases until you understand the raw data structures of tables and indexes and so on.  Over at MSDN Channel 9, there's a new series of videos on Data Structures and Algorithms, so be sure to watch if you want to know how database engines really work "under the hood".  My enjoyment of the video was unfortunately interrupted by horrible flashbacks from my CS310 days.

Moving away from relational databases, Jim Wilson helps us to understand HBase and BigTable.  Apparently, HBase is the open source implementation of Google's BigTable database, which is described as a "sparse, distributed, persistent multidimensional sorted map."  In layman's terms, that means "a database with wicked huge tables."

For those looking for a laugh, be sure to read Andrew Calvett's MS SQL Server Book of Wisdom.  It reminds me quite a bit of my infamous and widely misinterpreted Top 10 Things I Hate About SQL Server post from way back in the olden days.  Be careful, Andrew: sometimes folks don't get it if your jokes are too subtle!  (Of course, in my case, it could be that my jokes just weren't that funny.)

Well, that's all for this week.  Thanks, Dave, for giving me an opportunity to write this week's Log Buffer.  It was lot of fun and a welcome opportunity for me to spend more time than I usually do reading lots of great blog posts from around the internet.  Have a great weekend everyone!

posted @ Friday, May 23, 2008 9:09 AM | Feedback (10)

Implementing "Interfaces" in SQL

My latest article has just been published over at SQLTeam:

    Implementing Table Interfaces

When I wrote a Table Inheritance article a few months back, the technique shown was pretty standard and straight-forward.  As I was writing it, I thought it would be an interesting challenge to figure out a way to implement table interfaces as well, where different tables don't inherit from the same base class, but they still "implement" the same relations.  That definitely was not as easy, and the end result isn't as clean and direct, but I hope this at least provides some ideas and at the very least it should provoke interesting comments and alternative approaches.

posted @ Monday, May 19, 2008 9:06 AM | Feedback (0)

Need an Answer? Actually, No ... You Need a Question

Welcome!

The reason you were directed here is because you need assistance, and I am here to help.  I am not, however, here to provide you with any answers!  You see, it looks like the assistance you need is not finding an answer; it is rather that you need assistance finding a question.

As you know, there are all kinds of questions.  Questions that test memory recall.  Questions that test logic skills.  Brain-teasers and mathematical questions and so on.  But there is one requirement that all good questions must have in common before they can be answered:


In other words, if you omit important information from a question, it doesn't matter how simple or easy that question is:  It suddenly becomes very difficult, or even impossible, to answer. 

For example, consider the following question:


Seems pretty easy, right? No logic, no memorization, no trivia, no knowledge of any specific topic is required. 

So ... what's the answer?  Take a few minutes, think about it, write it down on a piece of scrap paper.  I can wait, take your time ...

What's that, you say? You can't answer that simple question!?  Why not?  I stated it very clearly, it requires a simple YES or NO response, there's nothing tricky there.  So, why would anyone have any trouble giving an answer to something so basic and simple?

The reason, of course, is because you can't see me.  You have no way of knowing what I am wearing because I did not provide you enough information!  As simple as it is, it cannot be answered; therefore, it is not a proper question!

Suppose, instead, I provided a picture of myself and asked "In this picture, am I wearing a hat?"  And, in the picture, my head is clearly visible and the fact that I am wearing a Boston Red Sox cap is very clear.  Would you be able to answer the question in that scenario?  Of course!  Suddenly, what was an impossible question to answer became very simple! 

How did that happen?

It happened, of course, because I provided you enough information to answer the question!  And that is the often problem with many of the questions we see day to day in forums asking for help.  You cannot expect an answer unless you provide a proper question with all the necessary information.  The majority of the effort by those helping others in these forums is not spent answering questions, it is spent trying to figure out what the heck the question actually is!  And that is the problem; people don't seem to realize that they can't just randomly cut and paste code or ask vague questions without any context and expect to receive help! 

Yet, requesting more information and details doesn't always go over so well ... Those looking for help seem to often have trouble understanding why the helpers need more info, why are they are so "anal" and "demanding" about minor things like database schemas, or sample data, or code samples.  "How is that important?" they wonder. "Just answer the question and provide me with some help, please!  An expert would know the answer!"

My goal today is to hopefully help you to understand why information and context is so important for even simple, basic, questions, and how providing that information suddenly transforms a poor question into a very good one that can be quickly and accurately answered. 

Let's try another example:


So, what do you think?  Is that a good question that provides all of the information?  It seems simple, right? It is just asking for basic directions. Yet, where is "work"?  Is it asking for driving directions, or walking directions, or maybe which subway lines to take?  Who knows!!?

As stated, this question simply cannot be answered!   Yet, if I just  thought about it a little and made sure to provide all of the necessary information, this "impossible" question with no answer suddenly becomes a very simple one:


See the difference? Instead of just assuming that everyone knows where I work and what mode of transportation I am looking for, if I make sure to simply tell them, there is no uncertainty, no confusion, no guesswork, and the question can be answered.  This isn't rocket science, right?  Yet, these common-sense basics seem to elude many, many, people!

Would you drop your car off at a mechanic with a note on it that says:


I sure hope not.  You'd explain what's wrong, right?  It has trouble starting, it has a flat tire, there's smoke coming from beneath the hood, the steering wheel fell off, and so on. It's basic common sense that you would do everything you can to be sure that the mechanic has the information he needs to fix your car correctly and promptl