Free SQL Server 2008 Virtual Event

Solid Q along with our friends and sponsors at SQL Server Magazine are running a free virtual SQL Server 2008 event. The aim is to show you ways to make better use of your existing investment in SQL Server 2000 or 2005 while finding out how SQL Server 2008 will affect you.

You can find more info and register by following this link:

Itzik Ben-Gan, Erik Veerman, Ken Spencer and Andrew Potter from Solid Q are presenting along with Michael Otey from SQL Server Magazine. Itzik is covering performance tuning, Erik is covering data cleansing in SSIS, Ken is covering building data-centric applications with SQL Server 2008, Andrew is covering SQL Server 2008 reporting services and Michael is covering virtualizing SQL Server.

A New Site For SQL Down Under

I hope all readers are having a great start to the new year. We’ve been revamping a number of our web sites over the last few weeks. First off the block is the new site for SQL Down Under:

Hopefully it’s easy to navigate and I didn’t miss anything while migrating to the new server. If you have any feedback, please just let me know.

It comprises the site for the podcast, links for the discussion/support list and the site for the SQL Down Under Code Camp, which will be running again in October this year.

Book: The Exceptional Presenter – Timothy Koegel

When I am travelling, I try to make sure I have enough books with me to read. I estimate how much time I’ll have for reading and look at the books in my “to be read” pile. It’s always a tradeoff between having enough books and the weight of the books in my luggage.

Timothy Koegel’s book “The Exceptional Presenter” wasn’t in that pile. It was one of the books I pick up in an airport when I’ve accidentally been caught without enough. Even though it wasn’t on my planned or recommended reading list, I enjoyed it. The quote from Tom Peters at the front of the book summed it up pretty well. He also had picked it up while waiting at an airport and said he figured that “the odds are high that I’ll find at least one, small, operational piece of advice”. He went on to say that he did find a couple of new ideas and lots of useful reminders.

What I find most reassuring about that is that even someone with the presentation experience of Tom Peters ( still spends his time working out how to improve his craft. Whether or not you agree with what he’s got to say, you could never say that Tom isn’t an experienced presenter. He said he thought the best advice in the book was that “Those who practice improve. Those who don’t, don’t”.

I’m endlessly amazed by the number of people who are offered presentation skills training and don’t take it or by those that think they’ve done enough now to stop learning how to do a good job of it.

Timothy Koegel’s book is full of lots of very solid advice: nothing earth-shattering but well worth reading.


Developer Productivity – MIA?

Darren Neimke has a thoughtful post about a Dot Net Rocks show he recently listened to. He said he gets really annoyed when people start knocking HTML and Javascript. Darren mentions sites like Amazon, Flickr, YouTube and Wikipedia as examples of what these technologies have brought us.

For me, the real story is developer productivity, which I now feel is missing in action.

My take on it is that sites like these have been built in spite of the tools being used. I wonder how much richer these sort of sites would have been, had we not been spending our time dealing with the technologies. The main reason they are successful is the ubiquitous delivery platform, not the quality of the development experience.

Darren said he’d rather maintain 10,000 lines of Javascript than 10,000 lines of VB.NET. I sure wouldn’t. Assuming the code was written by people with the same skill levels (and that’s usually where the argument starts), the tooling supplied for Javascript work still isn’t at the same level as the tooling for VB.NET (or C# for that matter). Tooling quality directly affects developer productivity.

Browser inconsistencies is my biggest pain point. I see great web-based projects wasting huge amounts of developer time trying to make the end-user experience even similar across different browsers. This is something our industry should be ashamed of. We are wasting great amounts of clients’ money on this nonsense.

Earlier this year I was involved in a public web-based project for a client. We (at the time this was Readify) had a team of four or five good developers working on the project for around seven or eight months. There was no lack of developer talent involved. What struck me when I saw the final result though is that (years before) if I had needed to quote the development of that same project as a winform app, I would have quoted several weeks in total, not several months and just for me, not for four or five people. What on earth have we done to developer productivity in recent years?

Now, you could contend that it’s not a fair comparison, given the winform app couldn’t have been deployed in such a public and cross-platform way but again that is a delivery platform issue, not a developer productivity issue. It’s a problem that could have been solved rather than many of the other problems that the industry has tackled instead.


Much Ado About Dates

I just did a short article for the PASS newsletter on SQL Server 2008 and date/time types. I thought I’d copy it here too: 

SQL Server 2008 introduces a wave of changes in the way we use and process dates and times in SQL Server. Reportedly, having separate date and time data types was one of the highest-rated customer requests from previous versions. The early betas of SQL Server 2005 included a separate date data type but this was withdrawn from the release when a number of problems with the way it was implemented became apparent. Thankfully, this has returned in SQL Server 2008 along with a host of other date/time improvements.

Higher Precision

At the time it was introduced, the precision of the datetime data type no doubt seemed appropriate. It offered a range of January 1st 1753 through December 31st, 9999 with an accuracy of 3.33 milliseconds. The reason for the odd start date was the alignment of the Julian and Gregorian calendars just prior to that time. So while writing a date involving a year like 1152 might seem simple, the date arithmetic required could become quite complicated and involve knowledge of which country was being discussed as changes occurred in different countries at different times.

It could also be argued that dates well into the future might offer some potential for change as well. I gather that scientists are still discussing whether or not the years 4000 and 8000 should or shouldn’t be leap years.I’m sure it’s important that they discuss that but I would have to say that I’m not too concerned about the outcome, either way J

While it might seem fairly pointless to deal with dates earlier than 1753, it can create a source of errors when migrating data between systems. As an example, I was recently porting some data from an Oracle database. An error occurred because the Oracle system included a date with the year 200. Even though this was an error (it was meant to be 2000), it was enough to cause a problem as SQL Server 2005 had a narrower range of dates than the source system.

The new datetime2 data type now comes to the rescue. It has a range of 1st January 0001 through 31st December 9999 with a precision of 7 decimal places ie: 100 nanoseconds. The name of the data type has been the source of a lot of discussion. Many of us would have preferred perhaps bigdatetime, in keeping with other existing SQL Server data types like bigint but the name chosen does align well with common practice in the .NET development arena.

Several literal string formats are supported for the datepart. Numeric options are mm/dd/yyyy, dd-m-yyyy and mm.dd.yyyy. An alphabetical option of mon dd, yyyy is supported along with ISO 8601 formats of yyyy-mm-dd and yyyymmdd, an ODBC format of {d’yyyy-m-dd’} and finally a W3XML format of yyyy-mm-ddZ. It is important to note that it is unaffected by dateformat set options, unlike the existing datetime data type.

Datetime2 is also a system CLR data type. In SQL Server 2008, many of the new data types are implemented as system CLR types. Regardless of whether or not CLR integration is enabled at the system level, these types are available.

Date and Time Data Types

The next new data type is the date data type. This is identical to the date portion of the new datetime2 data type and uses the same literal string formats. Developers have called for this type for a long time as systems often store datetime values but only want to search on a date. For example, on a table with a TransactionDateTime column (holding both date and time), we might often want to locate all the rows on a particular date. In SQL Server 2005, the approach was to use a clause like:

WHERE TransactionDateTime >= @RequiredDate AND TransactionDateTime < @TheDayAfter

A common mistake was to try to wrap the column in a function such as:

WHERE CONVERT(date,TransactionDateTime) = @RequiredDate

This, however, caused performance problems as an index on the TransactionDateTime column could well be considered not useful by the query optimizer. SQL Server 2008 comes to the rescue on this in two ways. First, we can now have a column that just holds a date. Second, the pattern involving the CONVERT function is now specifically detected and index searchability on such existing datetime columns has been restored. The date data type is stored in a fixed three byte location.

The new time data type offers a variable level of precision. Declaring a variable using it should normally involve a decision on the degree of precision required. For example:

DECLARE @When Time(6)

This would declare a variable with up to six decimal places for the seconds part of the time component. The maximum precision value is 7, which indicates 100ns.

The time data type is stored in a location of between three and five bytes. It does not have a fixed size.

And Onto The World

Although this was all great work, the SQL Server 2008 team haven’t stopped there. Great support for timezones has been added to the product as well. The DateTimeOffset data type is similar to the new DateTime2 data type but also adds support for storing the timezone offset. This means we can now store values such as:

2007-08-13 10:30:39.432343 -07:00

The timezone offset is preserved and returned. All comparisons of the DateTimeOffset data type are performed as UTC (universal time aka GMT). This affects comparing values, sorting values and indexing them.

SQL Server 2008 also provides functions that allow processing timezone offsets. The SWITCHOFFSET function allows modifying the timezone offset of a DateTimeOffset value while preserving the actual time (ie: UTC-based time).


With support for higher precision and offsets comes the need to enhance the system functions for date and time. In particular, the GETDATE function is now deprecated and replaced by the higher precision SYSDATETIME function. SYSDATETIMEOFFSET provides a value with the timezone offset of the server and SYSUTCDATETIME provides the current UTC time.

In Summary

The changes and enhancements to date and time management in SQL Server 2008 are a very welcome addition to the product, should provide a great basis for the future and assist today in interoperability with other systems.


PDA Typing

I had a colleague today who said “sorry about the PDA typing” when sending an email. It made me reflect on the change that PDA devices have had on the email system. I used to think that SMS messages (ie: texts) that the teenagers send to each other were fairly cryptic but I’d have to say that even they pale into insignificance compared to some of the messages I receive now from PDA-based emailers. The briefest email I’ve ever received from a client said:


Maybe I was having a day where I was a bit thick but it took me a while to realise this meant:

Thank you, David


PASS board meeting

Over the last few weeks, I also attended the board meeting for PASS ( I was voted onto the board this year and at the board meeting, it was confirmed that I will now take on the role of Director for Chapters. I’m very excited about the possibilities that this brings and I look forward to trying to make a signficant contribution to the chapter organisations throughout the world. My first step is to spend time talking to each chapter leader, so if you head up a PASS chapter, expect a call 🙂