Top Ten Things I Learned at SQL Pass on 10/13/2011

The learning continues...

1. The "AlwaysOn" feature of SQL Server 2012 greatly simplifies high availability through redundant servers and simultaneously optimizes reporting by allowing you to use secondary servers for reporting and the primary server for transaction processing. However, it is currently unknown whether or not AlwaysOn will be in any edition other than Enterprise. It seems likely that it is an Enterprise-only feature.

2. The "ColumnStore Index" feature in SQL Server 2012 could radically speed up queries against tables in the Shelby v.5 database, especially attendance history. I can't wait to try it out. I may install Denali to try it out even before the official release of 2012.

3. There is a version of SQL Server Express coming out with 2012 that can easily be embedded into applications. This could potentially be a way to simplify the distribution of Shelby v.5 for those customers who do not own a license of SQL Server Standard. I will be looking into it more back home.

4. The "Semantic Model" features of SQL Server 2012, when coupled with the "FileTable" feature make for a powerful combination, capable of doing searches for document content based not just on matching of strings but actually pairing of concepts. I was blown away by the demo. Any librarian in the world would have been salivating over the processes and results I saw.

5. Two more times in one day I was shown the fallacy of putting dates inside of a function in the WHERE clause. I will never do it again, I promise! But I also learned that datetime and smalldatetime are being depricated in SS2012, meaning that we should probably start using the other, new data types for time. I will pass that info along to our database developers.

6. I learned that by simply changing the varchar data type to nvarchar, we could be capturing special characters for names, addresses, and more, which would make the v.5 database friendlier to international users. I'm going to do some testing on this when I get home too, to verify what is happening in our db right now when someone puts special characters into the varchar fields.

7.  I need to kick the habit of not specifying the length of varchar values and others when I use them as variables. I've been snakebit by that one before, and what I learned today just reinforces that I need to specify length 100% of the time.

8.  Some people, no matter how smart they are about what you want to discuss, can just be too annoying to talk to. It's just better to bide your time and find someone who may not be as knowledgeable but who is willing to listen and to converse at a pace that matches your own.

9.  The 2013 Summit will be in Charlotte, NC. That will be much more convenient for me to attend, and I am very much looking forward to not having to adjust to a new time zone in order to participate.

10.  Making free form reports with PowerPivot data in the background is not only possible, it allows for greater control over formatting and the embedding of elements such as sparklines into the data results. Free form reports can even use slicers to control the content. The only downside is the fixed nature of the rows and columns, which is why PivotTables are so handy in the first place.

Top Ten Things Learned at SQL PASS on 10/12/2011

Wednesday is when the conference starts in earnest, and so I definitely learned more than I can summarize here, but these ten things really stood out to me. As with my first list, this is roughly chronological over the course of the day.

1. There are over 4,000 registered attendees at this event. Add in the presenters and the vendors, and you can imagine how packed the rooms are. It is good to have such a thriving community; and yet, it does strain the capacity of the venue.
2. SQL Server Code Named "Denali" has officially been named "SQL Server 2012," and is expected to be released in the first half of next year. "Crescent" has officially been named "PowerView" and will be a part of the SS2012 release, but apparently it requires the Enterprise edition. This is unfortunate, because that will put it in reach of only a couple of our Shelby Systems, Inc. customers.
3. SQL Server 2012 will provide connectors and (eventually) drivers for something called "Apache Hadoop." This is something I will need to learn more about. Apparently Hadoop is a way to consume gigantic amounts of essentially unstructured data (on the order of pedabytes) and generate meaningful output. I don't know that I'll ever need that in my current role at Shelby Systems, but it is an interesting feature.
4. I learned several things during the demos in the keynote, including: "sentiment" is now a measureable fact that can be quantified and reported on through the Microsoft Data Marketplace and the upcoming "Data Explorer"; only John Wayne has made more movies than Samuel L. Jackson.
5. Stephen Few is a name I heard multiple times with regard to optimizing report style and layout. I believe I'm going to have to look into his web site at www.perceptualedge.com and check out his books.
6. If I can get ahold of some mapping files that include the ZIP code layer (the Census Bureau is said to have some in the public domain), I should be able to get the mapping feature in Reporting Services to map down to that level. That would be much better than the current limit of reporting at the state level.
7. Satori Software (a vendor here as well as a partner with Shelby Systems, Inc.) could actually return latitude and longitude information back to the NAAddresses table if we added that to our service agreement with them. The additional licensing cost makes it unlikely that this feature would be a part of all our customer's ShelbyMAILROOM package, but it is interesting to know.
8. The inactivated slicer problem I noticed in the Excel PivotTables that are created programmatically from ShelbyQUERY is probably triggered by some option in the automated set up of the table. A manually-create OLE DB connection can use slicers just fine. It appears to have something to do with an OLAP-related setting, but I'm going to have to follow up on this more with Microsoft. Helpfully, one of the PowerPivot techs gave me his contact info and invited me to e-mail him if I couldn't pinpoint it on my own. I have to give props to the Microsoft staff here at PASS, who are as helpful as they can be.
9. I have been doing date filtering in the WHERE clauses of queries in a way that will impede the efficiency of the query, possibly adding multiple seconds of time to getting the query results.  I have to learn ways to avoid putting table columns into functions, even the DATEDIFF() function.
10. The pie chart is not an effective way of communicating comparisons in reports. Bar charts are far an away the best chart for most comparisons. Gauges too are relatively ineffective communicators. But the bullet chart is exceedingly good at communicating "KPI" style data with more context. I will have to learn how to create them in Reporting Services.

Calculating ISO 8601 Dates in T-SQL

If you do not work with international dates much, you may never have heard of ISO 8601 and you may never need to know what I'm about to cover in this post. If so, consider yourself lucky. I am not so lucky. I have been called upon to design a few queries for international customers of Shelby Systems, and in a few cases the queries have required temporal (i.e. time-related) analysis based on the ISO 8601 standard. And although I was not lucky enough to avoid the task of working with ISO 8601 in SQL, I have learned quite a bit about temporal querying in the process, so what I have lacked in luck I have made up for in knowledge. I see that as a fair trade.

In brief, ISO 8601 is a set of rules standardizing the representation of dates and times to facilitate international commerce and communication. For an comprehensive discussion of the full ISO 8601 standard, read the article on Wikipedia. For the purposes of this post there are just three points you need to know about this standard:

• It always expresses dates and times from the largest term to the smallest, i.e. YYYY-MM-DD.
• It counts Monday as the first day of the week, not Sunday.
• It counts the first week of the year as the week containing the first Thursday of the year, not the week containing the first day of the year.

Each of these facets of the ISO 8601 standard are at odds with the default ways in which T-SQL handles dates.  There are workarounds to each one, though each workaround is more complex than the last. I'll start with the easiest first.

Express Dates and Times from the Largest Term to the Smallest

The default ways to get a datetime value and express it as a string in date notation are the following:

convert(varchar, getdate())
cast(getdate() as varchar)

Both of these will return the result in the same format. For July 7th, 2011 at 4:13 pm, it would look like this:

Jul 7 2011  4:13PM

Apart from the three-letter abbreviation for the month and lack of a comma, this is a fairly typical way of expressing the date and time in America. But it is not at all in keeping with the ISO standard. Fixing this is fairly easy, though, because the CONVERT() function has an optional format parameter, and there are values that will return true ISO-formatted date and time results. Here are the values and their outputs for the same day and time as above:

convert(varchar, getdate(), 112) = 20110707 (date only)
convert(varchar, getdate(), 108) = 16:13:24 (time only)
convert(varchar, getdate(), 126) = 2011-07-07T16:13:24.173 (complete date and time)

For the last variation, notice the T separator between date and time. Also notice the inclusion of milliseconds as a decimal fraction of a second. The last format also includes hyphens in the date where the date-only format did not. Both variations are acceptable in the ISO 8601 standard. The format without the hyphens is the "basic format," and the format with the hyphens is the "extended format."

Count Monday as the First Day of the Week

This is actually the key reason why churches may be interested in using an ISO week even if they live in the United States or other countries that do not use the ISO 8601 standard as a general rule. Most churches that have Friday or Saturday worship events want to count the following Sunday worship as part of the same weekend for statistical purposes. The ISO standard does that be lumping Sunday in with the prior six days.

However, this is uniquely tricky for the ShelbyQUERY environment because it does not allow the command SET DATEFIRST 1, which would automatically tell SQL Server to count Monday as the first day of the week. One simple workaround is to simply subtract one day from the date when doing comparisons based on which week the date belongs to. This will treat Sunday as if it were Saturday of the prior week, and it will treat Monday as if it were the first day of the week. Simply using getdate()-1 wherever you use getdate() will do that trick. Just remember not to subtract the day when you want to display the actual calendar date.

For statistical calculations based on a Monday through Sunday week, group the values by the ISO week and the ISO "week year" (i.e., the year in which the week falls -- which can be a different year than the calendar year because the first few days and last few days of the year can fall into the other year's week count). How to calculate the ISO week value is the next topic.

Count the first week of the year as the week containing the first Thursday of the year, not the week containing the first day of the year.

This is the most difficult part of ISO, and it comes down to calculating the week of the year. The DATEPART() function has a week parameter option that returns the week of the year; however, it does not follow ISO rules. It will count January 1st as being in the first week of the year no matter when it falls during the week. If you are using SQL Server 2008 or later you have it easy because Microsoft introduced a new DATEPART() parameter option in 2008 that does follow the ISO 8601 standard: iso_week. If you are using SQL Server 2005 or earlier, though, you are going to have to calculate it the hard way.

This is the algorithm I use  in the query below to calculate the ISO week of the year:

1. Let TargetDate equal the date for which we want to know the ISO week value.
2. Let CurrentThursday equal the date of the Thursday associated with the same week as the TargetDate (taking into account that Sunday belongs with the prior Thursday).
3. Let FirstThursday equal the date of the Thursday of the week containing January 4th (by definition January 4th is always in the first ISO week of the year), basing that on the year value of the CurrentThursday (not the TargetDate).
4. Let ISO_Week_Value equal the difference, in weeks, between the FirstThursday and the CurrentThursday, plus one.

Here is a query that uses variables to reflect each step of the algorithm and includes two AllInOne versions that rely on only the @TargetDate variable and no others. The FROM clause at the end is only necessary in ShelbyQUERY because of a requirement in that environment to always include a FROM clause.


declare @DateOffset as smallint, @ThursdayOffset as smallint, @TargetDate as datetime, @CurrentThursday as datetime, @FirstThursday as datetime, @ISO_WeekNumber as tinyint

/* The @DateOffset is used to make sure that Monday - Wednesday are always in the same week as the following Thursday and that Friday - Sunday are always in the same week as the prior Thursday no matter what the @@datefirst value is.
The @ThursdayOffset is used to find Thursday no matter what the @@datefirst value is. */ 

set @DateOffset = case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end
set @ThursdayOffset = case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end

set @TargetDate = getdate()


set @CurrentThursday = dateadd(day, @ThursdayOffset - datepart(dw, @TargetDate + @DateOffset), @TargetDate + @DateOffset)

set @FirstThursday = dateadd(day, @ThursdayOffset - datepart(dw, cast('1/4/' + datename(year, @CurrentThursday) as datetime) + @DateOffset), cast('1/4/' + datename(year, @CurrentThursday) as datetime) + @DateOffset)

set @ISO_WeekNumber = datediff(week, @FirstThursday, @CurrentThursday) + 1

select
 TestDate = @TargetDate,
 CurrentThursday = @CurrentThursday,
 FirstThursday = @FirstThursday,
 ISO_WeekNumber = @ISO_WeekNumber,
 ISO_WeekYear = year(@CurrentThursday),

 ISO_WeekNumber_AllInOne = datediff(week, dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, cast('1/4/' + datename(year, dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end)) as datetime) + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), cast('1/4/' + datename(year, dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end)) as datetime) + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end)) + 1,
  ISO_WeekYear_AllInOne = year(dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end))
 
from (select A = count(*) from Shelby.NANames) a

 

Comparing Dates in SQL

I found out the hard way (read: customer calling in to complain that the query I wrote him was omitting some events from the calendar query I wrote for him) to NEVER use basic math comparisons (=, <, >) for dates; at least, I have learned not to use them without DATEDIFF( ). It is more difficult sometimes, but it is going to render the correct result every time, with the other comparison operators will not.

The problem is that dates are not stored merely as dates but as datetime values. This means there is an hour, minute, second, and millisecond value associated with every date, even if that value is ignored by the user interface. In most cases this is not going to cause a mistake when comparing two different dates, but it does cause a problem when one is trying to find equivalence between two instances of the same date.

Here is the underlying "gotcha": When the time of day does not matter, most databases store dates as if they were at midnight of the day in question whereas the GETDATE( ) function returns the date and current time. Any calculations based on the GETDATE( ) value also have the current time. And because 1/1/2009 00:00:00 is technically less than 1/1/2009 15:39:15 (or any other time after midnight), those two values will not satisfy a comparison using "=" even though they signify the same calendar day.

Here is a simple CASE statement showing the use of DATEDIFF( ) in conjunction =, >, or <.

CASE
WHEN DATEDIFF(day, date1, date2) = 0 THEN "The dates are the same."
WHEN DATEDIFF(day, date1, date2) > 0 THEN "Date1 is earlier than Date2."
WHEN DATEDIFF(day, date1, date2) <>


Using an approach like this will always give you the results you want, without any problems from the time element of the datetime value.