I had a fantastic time at the SoCal Code Camp both as a presenter and attending some of the many great sessions. Many thanks to Woody Pewitt, Michele Leroux Bustamante, Brian Maso, & Daniel Egan for all their hard work and dedication to the event. Not only was there an outstanding variety of sessions but there was a great Geek Dinner on Saturday night with a live music and free food. Every attendee and presenter I spoke with remarked about how great the event was and they were greatly anticipating the next event. Michele also a good recap here.
Speaking of next events, it looks like the next Code Camp will be in San Diego most likely in June or July.
I would like to thank everyone who attended my sessions on T-SQL Enhancements and SQL CLR Enhancements in SQL Server 2005. Both sessions had a great crowd, some great questions, and also some really good discussion. I hope everyone took something away from the session.
You can get all the slides and demo code here: SoCalCodeCamp.zip
Now playing: Ray Charles - Here We Go Again (With Norah Jones)
In a previous post I talked about the changes to the TOP keyword in SQL 2005. Keeping along the same lines, this post will focus on one of the T-SQL enhancements that I am most excited about using, the ROW_NUMBER keyword.
All of us at some point in time worked on an application where we had to display a bunch of data in a paged format. There were a few options: bring back all the data and only display the records you want, build a dynamic query bring back only the rows you want, or use Temp tables in a Stored Procedure to bring back only the rows you want. Each option had its benefits and drawbacks, but none of them for particularly elegant or efficient. All that gets much easier with the addition of the ROW_NUMBER keyword.
The syntax of the ROW_NUMBER function is:
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
Let’s look at this with a few quick examples:
First a real basic example. In this example, we are only using the “ORDER BY” clause of the function. Remember, that this ORDER BY specified in the OVER clause applies only to the ROW_NUMBER function. You can specify a different ORDER BY for the final result if you wish.
--Basic
Select ROW_NUMBER () OVER(Order By OrderDate ASC, TotalDue DESC) as RowNum, TotalDue, *
From Sales.SalesOrderHeader
Where OrderDate BETWEEN '6/6/2004' AND '6/8/2004'
Order By RowNum
In the next example, we will utilize the PARTITION clause. This is not as useful when we talk about the paging application, but it could be very useful if you are looking at sets of data. The PARTITION acts as a grouping mechanism for the ROW_NUMBER function. When specified, the ROW_NUMBER clause will restarting numbering at the beginning of each group as specified by the PARTITION clause. So in this case it will assign Row Numbers based on the Total Due within each distinct Order Date, Territory ID combination.
-- Using the Partition we can have a Row Number within sections
Select ROW_NUMBER () OVER(PARTITION BY OrderDate, TerritoryID Order By TotalDue DESC)
as RowNum, OrderDate, TerritoryID, TotalDue, *
From Sales.SalesOrderHeader soh
Where OrderDate BETWEEN '6/6/2004' AND '6/8/2004'
Order By soh.OrderDate, soh.TerritoryID
On to our original question. How does this function help us our paging application example? I’m glad you asked. The below query takes in an input parameter of a page number and number of rows per page and returns only the records that would be displayed on that page. So in this case it brings back rows 76 through 90. I think this is really cool because not only is it code that I had to write myself in the past, but in the case where I might have been bringing back a large number of rows, I am limiting the amount of data that I am sending from my database server to my application tier.
-- We can also use the RowNumber to select a subset of Rows
DECLARE @PageNumber int
DECLARE @PageSize int
SET @PageNumber = 6;
SET @PageSize = 15;
WITH OrderedSales AS
(Select ROW_NUMBER () OVER(Order By OrderDate ASC, TotalDue DESC) as RowNum,
TotalDue, CustomerID, SalesOrderID, OrderDate From Sales.SalesOrderHeader
Where OrderDate BETWEEN '6/6/2004' AND '6/8/2004')
Select * From OrderedSales
WHERE RowNum BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize
Order By RowNum
You may have noticed the “WITH” keyword that I am using in the above query. The WITH is a common table expression (CTE). I will cover that in a future post. I hope this post was helpful in giving you an introduction to the ROW_NUMBER() function.
Now playing: Killers - Mr. Brightside
A friend of mine in San Diego Troy hosts a weekly program called Fox Rox that talks about the local San Diego rock scene and a lot of new music on the scene.
In his latest show he talked about a web site called Pandora that allows you create a Radio Station with your favorite artists/songs and it will recommend other artists based on those choices. It is pretty cool. I was amazed at some of the recommendations that it was spitting out. I think it is much cooler that something like Amazon recommendations because it is based on the actual attributes of a song. For example, it described an Alice in Chains song as “It features mellow rock instrumentation, mile rhythmic syncopation, acoustic sonority, a vocal-centric aesthetic and major key tonality.”
To continue along the new music theme, another buddy of mine, Dan, has a site called http://www.musicrightnow.com/ that highlights 2–3 new bands each week in his Wednesday Music Dose e-mail.
I love finding new bands and new music. Do you have any favorite sources for new music? I would love to hear about them in the comments.
Don Peterson, a Clarity Consultant has just gotten an MSDN article published on Creating Starter Kits. Check out the article here. I think the most important thing to get out of this article is not creating starter kits but applying this to creating project templates. Don sums it up really well in this post.
I just found out that I had two sessions accepted for the Southern California Code Camp that is being held in Fullerton, CA on January 21–22, 2005.
The Code Camp counselors are an elite group consisting of Woody Pewitt, Michele Leroux Bustamante, Brian Maso, & Daniel Egan.
They have done a wonderful job assembling an outstanding array of sessions. I am really looking forward to presenting and also attending some of the other sessions. A full listing of the sessions that will be presented at Code Camp can be found here: http://www.socalnetevents.org/Default.aspx?tabid=86
It is going to be a great weekend so if you are in the SoCal next weekend, feel free to stop by. The greatest thing about Code Camp is that it is free.