Explore the evaluation function for SQL Server 2005

First, Introduction

In November 2005, Microsoft released three new product lines: Visual Studio 2005,sql Server 2005 and. NET Framework 2.0 (it includes ASP.net 2.0). SQL Server 2005 is the most recent version of Microsoft's database platform since its last major release, SQL Server 2000. In the past five years of development, SQL Server has added a lot of new features, all of which are summarized in a Microsoft website article "What ' New in SQL Server 2005." Developers of web-based applications that use SQL Server 2005 as a back-end database are likely to have a strong interest in these new features, including new T-SQL improvements, better Visual Studio integration, and clr/. NET Framework and SQL Server Management Studio application (which is a more "smooth" version of SQL Server 2000 's Enterprise Manager).

Compared to previous SQL Server 2000, the T-SQL improvements in 2005 make it extremely easy to write certain types of queries. In SQL Server 2005, the T-SQL syntax is more concise, readable, and easy to understand.

In this article, we will focus on the evaluation functions of SQL Server 2005, which greatly simplify the process of evaluating query results.

II. data model and basis of evaluation results

Before we analyze how to use normal query mode, let's first create a data model that can run these queries. In this article, I used the SQL Server version of Express to implement my demo, and included a database and a asp.net 2.0 website (please refer to the appropriate complete source code in this article). Like Visual Studio, there are several other versions of SQL Server releases that are mixed with each other. Among them, the Express version is a free version for amateurs, students and other groups. If you download and install Visual Studio Developer (Express version for Web developers), you can choose to install the SQL Server Express version together.

For the example in this article, we will use a database that contains products, sales people (employees), customers, and order information. We use five tables to model: Customers,employees,products,orders and OrderItems. The Customers,employees and Products table contains the row record data for each customer, employee, and product information respectively. Each time a customer buys an activity, a new record is added to the Orders table, and the information indicates that the customer has achieved the purchase, the sales activity and the date of the order. where OrderItems maps each product in the order, the quantity and value of the product (assuming a larger purchase can be discounted). The following figure shows the tables (and fields) and the relationships between them.

As shown in the figure, this orderitems establishes a pair-pair connection between the orders and the Products table.

When building reports or profiling data, users or administrators often want to see the evaluation of data in some way. For example, your boss may want a report that shows the top ten best selling routes, or the top three salespeople who have the biggest revenue in the sales department for the third quarter. A more complex scenario might be to return only the 3rd to 5th ranked salespeople. In SQL Server 2000, queries that return the highest ranking items can be implemented by using the top or rowcount keywords. To retrieve a specific subset of evaluations, you need to use a "derived table" (or a view-based approach).

Four new evaluation functions were introduced in SQL Server 2005: Row_number,rank,dense_rank and Ntile. Although these are a significant improvement over the functions provided by SQL Server 2000, the use of these functions still has some limitations (requiring the use of derived tables or views to implement more powerful applications). Let's take a look at each of these functions.

Use the Row_number function to calculate the number of rows

The Row_number function assigns an ordinal value to each returned record, which relies on a particular order by statement that is used with the function. The syntax for a function row_number is: row_number () over ([partition] an ORDER BY clause). For example, the following query will return from the most expensive to the cheapest product and evaluate each product by Price:

SELECT ProductID，Name，Price， ROW_NUMBER() OVER(ORDER BY Price DESC) As PriceRankFROM Products

The execution result of this statement is shown in the following table:

productid	name< /td>	price	pricerank
desk	495.0000	10	executive Chair	295.0000	2
9	125.0000	3
mouse	14.9500	6	mousepad	9.9900	5
11	8.5000	6
stapler	7.9500	7
3	1.9500	8