SQL Server MVP Deep Dives

I’m not a SQL Server Most Valuable Professional anymore[1], but I was when this amazing project got underway, and I am proud to contribute as an author on SQL Server Integration Services. SQL Server MVP Deep Dives is a unique book. It has:

• 53 authors
• 59 chapters
• Coverage of database design, development, administration and performance tuning
• Coverage of the SQL Server BI suite: SSIS, SSAS and SSRS

But that’s not the coolest thing about this book. The coolest thing is where the money goes. All author royalties (that’s right – 100%) go to War Child International, a network of independent organizations, working across the world to help children affected by war.[2] So not only will this book help you become a better SQL Server practitioner, it will also help children around the world.

You can pre-order the book online today at the SQL Server MVP Deep Dives web site, and get immediate access to a pre-release PDF copy of the book. (Note: if you order before the end of September and use the code pop0928 at the checkout you will also save 50%!) Also, if you’re attending the PASS Summit in Seattle in early November, you can buy a copy at the conference bookstore and it signed by many of the authors. I’ll be there, and based on the email I’ve seen we’ll see a majority of the authors and editors should be there as well.

If you only buy one SQL Server book this year, why not make it this book? And since all proceeds go to benefit a great cause, why not buy two?

I’ll see you at PASS!

[1] You can’t be an MVP and a Microsoft employee at the same time, so when I joined Microsoft they took away my MVP status. Which was OK.

[2] Not, as some may think, a non-profit organization dedicated to helping aging Jethro Tull fans.

T-SQL Fundamentals is (Almost) Here!

Not too long ago I posted about Itzik Ben-Gan's excellent book Inside Microsoft SQL Server 2005: T-SQL Querying and how valuable I've found it. Well, Itzik has just completed work on his next book: Microsoft SQL Server 2008 T-SQL Fundamentals.

This is a book for people who are new to SQL programming, which is an audience that Itzik has not tackled before. Now you might think that for someone who knows more about T-SQL that anyone else in the world, writing a beginners' book should be about as difficult as sleepwalking, but this is not the case. In a recent post to the private SQL Server newsgroup for MCTs[1] Itzik had this to share:

“I always wanted to write a book about T-SQL Fundamentals, but kept postponing it until I felt I acquired enough knowledge and teaching experience to write it. Well, you never feel you have enough knowledge especially with a language and a model that are so deep, but at least enough to make a decent effort. Some may think that writing a Fundamentals book is easier than writing an advanced one, but I think it's actually the other way around, especially with SQL. Target audience for advanced books is less prone to be misled and mainly need their gaps to be filled. It's a big responsibility to teach people fundamentals, and that's one of the reasons I waited so long.”

How cool is that? Even though I don't think I'm the target audience for this book, I am definitely going to get a copy. As a trainer I'm always looking for better ways to explain core concepts, and T-SQL has enough difficult concepts that I am sure to pick up a pointer or two (or two hundred) from this book.

The book is scheduled to be released on October 22 and you can pre-order it today. So what are you waiting for?

[1] Yes, I asked his permission before posting this quote here. ;-)

Buy This Book, or Your Database Gets It!

At last night's VDUNY meeting I talked about a whole bunch of new features in SQL Server 2008 for database developers. And because this is a favorite topic of mine[1] a lot of the discussion revolved around Transact-SQL.

And you can't really talk about Transact-SQL without the name Itzik Ben-Gan coming up at least once or twice. Itzik is arguably the person who knows more about using T-SQL than anyone else on the planet, including the people who develop the language at Microsoft.[2] And of course, you can't mention Itzik at a user group without recommending this book:

Inside Microsoft SQL Server 2005: T-SQL Querying

If you develop applications using SQL Server - any version, not just SQL Server 2005 - then you absolutely must have this book in your library. It doesn't matter if you are brand new to SQL Server or (like me) you have been a SQL Server consultant and trainer for over ten years - you will find information in this book that you never knew you didn't know, and you'll find techniques that will make you a better SQL developer every day.

Yeah, it's that good.

Just to make sure no one thinks that Itzik is here behind me with a gun, this is a purely unsolicited recommendation. I'd just gotten a follow-up email from an attendee, which made me remember the presentation, which made me remember the discussion, which made me remember just how darned good this book is. Buy it and see.

[1] And because they were foolish enough to say "talk about whatever you'd like." ;-)

[2] Yes, this is a powerful statement, but I feel comfortable making it because A) I did qualify it with the word "arguably" and B) I've heard enough people on the SQL Server team say the same thing without using the word "arguably."

One More Reason to Attend

I've posted a few times already[1] about the SSWUG Business Intelligence vConference that I have been helping to organize. Well, the conference is now less than a month away, and there is more news to share:

We're giving away a copy of Microsoft Visual Studio Team System 2008 Team Suite with MSDN Premium to one lucky attendee.

That's right - the big one. This is the ultimate version of Microsoft's MSDN subscription, with a suggested retail price of $10,939. If you're a software developer or BI professional, this package has everything that you need to develop for the Microsoft platform, and then some.

If you'd like a chance to win this MSDN subscription, just register for the SSWUG Business Intelligence vConference. For just $100 you get:

• 30 amazing technical sessions from 10 amazing speakers.
• The ability to watch the sessions either during their multiple scheduled timeslots, or on demand for up to 14 days after the conference is over.
• Online chat and Q&A with the speakers during the scheduled sessions.
• Downloadable slides, transcripts and demo files so you can continue the learning once the session is done.
• And here's the one that blows my mind - a $150 discount on the Fall SQL Connections conference in Las Vegas. So if you're planning to attend the SQL Connections conference, the SSWUG Business Intelligence vConference more than pays for itself.

So what are you waiting for? This vConference is going to be amazing, and we'd love to see you there!

[1] For example:

• BI for the Virtual Guy
• Check Out This Lineup!
• And also a mention in What Happens in BIDS, Stays in BIDS

Visual Developers of Upstate New York

I apologize for the late notice (I've know for weeks, but have kept forgetting to post) but I will be speaking next Wednesday, August 27th, at the VDUNY user group in Rochester, New York. This user group meets on the 4th Wednesday of each month in the Rochester Microsoft offices, and generally has a great turnout of talented software development processionals.

This month I'll be presenting on some of my favorite new developer-centric features in SQL Server 2008. I realize that this is awfully vague, but that is intentional. I'm planning on going in with a loose agenda and lots of demos, and seeing where the session goes.

And to sweeten the pot a little, I will also be giving away a complete set of nine DVDs with all of the content from the TechEd Developers and IT Professionals conferences in Orlando this June. If you're in the Rochester area, you should plan on attending - it will be a lot of fun.

Handling Delimited Fields

I'm working with a set of input data that has multiple logical values "encoded" within a single field for each record. The source system allows users to select multiple values from a list, and then crams all of these values in a single field in the underlying database table, with a multi-character delimiting string between them. This is (believe it or not) the first time I've had to deal with this in a production SSIS scenario, so I investigated several different approaches to splitting the values into multiple rows with one value per row.

The first approach involves loading the data from the source system into a table in the staging database without modifying the shape of the data, and then using a SQL Server table-valued function to split the delimited field during the process of loading the data from the staging database into the data warehouse. I did some searching for a decent "split" function online and found one here that did most of what I needed. The only significant thing that I added was support for a "key" field to be passed in and included in the return table, so that the records extracted from the delimited field could then be correlated easily with the data in the source record. Here's the function I ended up with:



IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KeySplit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[KeySplit]
GO

CREATE FUNCTION dbo.KeySplit
(
@Key NVARCHAR (4000)
,@String NVARCHAR (4000)
,@Delimiter NVARCHAR (10)
)
RETURNS @Results TABLE
(
KeyColumn NVARCHAR (4000)
,ItemColumn NVARCHAR (4000)
)
AS
BEGIN

DECLARE @CurrentItem VARCHAR(8000)

WHILE CHARINDEX (@Delimiter ,@String, 0) <> 0
BEGIN
SELECT
@CurrentItem = RTRIM (LTRIM (SUBSTRING (@String, 1, CHARINDEX (@Delimiter, @String, 0) -1))),
@String = RTRIM (LTRIM (SUBSTRING (@String, CHARINDEX (@Delimiter, @String, 0) + LEN (@Delimiter), LEN (@String))))



IF LEN(@CurrentItem) > 0
INSERT INTO @Results (KeyColumn, ItemColumn) SELECT @Key, @CurrentItem


END



IF LEN(@String) > 0 -- Record after final delimiter
INSERT INTO @Results (KeyColumn, ItemColumn) SELECT @Key, @String

RETURN

END
GO

I can then use it like this in the source query from to load the data warehouse:

SELECT UDF.[KeyColumn] AS [RecordKey]
,UDF.[ItemColumn] AS [ItemName]
,RFC.[DelimitedList] AS [RawItemList]
FROM dbo.RFC_DATA RFC
CROSS APPLY dbo.KeySplit (RFC.[ID], RFC.[DelimitedList], '!#!') UDF

This performs reasonably well (although as I mentioned above, I don't really have enough data to say if it performs well enough) and returns the data I need.

Next, I wanted to look at a way to perform the same "splitting by key" functionality in memory in the SSIS data flow. This was obviously a job for... the Script Component![1] I personally tend to shy away from using the Script Task and Script Component in my SSIS packages (largely because there is so rarely anything that I need done that cannot be done using the built-in tools) but this is a case that screams out for scripting. And SSIS makes this incredibly easy. Here's what I needed to do:

First, I updated the data flow I'd created to load the data into the staging database by adding a Multicast transformation, a Script Component transformation, a Row Count transformation and an OLE DB destination. The image below shows the finished data flow after everything has been configured and connected.




Next, I right-clicked on the Script Component and set up the inputs and outputs to include the columns I needed.




Next, I selected my output and set its SynchronousInputID property to None to mark this as an asynchronous transformation[2]. It's important to do this before going into Visual Studio for Applications (VSA) to write any code, because Visual Studio will put in place the method stub code appropriate for the component when you first launch VSA, and it's mildly annoying to have to change it later on.




Next, I added the Delimiter package variable (which I added earlier to my package with the data type string and the value of the delimiter in the data I needed to split) to the ReadOnlyVariables list for the Script Component.




I then clicked on the Design Script button to launch VSA, and updated the stub code with the splitting logic I needed.




As you can see, this is much cleaner in VB.NET than it is in T-SQL, since the requisite functionality is already included in the .NET System.String class, so we don't need to re-invent this particular wheel.


Finally, I executed the package and made sure everything ran as desired.





I also did a little testing in the database to verify that the two techniques produced identical results, which they did. As you can see from the final image above, there just isn't enough data at this point to reach any meaningful performance conclusions, but I'm now armed and ready with two tested techniques for once the production data is available.



[1] If you didn't read that to yourself in a superhero voice, please go back and re-read this sentence until you get the tone right.


[2] If you're not sure about the differences between synchronous and asynchronous components in SSIS data flow, check out this TechNet article, or better yet, this excellent book by former SSIS Group Program Manager Donald Farmer.

More Dating Advice

There must be some sort of synchronicity at work - just this morning I was blogging on how to convert different string and integer date representations into "real" datetime values, and then this afternoon I found myself needing to include a "seconds formatted as time" column in a view I was building on top of an audit dimension table. This is something I've done many times before, so I figured I could just Google it and get some sample code from any one of a million places online.

But this was not the case. I couldn't find any samples at all. So I dug through my old project code (and dug, and dug, and dug) until I found what I was looking for. And then I had to update it to include millisecond support, because the old code I found didn't go to this grain. Anyway, here's the deal:

1) Start with a table that has a start time and an end time column, like so:

CREATE TABLE DateFormatTemp
(
[ExecStartDT] DATETIME NOT NULL,
[ExecStopDT] DATETIME NOT NULL
)

This looks pretty familiar, right? And it's very simple to DATEDIFF these two fields to find out how many seconds (or whatever) there were between the two dates. But your users (and that probably includes you, too) don't want to have to translate a second count into hours, minutes and seconds. I know I don't, so...

2) Use the T-SQL DATEDIFF, CASE and string concatenation functions to format the results into something more useful [1]:

  SELECT [ExecStartDT]
        ,[ExecStopDT]
        ,DATEDIFF (ss, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE()))    AS ExecutionTimeInSeconds
        ,CASE -- Hours
            WHEN (DATEDIFF  (hh, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE()))) >= 10 THEN
               CAST (DATEDIFF  (hh, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) AS CHAR (2))
            ELSE
               CAST ('0' + CAST (DATEDIFF  (hh, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) AS CHAR (1)) AS CHAR(2))
         END + ':' +
         CASE -- Minutes
            WHEN (DATEDIFF  (mi, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE()))) % 60 >= 10 THEN
               CAST (DATEDIFF  (mi, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 60 AS CHAR (2))
            ELSE
               CAST ('0' + CAST (DATEDIFF  (mi, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 60 AS CHAR (1)) AS CHAR(2))
         END + ':' +
         CASE  -- Seconds
            WHEN (DATEDIFF  (ss, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE()))) % 60 >= 10 THEN
               CAST (DATEDIFF  (ss, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 60 AS CHAR (2))
            ELSE
               CAST ('0' + CAST (DATEDIFF  (ss, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 60 AS CHAR (1)) AS CHAR(2))
         END +
         CASE -- Milliseconds
            WHEN DATEDIFF (ms, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 1000 < 10 THEN
               '.00' + CAST (DATEDIFF (ms, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 1000 AS VARCHAR)
            WHEN DATEDIFF (ms, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 1000 < 100 THEN
               '.0' + CAST (DATEDIFF (ms, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 1000 AS VARCHAR)
            ELSE
               '.' + CAST (DATEDIFF (ms, [ExecStartDT], ISNULL ([ExecStopDT], GETDATE())) % 1000 AS VARCHAR)
         END                                                               AS ExecutionTimeFormatted
    FROM [DateFormatTemp]
ORDER BY ExecutionTimeInSeconds ASC;

When you execute this you get back something that looks vaguely[2] like this:

ExecStartDT      ExecStopDT              Exec... ...Formatted

---------------- ----------------------- ------- ------------

2007-05-28 17:00 2007-05-28 17:03:13.817 193     00:03:13.816

2007-05-28 17:00 2007-05-28 17:03:31.323 211     00:03:31.323

2007-05-28 17:00 2007-05-28 17:05:24.547 324     00:05:24.546

2007-05-28 16:30 2007-05-28 17:04:10.920 2050    01:34:10.920

2007-05-28 16:30 2007-05-28 17:05:19.193 2119    01:35:19.193

2007-05-28 16:20 2007-05-28 17:04:05.973 2645    01:44:05.973

2007-05-28 16:10 2007-05-28 17:03:59.680 3239    01:53:59.680

2007-05-28 16:00 2007-05-28 17:03:42.323 3822    01:03:42.323

2007-05-28 15:00 2007-05-28 17:03:46.710 7426    02:03:46.710

(9 row(s) affected)

This is much more useful than just having the difference in seconds.

Of course, you can put this code in a T-SQL UDF, or have MUCH simpler code that does the same thing in a SQL CLR UDF, but in my case I only need this logic once in each database, and do not want the added complexity of managing SQL CLR anything, so this does exactly what I need.

[1] Useful like the formatting here is not. This code is just too wide to fit nicely here.

[2] I say "vaguely" because I've edited the column headings and start times to make the values fit better on the blog.