T-SQL returns specific row data and paging query randomly

The T-SQL language adds a lot of features than standard SQL, and in order to improve SQL Server performance, it is necessary to get a deep understanding of these two minor issues that are typically included in an interview.

First, it is a random return of 5 lines of information in a AdventureWorks person.address, which can be written as follows:

Select Top 5 * 
from
(
    select *,newid () as Randomid to
    person.address
) T
ORDER by T.randomid

The key is to invoke the random number generated by the built-in function newid () and sort, taking the first 5 lines.

Paging query, you need to query person.address sorted by city after the 第50-200 line, of course, can write a stored procedure to specifically query by parameters.

SELECT * FROM
(
    select *,row_number ()-RowNumber from
    person.address
) as T
WHERE T.rownumber BETWEEN and 200

The key is to use the Row_number () function, to calculate the current row in all query rows is ranked, and then filter the final results.

Summary: With some language features, although the binding platform, however, on a large platform, it does not matter, it is worth it.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/