Happy Programming

1. SELECT ProductID, TransactionDate,
Row_Number() OVER(ORDER BY TransactionDate) as RowNumDate,
Row_Number() OVER(ORDER BY ProductID) + 5000 as RowNumiD
FROM Production.TransactionHistory

-- not permitted
Where Row_Number() OVER(ORDER BY TransactionDate) Between 10001 AND 20000
ORDER BY TransactionDate;

New feature in sql 2005, here, using Row_Number in where close is not permitted.

2. So if we want to use mid range of rownumbers use this way

-- Select mid-range of rownumbers & every 5th row
SELECT RowNum, ProductID, TransactionDate
FROM (SELECT ProductID, TransactionDate,
Row_Number() OVER(ORDER BY TransactionDate) as RowNum
FROM Production.TransactionHistory) sq
WHERE RowNum Between 10001 AND 20000
-- AND RowNum%5 = 0
ORDER BY RowNum ;

In here use sub query as a table and get back the result set

3. Ranking and Windowing - these are very helpful when we are reporting

SELECT SalesOrderId, CustomerId, TerritoryID,
ROW_NUMBER() OVER(ORDER BY CustomerId) AS RowNum,
RANK() OVER(ORDER BY CustomerId) AS [Rank],
DENSE_RANK() OVER(ORDER BY CustomerId) AS [DenseRank],
NTILE(5) OVER(ORDER BY CustomerId) AS NTile5,
RANK() OVER(PARTITION BY TerritoryID ORDER BY CustomerId) AS [Partition]
FROM Sales.SalesOrderHeader;

4. Sample Data - randomly select data


SELECT
SalesOrderId, CustomerId
FROM Sales.SalesOrderHeader TABLESAMPLE (10000 rows)

return random 8000, 11400, 12000 rows. it's not a bug at all and here this method is more powerful than Select Top 1000 way.

SELECT
SalesOrderId, CustomerId
FROM Sales.SalesOrderHeader TABLESAMPLE (10 percent)
ORDER BY CustomerId