If we’re going to be great SQL Server developers, then we need to develop two qualities in ourselves, awesomeness and laziness. Being awesome means that we write code that executes quickly and efficiently. Being lazy means that we write as little code as possible to get the job done, and we write that, hopefully, only once. The alternative is writing the same code over and over again, hoping that you got it the same way every time, only to have an angry DBA track you down because you got one of the … ah! Jeez, nightmare fuel … only to have them track you down because you got one wrong. Rather than go through this misery, let’s try to be awesome and lazy and write that code once and only once. The perfect place to start in developing these qualities is with number and date tables.
A number table is simply a list of numbers. It’s a table with one column, and in that column you have numbers beginning with one and, in ascending order, ending with 1,000 or 10,000. A number table can help you accomplish several tasks in a set base manner as opposed to using a while loop or row-by-row operations. For example, you can generate test data using a number table. That’s how the test data was generated for the player roles table in our Crossbows and Cursors [00:01:33] database. You can also do while loops without actually have to code the while loop part. You can just join to the number table. You can also parse through string fields to look for certain characters using a number table. It’s more efficient than having to dig through each character one at a time. One more use: You can use a number table to look for gaps in numerical data.
Now there are plenty of other [00:02:00] uses for number tables, but of all the things we’ve just covered, I want to focus on one in particular, and that is looking for gaps in numerical data. Say we want to report on all that monsters in our Crossbows and Cursors game grouped by how many hit points they have. If we wanted to do that query, we could do it this way. We could SELECT the COUNT of MonsterID and the MonsterHP from monster, GROUP BY, ORDER BY MonsterHP. Pretty simple query, it should give us what we want, but there’s one little problem. What if the person that’s going to read the results isn’t us? What if that person is going to look at that data and say there aren’t any monsters here with 7 or 9 hit points. I’m wondering, is your query accurate? Is there incomplete data? Is your code wrong? The trouble is, when we write queries this way, we return just the information that really exists, and sometimes people aren’t entirely convinced by the. What we need to do is return all the possible values and just show them, very plainly, that there is no matching data. The way that we can do that is to rewrite our query using the number table as our source and join to the monster table.
Let’s take a look. Instead of doing it as a select from the monster table, we’ll this time select from the number table, and then we’ll join back to the monster table, joining the MonsterHP to the number field in the number table. In doing this, we will get all the rows back that match from the number table, and then if there’s a count of monsters to be attached to that MonsterHP value, we’ll see it. If not, there won’t be anything there. I’ve used this technique more times than I can remember. It’s so simple and so effective. Keep it in mind any time you are faced with coding for a skeptical audience [00:04:00].
A data table is a must-have for any application that returns results on or does lookups on date names or date ranges, which, if you think about it, is a pretty broad definition, one that encompasses just about every application you can think of. Chances are, you could really use a date table. There are a number of compelling reasons why you might want a date table, but I’ll give you four in particular. Number one, it establishes a central source for all of the data that you have about dates for your organization. It means that you and your fellow SQL server developers will all be playing with the same deck of cards when it comes to describing dates. Number two is that it means you don’t have to use DATENAME anymore in where clause. If you want to look up what happened in July, then you say where month name equals July. You don’t have to say where date name and then the month and then whatever source date it is equals July. It ends up being much faster both in writing the code and in executing.
A third reason is that you don’t have to cast and concatenate your way to putting together a full date anymore. That full date is simply stored in the date table. To get January 7th, 2016, you don’t have to mishmash that together anymore; it’s already there for you, preformatted in the date table. Finally, the fourth reason is you can establish alternate periods for your organization. That means that dates are normally described by calendar year and other common aspects but also things that might be particular to your situation. Let’s talk a little bit more about that last point, establishing alternate period names for dates. Let’s say we have ages in [00:06:00] our Crossbows and Cursors database and we want to say that anything that happened in January through March is part of the Age of Goats and anything that happened in April is part of the Age of Copper. There’s no Age of Aquarius because I hate that song. We would query the player roles table and then include a column that told us what age this role took place in.
Now we have a few options as to how we would pull this off using code. One option would be to do UNION ALL. What we would do then is select all the roles from January through March and say those will be Age of Goats as age. Do a UNION ALL, and then select all the roles from April and say Age of Copper for the age. That would work. The only problem is this would require two round trips to the table. That’s not quite as awesome as we want to be here. We can probably pull this off with just one trip to the player roles table, so let’s think of something else.
We could use a CASE expression. That’s something we’ll get into more in a following chapter, but the CASE expression will allows us to say, if a value is something, then return something else. If we say if the date is between January 1st and March 31st, then we’ll call it Age of Goats. If it’s between April 1st and April 30th, then we’ll call it Age of Copper, and we can do that in a set-based manner. There’s a drawback with this approach though. If we wanted to add additional date fields, other ways of describing the data from a date perspective, we would have to write additional case expressions. We couldn’t just add a column; we’d have to build it ourselves. Not only that, but if we had, say, 20 or 30 ages to pick from, that’s a whole lot of writing [00:08:00] to make sure that we just get that one field back correctly. We don’t want to do that either; that sounds horrible, so we’re better off going with a date table.
The Crossbows and Cursors database comes with a miniaturized version of a date table that you can just play with to get familiar with the concept. It’s called DimDate. Now the dim is borrowed from data warehousing concepts, and it’s just there so that we don’t have to call our date table date because that’s a reserved SQL server keyword, and we don’t want to mess with that. We SELECT from the player roles table and then JOIN to DimDate on the ShortDate on the DimDate and the RollDate on the player roles side. Then we are able to pull the age, the date age, along with the RollDate, PlayerID, DieSides, DieRoll all in one query. This is super simple to write and read, as you can see. It doesn’t involve any CASE expressions. It doesn’t involve UNION ALLs across multiple queries. You’re just good to go with one single statement. The wider your date table is, the more date descriptors you have in it, the easier it is to pull additional date fields in the same query. It’s code that’s easy to write, code that’s easy to expand and code that will scale well relative to the other methods we describe.
We’ve looked at some ways to get ahead using number and date tables, but there may be occasions where joining to one of those tables might be overkill, where maybe we don’t need to return all those results. Maybe we just have a few different values that we could possibly be looking up. When that happens, it’s usually a good time to turn to CASE expressions, and that’s what we’ll check out next.
- 01. Prologue and Script Files
- 02. Thinking in Sets (5m)
- 04. Case Expressions (17m)
- 05. Computed Columns (10m)
- 06. Windowing Functions – Design (21m)
- 07. Windowing Functions – Performance (8m)
- 08. Replacing Cursors, Part 1 (6m)
- 09. Replacing Cursors, Part 2 (5m)
- 10. Replacing Cursors, Part 3 (7m)
- 11. Let the Adventure Begin! (9m)
- 12. About Doug (2m)