Select TOP N Records

Most times while retrieving records from database we like to fetch top N records
There are couple of ways to do it..
here in this blog I have tried with

1) TOP
2) ROWCOUNT
3) ROW_NUMBER()

out of all the three I could prefer to use TOP.
ROWCOUNT will take priority if its specified smaller than TOP.
ROWCOUNT cannot be used in Functions.
ROW_NUMBER() can be used to fetch top N records with partition.

Here We Go..!!

USE AdventureWorks

Using TOP:

SELECT TOP 10 * FROM Person.StateProvince

with variable:

DECLARE @N INT
SET @N = 10
SELECT TOP (@N) * FROM Person.StateProvince

Using ROWCOUNT:

SET ROWCOUNT 10
SELECT * FROM Person.StateProvince

--TO RESET THE ROWCOUNT
SET ROWCOUNT 0

Using ROWNUMBER:

SELECT * FROM
( SELECT ROW_NUMBER()OVER(PARTITION BY 1 ORDER BY NAME) AS ROW_NUM,* FROM
Person.StateProvince
) TAB
WHERE ROW_NUM <=10