Top in SQL SERVER 2008

The TOP keyword allows you to return the first n number of rows from a query based on the number of rows or percentage of rows that you define.
The first rows returned are also impacted by how your query is ordered.

In previous versions of SQL Server, developers used SET ROWCOUNT to limit rows returned or impacted.

We can use the top keyword with the following queries:

Select

Insert

Delete

Update

Basically, most of us know about top, we can use it in a select query to get the top element from the query.

But top can also be used in Insert, Delete and Update commands for the table as well.

We will see how we can use top with Insert, Delete and Update.

Two sample tables

1 Top with select statement

Syntax: - SELECT TOP 10 * FROM <TNAME>

Example:- 

SELECT top(1) Name,Class,Age,Address,Phone,Pin FROM [dbo].[SampleTable]

2 Top with Insert Statement

It is when we have to insert only the top few records that we can use this:

Syntax:- 

Insert top (10) into <TName> (column1, column2.....)
Select column1, column2..... From <TName1> 
or 
Insert into <TName> (column1, column2.....)
Select TOP (10) column1, column2..... From <TName1> 

Example:- 

INSERT TOP (2) INTO [dbo].[SampleTable1]

SELECT Name,Class,Age,Address,Phone,Pin FROM [dbo].[SampleTable]

ORDER BY Class DESC

SELECT Name,Class,Age,Address,Phone,Pin FROM [dbo].[SampleTable]

SELECT Name,Class,Age,Address,Phone,Pin FROM [dbo].[SampleTable1]

 Top with Delete Statement

Deleting top records from a table:

Syntax: - Delete top (10) from <TName> where <Cond>

Example:- 

SELECT  Name,Class,Age,Address,Phone,Pin FROM [dbo].[SampleTable]

DELETE Top(1) FROM [SampleDB].[dbo].[SampleTable]

SELECT  Name,Class,Age,Address,Phone,Pin FROM [dbo].[SampleTable]

Top with Update statement

Updating top records from a table:

Syntax: - Update top (10) <TName> set Colmn1=<value> 

Example: - 

UPDATE TOP (1) [dbo].[SampleTable] SET Class='Hindi' WHERE Class='Msc'