Most people already know you can now write stored procedures in .NET with SQL Server 2005 (and Express), but there are other enhancements that many may miss. Here are a few I have run into lately.
* XML data type. XML is now a legitimate data type you can assign as a column in a table, return in a result set or pass as a parameter to a stored procedure. It is now simple to use XML in SQL Server on about any level.
* Data type limits have been super-sized! Data types such as char, nchar, varchar, nvarchar and varbinany, can now specify "MAX" length storing values as large as 2 GB without the special handling used for text, ntext and image data types. I have not checked, but I would think the 'like' and other functions would now be compatible with them, which was not possible with the nText, Text and image types.
* Goodbye to many temporary tables and ugly sub queries and enter 'Common Table Expressions'. Now you can specify a temporary result set that lives for the duration of your select, update, delete or insert query and treat it as a table. There are numerous times I could use such an animal, but one that really comes to mind is an example in the online books, using it to return a hierarchical list through recursion.
* The TOP operator is now usable with Insert, Update and Delete statements. Not only that, but you can now use a variable for the value instead of it requiring a numeric text value and you can even specify as a percentage. Oh, the number of times I had to build queries dynamically as a string in a stored procedure executing them only to specify a variable "TOP" parameter!
* New Ranking functions. There are several new functions to handle ranking:
- Rank()
- Dense_Rank()
- NTile(x)
- Row_Number()
All of these are used with an "Over(order by...)" clause. If you happen to have the "pubs" database on your server, here is a sample query:
use pubs
select
rank() over(order by qty) as rank,
dense_rank() over(order by qty) as Dense_Rank,
NTile(10) over(order by qty) as NTile,
Row_Number() over(order by qty) Row_Number,
Row_Number() over(partition by qty order by qty) Row_Number_P,
qty
from sales
The results are:
|
Rank |
Dense_Rank |
NTile |
Row_Number |
Row_Number_P |
qty |
|
1 |
1 |
1 |
1 |
1 |
3 |
|
2 |
2 |
1 |
2 |
1 |
5 |
|
3 |
3 |
1 |
3 |
1 |
10 |
|
3 |
3 |
2 |
4 |
2 |
10 |
|
3 |
3 |
2 |
5 |
3 |
10 |
|
6 |
4 |
3 |
6 |
1 |
15 |
|
6 |
4 |
3 |
7 |
2 |
15 |
|
6 |
4 |
4 |
8 |
3 |
15 |
|
9 |
5 |
4 |
9 |
1 |
20 |
|
9 |
5 |
5 |
10 |
2 |
20 |
|
9 |
5 |
5 |
11 |
3 |
20 |
|
9 |
5 |
6 |
12 |
4 |
20 |
|
13 |
6 |
6 |
13 |
1 |
25 |
|
13 |
6 |
7 |
14 |
2 |
25 |
|
13 |
6 |
7 |
15 |
3 |
25 |
|
13 |
6 |
8 |
16 |
4 |
25 |
|
17 |
7 |
8 |
17 |
1 |
30 |
|
18 |
8 |
9 |
18 |
1 |
35 |
|
19 |
9 |
9 |
19 |
1 |
40 |
|
20 |
10 |
10 |
20 |
1 |
50 |
|
21 |
11 |
10 |
21 |
1 |
75 |
First notice that Row_Number is in sequential order for the rows returned. This one feature makes it very easy to add paging to results, but more on that in a moment.
As you can see, the Rank column skips ranking after ties as in Row_Number 3-5, there is a three-way tie for third place, but there is no 4 or 5 place since 4 and 5 tied with 3 for third place. Same thing occurs in 9 place where there is a four-way tie, which skips places 10-12.
Dense_Rank does not skip numbers on ties. I think this version is much more usable as I have never heard of rankings that are skipped on ties.
NTile spreads the rank out evenly throughout the range of 1 to (x) you specified. In this example, it is 10, which spreads the values 1-10 throughout all the rows of the results.
In addition to these, you can use a "partition by" argument to the 'over' clause and gain a form of “group by“, where it will reset the ranking numbers (Rank(), Dense_Rank(), NTitle(x), Row_Number()) based on the values of the "partition by" you specified.
As you can see in the Row_Number_P column, the value keeps resetting to one every time the qty changes.
* Paging! Paging! Paging!
All of this exploration today, came about because I was having a problem in a SQL 2000 database using a "Select into" with an "order by" for paging purposes. This is not reliable in SQL Server 2000 and you need to use an alternative means to obtain the results. Instead, I decided to see what it would be like under SQL 2005.
Much to my surprise, the problem code worked perfectly on SQL 2005 without change. That was no good as I wanted to play with SQL Server 2005 a bit and explore the Rank functions.
I found paging is now quite easy. Here is code to page the pubs.sales table:
use pubs
select stor_id, qty, title_ID
from
(
select *, row_number() over(order by qty) as Row_Number
from sales
) as resultData
where Row_Number between 6 and 10
If we were paging the data at five rows per page, this would return page two of our data as specified by the rows "between 6 and 10". This is easy to use, not sure about performance, but it is handy!
Let us now apply our new Common Table Expressions to the code, cleaning it up a bit:
use pubs
with StoreSales as
(
select *, row_number() over(order by qty) as Row_Number
from sales
)
select stor_id, qty, title_ID
from StoreSales
where Row_Number between 6 and 10
There is a lot of new power coming our way with the release of SQL Server 2005. Features like these are just frosting on our cake!