Have you ever decided to chip away at several items one time because you are sick and tired of seeing them stack up. You know you want to do them and probably have for a while, but they keep getting pushed back. Well I'm doing that with a couple of tasks right now.
One of them includes reading an acquaintance's book Peter DeBetta with Wintellect has authored a book on MSPress in-titled “Introducing Microsoft SQL Server 2005 for Developers.” I have had the pleasure of meeting Peter in Dallas a couple of times. Nice guy!
I have already been using SQL Server 2005 thanks to Scalability Experts, Microsoft, MS Learning and all the awesome sites out there. I enjoy SQL Server and I do a lot of training in it. Yet to think I know it all is a far cry from reality. I enjoy reading several authors including Ken Henderson, Bob Beachum, Kalen Delaney, and others to see their perspective and to pick up items that I might never have considered.
If your reading DeBetta's (or anyone's) book, I hope you are taking the time to write notes on the paper and have a highlighter near by as well. I have been doing this and thought I might share a couple of thoughts as I go along. Unless I particularly state that I think Peter is mistaken, do not read any more into my observations of his code or instruction other than, “Hey, this is an idea I would consider doing different and is just a preference.“
The first is on page 6. Listing 1-1 has the following code:
SELECT
Row_Number() OVER (ORDER BY OrderDate DESC) AS RowNum,
SalesOrderID, CustomerID, OrderDate
FROM Sales.SalesOrderheader
ORDER BY OrderDate DESC
The first eight results look like:
1 75084 11078 2004-07-31 00:00:00.000
2 75085 11927 2004-07-31 00:00:00.000
3 75086 28789 2004-07-31 00:00:00.000
4 75087 11794 2004-07-31 00:00:00.000
5 75088 14680 2004-07-31 00:00:00.000
6 75089 19585 2004-07-31 00:00:00.000
7 75090 27686 2004-07-31 00:00:00.000
8 75091 20601 2004-07-31 00:00:00.000
Somewhere along the way, I learned that your ORDER BY clause can take advantage of any Aliases you use in your Select clause.
So you can make a small change in your query like this:
SELECT
Row_Number() OVER (ORDER BY OrderDate DESC) AS RowNum,
SalesOrderID, CustomerID, OrderDate
FROM Sales.SalesOrderheader
ORDER BY RowNum
Notice the change ORDER BY RowNum.
The results return the same set:
1 75084 11078 2004-07-31 00:00:00.000
2 75085 11927 2004-07-31 00:00:00.000
3 75086 28789 2004-07-31 00:00:00.000
4 75087 11794 2004-07-31 00:00:00.000
5 75088 14680 2004-07-31 00:00:00.000
6 75089 19585 2004-07-31 00:00:00.000
7 75090 27686 2004-07-31 00:00:00.000
8 75091 20601 2004-07-31 00:00:00.000
Now I do not have to change the ORDER BY in two places if I which to change how the representation is presented. For example, I can now change the order of presentation by making the last line read, ORDER BY RowNum DESC.
Here are the last eight rows of my result:
8 75091 20601 2004-07-31 00:00:00.000
7 75090 27686 2004-07-31 00:00:00.000
6 75089 19585 2004-07-31 00:00:00.000
5 75088 14680 2004-07-31 00:00:00.000
4 75087 11794 2004-07-31 00:00:00.000
3 75086 28789 2004-07-31 00:00:00.000
2 75085 11927 2004-07-31 00:00:00.000
1 75084 11078 2004-07-31 00:00:00.000
Try doing that with the original code. Even if you do the last line as ORDER BY OrderDate ASC, the last eight rows are now:
33 75116 16402 2004-07-31 00:00:00.000
34 75117 18178 2004-07-31 00:00:00.000
35 75118 13671 2004-07-31 00:00:00.000
36 75119 11981 2004-07-31 00:00:00.000
37 75120 18749 2004-07-31 00:00:00.000
38 75121 15251 2004-07-31 00:00:00.000
39 75122 15868 2004-07-31 00:00:00.000
40 75123 18759 2004-07-31 00:00:00.000
Let me know what you think?
Until later.