TOP clause in T-SQL

Using TOP in T-SQL select given number of rows. It limits the rows returned in a query result set.

Let begin with TOP clause. Set AdventureWork2012 database up as current in SQL Server. You select all names of people and  modified date from Person table.

SELECT 
  FirstName, LastName, ModifiedDate
FROM Person.Person;

In result it is 19.972 rows. How select only 7 first rows? The TOP may do it.

SELECT TOP 7
  FirstName, LastName, ModifiedDate
FROM Person.Person;

Or

SELECT TOP (7)
  FirstName, LastName, ModifiedDate
FROM Person.Person;

After TOP it is correct to write only integer number or write integer number in brackets.
Through TOP You may either select number of rows in percentage terms.

SELECT TOP 1 PERCENT
  FirstName, LastName, ModifiedDate
FROM Person.Person;

This query select 200 rows. In percentage the number may be either in brackets.

SELECT TOP (1) PERCENT
  FirstName, LastName, ModifiedDate
FROM Person.Person;

But if You would like present fraction of percent, You must use float number in brackets.

SELECT TOP (0.04) PERCENT
  FirstName, LastName, ModifiedDate
FROM Person.Person;

The 0.04 percent of all rows is 8 rows.

After TOP the value is convert to bigint or if PERCENT is used to float datatype.

If TOP is used  with the ORDER BY clause, the result set is limited to the first given after TOP number of ordered rows. It is example to above query.

SELECT TOP (0.04) PERCENT
  FirstName, LastName, ModifiedDate
FROM Person.Person
ORDER BY LastName,FirstName;

The result  with ORDER BY clause:

In TOP with ORDER BY clause is used either WITH TIES expression. It denote that if You select only 3 ordered rows and last row and another several after them have identical data in columns given after ORDER BY clause, this additional rows will be display. It is example, if You select 3 last names from Person table order by LastName column:

SELECT TOP 3 LastName
FROM Person.Person
ORDER BY LastName;

The query displays 3 rows:

Using WITH TIES to TOP clause display 5 rows:

SELECT TOP 3 WITH TIES LastName
FROM Person.Person
ORDER BY LastName;

Using WITH TIES to TOP clause for several diffrent columns and only one column in ORDER BY, display either 5 rows:

SELECT TOP 3 WITH TIES FirstName, LastName, ModifiedDate
FROM Person.Person
ORDER BY LastName;

WITH TIES may be use only in SELECT statement and with ORDER BY clause. The TOP is used in SELECT, INSERT, UPDATE, DELETE, MERGE statement.