Jonathan Gennick

You've Got Framing!

2012-01-12T05:37:00.000-08:00

Article #5 in my series on window functions in SQL Server 2012 is about the framing clause:

You've Got Framing!

Use the framing clause to cumpute running sums, moving averages, and to otherwise answer questions by examining data as it moves through a frame of reference.

Pulling Rank for the Boss

2012-01-02T11:23:00.000-08:00

Here's an article showing how to attack business questions involving words or
phrases such as "topmost" or "bottommost", "top N" or "bottom N", or that are
otherwise answerable by ranking the rows in a result set according to some
criteria that you can apply to one or more columns of data.

Pulling Rank for the Boss

This article is fourth on my series covering the use of window functions in SQL Server 2012. This article goes over the use of ROW_NUMBER, RANK, and DENSE_RANK. They are similar functions providing slightly different ways to rank rows in a result set with respect to one another.

Enjoy!

Interlude: The Mechanics of Window Functions and How They Work

2011-12-07T04:51:00.001-08:00

I interrupt my business-case-driven series on window functions with a detailed look at how they are evaluated in the context of a SELECT statement. It's important to understand when these useful functions are executed, because that when has implications on the data available.

Link to the article:

Interlude: The Mechanics of Window Functions and How They Work

The idea hit me on a long car drive. I hope you enjoy the result.

H.G. Wells and SQL: Travelling in the Second Dimension

2011-11-21T05:36:00.001-08:00

I should have spent yesterday working on a troublesome project at the office, but I badly needed a break from working on weekends and felt on a roll with my series on window functions, so I took the day and had some "fun" with LAG and LEAD. Here is a link to my results:

H.G. Wells and SQL: Travelling in the Second Dimension

This is the second in my series of articles on window functions in SQL Server 2012.

Detail in the Aggregate

2011-11-19T18:26:00.001-08:00

Work has been crazy. It's taken me longer than expected. But finally, I've written and published the first of what I plan to be a series of articles on SQL window functions:

Detail in the Aggregate

This series is motivated by the increased support for window functions that we can look forward to in SQL Server 2012.

I versus We: Avoiding the Majestic Plural

2011-10-16T14:58:00.000-07:00

Felt like writing today, so I whipped up a little piece on the topic of "I" versus "we" in instructional writing:

http://gennick.com/we.html

SQL Rally 2011

2011-05-13T17:07:00.000-07:00

I'm sitting here in my hotel room resting after one of the best conferences I've attended in years -- SQL Rally 2011. Forget that I'm a computer-book editor supposedly on the prowl for new author talent. My trip to the Rally was a chance to hark back to my roots as developer and DBA. The sessions were uniformally good and held my attention. Here are some highlights from Thursday:

Tim Mitchell gave an excellent talk comparing ETL done with SSIS versus with T-SQL. Tim proposed various data load scenarios and compared the SSIS solutions with the T-SQL solutions. I liked what I saw of the SSIS GUI interface, and I wish something that elegant had been available way back in the day when I was doing ETL into Oracle. Tim's scenarios were realistic. I liked that he ran actual tests and showed the actual results.

Next up was lunch, and possibly my only bad decision at the Rally. I choose the chicken sandwich ahead of the beef sandwhich and wound up with a one-inch thick slab of chicken-breast between two slices of bread. Hint: my mouth isn't that big. LOL! Well, if a too-thick slab of chicken is all I can complain about, you know the event was good.

After lunch came Scott Shaw's presentation "The Enterprise SQL Server DBA". (Thank you Scott, for adding the word "Server" after "SQL" at the very last minute). Scott had some good wisdom to offer on forming DBA teams in the enterprise, on presenting a uniform team face to the rest of the company, on mentoring the less experienced DBAs, and much more. Scott also spoke about the challenges of managing a mixed environment with databases going back as far as SQL Server 2000. He even has one instance that runs in 6.5 compatibility mode. If I had to pick a favorite session from Thursday, I'd give the nod to Scott's.

My other Thursday session was "DBA Disaster Recovery Techniques to Keep Handy" by Edwin Sarmiento of The Pythian Group watching him run his demo, you know that he is comfortable with recovery and the pressures that go with it. Get his phone number and keep it handy against the day when you're up against the wall in a recovery nightmare. Terms to remember from his talk: "Recovery Point Objective" and "Recovery Time Objective".

Friday I took a cab in rather than walking the three miles from my hotel. I like to walk, but Florida is hot, hot, hot, and I didn't want to arrive a sweat-soaked mess. Besides, the breakfast was better at the conference hotel.

As luck would have it, Louis Davidson found me whilst I was lingering over my morning coffee at the hotel restaurant. That was a bit of good fortune. It's always nice to see a familiar face, and I'd been hoping to catch him to talk about his upcoming new edition of Pro SQL Server Database Design.

Session highlights from Friday:

Kendra Little, "Big and Tall: When to Partition". I had forgotten -- did I ever know? -- that SQL Server supports partitioning. One difference from Oracle Database is that in SQL Server every table is considered as being partitioned. It's just that many tables have only one partition. I like the simple elegance in that line of thinking. A question I have for Kendra is to ask why LEFT partitioning is the default when it seems like RIGHT partitioning makes more sense.

John Sterret's talk on Policy-Based Management. I tried, I really tried to attend this session. I went in. I sat down. John began. But I got a phone call, hit the "ignore" button, the caller tried again, I hit the "ignore" button again. When the third try came, I figured maybe I should take the call.

Thomas Grohser's talk "SQL Server Storage Engine under the hood" was my next session. (Oh, my lunch choice today was much better. Very good lunch). Thomas got into the nitty-gritty on how SQL Server does I/O. 8KB page sizes. 64KB allocation units. Checkpoints. Good stuff in this talk.

I enjoyed the Rally. There was a good vibe. The crowd was small enough that one could catch speakers in the hallway to ask follow-up questions or just to rub shoulders a bit. And not one session did I regret attending. SQL Rally is now on my short-list of "must attend" conferences for 2012.

Example Book Proposal

2010-11-25T19:37:00.000-08:00

Following is a link to an example book proposal that I've just finished crafting. It's the result of many an evening, and most of my Thanksgiving Day afternoon. I'm pretty happy at how it came out. If you have aspirations to write, I believe you'll find it helpful.

http://gennick.com/ExampleProposal.html

Returning the Days in a Month, Revisited!

2010-11-14T17:45:00.000-08:00

Thanks to Anton Zlygostev, I've just published a new version of my article on writing a query to return one row for each day in the current month:

http://gennick.com/days_in_month_query.html

I first wrote the article some ten years ago, in June 2000. I hadn't thought about it in years, until Anton wrote to point out a much more elegant solution than the two that I first proposed. He graciously allowed me to post his solution in a revised version of my article.