Louis Davidson : SQL Server, SQL Techniques

Quest DMV Breakfast Code – Disk IO

drsql — Wed, 04 Nov 2009 17:52:43 GMT

For any of you who attended the Quest breakfast at PASS (and anyone else really) I wanted to go ahead and post the code and give a little wrap up/explanation. (I will post the stuff on perf counters later. They are cool, but this is really what I wanted...(read more)

Commenting your code

drsql — Thu, 31 Jul 2008 02:19:43 GMT

As I am easing back into real life from writing the book, I am in search of easy targets for blogging. My boss mentioned this blog over on Jeff Atwood's Coding Horror Blog and it got me thinking about commenting. His advice is to only comment "why" the code works. I can't quite agree, because the code he claims to be acceptable is:

private double SquareRootApproximation(n) {
r = n / 2;
while ( abs( r - (n/r) ) > t ) {
r = 0.5 * ( r + (n/r) );
}
return r;
}
System.out.println( "r = " + SquareRootApproximation(r) );

I mean, it is better than some code I have seen, but still, I would like a bit more information about why this works. Maybe the name of the algorithm used, or at least what to do if this fails to provide the expected results. Admittedly this is probably something that could be easily found, but most algorithms are not. Comments in my mind should at least lead you to understand the mindset of the programmer. What would actually improve this code in my mind is to change the variables to full words (though in this case it might not make sense to do this.)

On an extremely different side of things is this article from "Edgewood Solutions Engineers" on mssqltips.com. Their answer is to explain what the code is doing in simple terms, making sure to comment almost everything. They have a very elaborate header devised, with dependencies, both users of the object and objects it used. Most of what is said seems a bit like overkill, but their point here "Comment all of the major code blocks of the code and the critical minor points that can be easily overlooked such as a obscure WHERE clause." is a good one. I generally pepper my code with comments where I think it will be hard to debug for myself later, with a consideration for others, particularly when those others will call me to explain the code.

Which brings me to my commenting philosophy. I personally think you have to comment to the expected lowest common denominator. Think of the dumbest person who could have the need to read your code who is also qualified to have their job (otherwise you would have to write instructions on every line of code). If the qualified person can figure out what you are doing just by your naming conventions and , then it doesn't need comments. But if that person would look at the code and reasonable figure it out, then there is no need to comment the code. What this requires is a few things:

Naming objects - if your procedures, tables, columns, functions all have meaningful names, you won't have to explain what they mean, saving time
Good design - if the relationship between objects and the cardinality of those relationships is clear, then you don't need to explain that what you are doing is hack due to poor thinking...
Naming variables - probably the most important thing to avoid the need for comments is naming stuff. Name variables with words, not single character values (except sometimes i, x, etc will suffice for obvious typical uses)
Reasonable code formatting - SQL has no real form, so you could write procedures on a single line. You could. You could smash your hand with a hammer too. Neither action would be very good. (Consider using Red-Gate's SQL Refactor tool if nothing else.)

However, the fact is, for SQL code, the real problem comes in when you start coming up with cool relational methods of solving problems that most moderately qualified people wouldn't get. For example the trick of using a sequence table to break apart a comma delimited list. Couple that with a join and you get some amazingly cool code, but how do you comment it?

For example, say an architect that shouldn't be an architect designs a table with a comma delimited list like this (didn't I mention good design earlier? I hate having to say this is a hack, but it is an elegant hack...)

--excerpted from Chapter 7 of Pro SQL Server 2008 Relational Database Design and Implementation
CREATE TABLE poorDesign
(
poorDesignId int,
badValue varchar(20)
)

INSERT INTO poorDesign --using 2008 syntax
VALUES (1,'1,3,56,7,3,6'),
(2,'22,3'),
(3,'1')

You can "normalize" this set using a table of numbers (in my examples named tools.sequence) and a really cool join:

SELECT    poorDesign.poorDesignId as betterDesignId,
          SUBSTRING(',' + poorDesign.badValue + ',',i + 1,
                CHARINDEX(',',',' + poorDesign.badValue + ',',i + 1) - i - 1)
                               as betterScalarValue
FROM     poorDesign
             JOIN tools.sequence
                    on i >= 1
                      AND i < LEN(',' + poorDesign.badValue + ',') - 1
                      AND SUBSTRING(',' + + poorDesign.badValue + ',', i, 1) = ','

But are there enough pixels available on the planet to make that more understandable to most SQL programmers? Even the reasonably qualified? I mean, I am still kind of amazed at the technique and the fact that it returns the following:

betterDesignId betterScalarValue
-------------- -----------------
             1                 1
             1                 3
             1                56
             1                 7
             1                 3
             1                 6
             2                22
             2                 3
             3                 1

still impresses me. Frankly I don't know how to comment that code to make it readable. In a real situation I would settle for a comment before the SELECT that stated:

--Uses a table of numbers to parse the comma delimited list into a SQL acceptable format.
--If you don't understand this code, read this article: http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum

Opinions? What do you use for a comments in your code? Do you have commenting policies?

The joys of a calendar table (ytd, month, and quarter summations)

drsql — Sun, 10 Dec 2006 01:13:00 GMT

There was a question on the forums the other day that asked how to get a total for the current month, the current quarter, and year to date. It is actually a pretty easy task when using a calendar table. I have one you can use here (I fixed an error in it that I just found too!): http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry

For an example, I will use the SalesOrderHeader and SalesOrderDetail tables from AdventureWorks, and use the LineTotal to do the summations. I will group on the AccountNumber. So the query is:

select SalesOrderHeader.AccountNumber,
        sum(case when today.year = calendar.year and today.month >= calendar.month
                              then LineTotal else 0 end ) as ytdTotal,
        sum(case when today.year = calendar.year and today.quarter = calendar.quarter
                              then LineTotal else 0 end ) as quarterTotal,
        sum(case when today.year = calendar.year and today.month = calendar.month
                              then LineTotal else 0 end ) as monthTotal
from   sales.SalesOrderHeader
         join sales.SalesOrderDetail
            on SalesOrderHeader.SalesOrderId = SalesOrderDetail.SalesOrderId
        join dbo.calendar
            on calendar.dateValue = SalesOrderHeader.OrderDate
        join dbo.calendar as today
            on today.dateValue = '2001-12-31' --vary this value to vary the date of "today"
group by SalesOrderHeader.AccountNumber

So let's briefly build this query from the ground up. First, the base data from the sales schema in adventureworks.

from   sales.SalesOrderHeader
         join sales.SalesOrderDetail
            on SalesOrderHeader.SalesOrderId = SalesOrderDetail.SalesOrderId

Next, join in the calendar table two times:

from   sales.SalesOrderHeader
         join sales.SalesOrderDetail
            on SalesOrderHeader.SalesOrderId = SalesOrderDetail.SalesOrderId
        join dbo.calendar
            on calendar.dateValue = SalesOrderHeader.OrderDate
        join dbo.calendar as today
            on today.dateValue = '2001-12-31'

The first calendar will associate one row in the calendar table with each row being output. The cardinality of the set will not be changed because the calendar has one row per day. The second copy of the calendar table I aliased today because it will usually represent the current day (it could be anything). What this does is add another copy of the calendar table, giving you all of the columns of that date to do math with (if you don't see that, it will be more clear in a moment (I hope :)

Next comes the column that will be grouped by. In this case I am using the account number.

select SalesOrderHeader.AccountNumber
from   sales.SalesOrderHeader
         join sales.SalesOrderDetail
            on SalesOrderHeader.SalesOrderId = SalesOrderDetail.SalesOrderId
        join dbo.calendar
            on calendar.dateValue = SalesOrderHeader.OrderDate
        join dbo.calendar as today
            on today.dateValue = '2001-12-31'
group by SalesOrderHeader.AccountNumber

This makes the set now one row per account number, with a "table" of values associated to each account number that we can use aggregates on. This is where the interesting stuff begins (you were hoping, weren't you!):

Because each one of these tables for aggregates has calendar table rows associated with the date or order, and the "current" date, you can use this fact to pick out values you are interested in for a given sum. take the ytdTotal one:

sum(case when today.year = calendar.year and today.month >= calendar.month
then LineTotal else 0 end ) as ytdTotal,

For each row I check the year of the order, and see if it matches the year we are looking for. Then check to see if the order month is less than or equal to the current month. If it is, put out the LineTotal, else just use 0. You can build very powerful expressions that are extremely fast by varying the way you compare the current day to the date of the order. In an earlier article, I also showed how you could use relative positions in the calendar table to get the past 6 months, past 13 months, etc, without any kind of ugly math. For this example, if you wanted to get the past 6 months, you could simply add:

sum(case when today.relativeMonthCount -5 <= calendar.relativeMonthCount
and today.relativeMonthCount >= calendar.relativeMonthCount
then LineTotal else 0 end ) as past6MonthsTotal

Getting the equation just right can be a bit tricky (at least for my brain :), so I generally test my date ranges using a query like this:

select distinct calendar.year, calendar.month
from dbo.calendar
          join dbo.calendar as today
               on today.dateValue = '2001-12-31'
where today.relativeMonthCount -5 <= calendar.relativeMonthCount
    and today.relativeMonthCount >= calendar.relativeMonthCount

Which returns:

year     month
-------- --------
2001    7
2001    8
2001    9
2001    10
2001    11
2001    12

Crossposted to http://drsql.spaces.live.com