SQL Server T-SQL Query Learning Notes (3) _mssql2005

AD HOC Paging:

It means to request a separate page with the number of pages and the size of the page. Here is an example.

DECLARE @pagesize as int, @pagenum as int;
SET @pagesize = 5;
SET @pagenum = 2;

With Salescte as
(
 SELECT row_number ()-qty, Empid) as RowNum,
  empid, Mgrid, qty from
 dbo. Sales
)
SELECT rownum, Empid, Mgrid, qty from
salescte
WHERE rownum > @pagesize * (@pagenum-1) and
 rownum <= @pagesize * @pagenum
Order by RowNum;

Description: In the last example, in fact, SQL only looked at 10 lines (2*5), that is, to view the n pages, SQL only found the data of n pages, n pages after the data are not viewed.

In addition, whenever a page is moved, the page will be put into the cache, so each query, is the logical query (cache) + physical query process. The physical query only needs to query the new requested page, and all others are executed in the cache, which greatly speeds up the query.

MULTIPAGE ACCESS:

If the result set is not large and the request does not move forward with multiple request pages, this is a good solution: first use Row_number to materialize all the pages in one table and then create a clustered index. Here is an example.

First create press Row_number to make up the columns.

SELECT row_number () Over (Order by qty, empid) as RowNum, Empid, Mgrid, qty to #SalesRN from dbo. Sales;

CREATE UNIQUE CLUSTERED INDEX idx_rn on #SalesRN (rownum);

Then directly press rownum query,

DECLARE @pagesize as int, @pagenum as int; SET @pagesize = 5; SET @pagenum = 2; 
SELECT rownum, Empid, Mgrid, qty from #SalesRN WHERE rownum BETWEEN @pagesize * 
(@pagenum-1) + 1 and @pagesize * @page num ORDER by rownum;

Rank & Dense Rank

The difference between these 2 functions and the row_number is that the row_number have duplicate rows in the condition of the order by, and they are arranged by index, but rank and dense rank are always determined, that is, as long as the order by the duplicate row, They are the unified index.

The difference between rank and Dense_rank is that rank is if the upper index and subordinate index may not be +1 relationship, is the subordinate really in the column position for index, and Dense_rank is in accordance with the superior index+1 of the relationship between the code.

Like what:

SELECT Empid, qty, RANK () Over (Order by qty) as RNK, Dense_rank () over (the Order by qty) as Drnk from dbo. Sales Order BY Qty;

Ntile

Ntile's usage is the same as other rank functions, except that it can pass in a parameter that determines the maximum index: it divides by row number and then averages the number of rows to index.

For example, if there are 11 columns, then first 11/3=3,3 a group as a index, and then, 11%3=2, the 2 columns will be added to the previous 2 groups respectively.

Like what

SELECT Empid, qty, Case
 ntile (3) Over (Order by qty, empid) when
  1 THEN ' low ' when
  2 THEN 'medium ' 3 THEN ' high ' end aslvl from dbo. Sales Order by qty, empid;