SQL Server 2005 is packed with new features and enhancements. One of the enhancements in SQL 2005 that we are going to see is the clause, "TOP." The "TOP" clause will now allow us to do Data Manipulation and also allow similar criteria results to be displayed by using the TIES option.

Let us create a table and insert a bunch of rows. Execute the query below to create the table "MyTable2" and insert a few rows.

 
Create table Mytable2 (au_id int, 
 authors_name varchar(100))
Go
insert into MyTable2 select 110,'Mathew Arnold'
insert into MyTable2 select 140,'Keith Davis'
insert into MyTable2 select 76,'David Taylor'
insert into MyTable2 select 127,'Agatha Christy'
insert into MyTable2 select 12,'Sidney Sheldon'
insert into MyTable2 select 12,'Mcarthur'
insert into MyTable2 select 76,'Alan Smiles'
insert into MyTable2 select 100,'Kreisler'
insert into MyTable2 select 88,'Jeff Watch'
insert into MyTable2 select 99,'William Anderson'
insert into MyTable2 select 99,'William Anderson'
go

Let us query the entire table. Execute the query below to display the entire table.

Select * from MyTable2

Here is the result set.

Now let us query only the top 5 rows using the following query.

Select top 5 * from MyTable2

Here is the result set.

The same results can be produced using the set rowcount statement.

set rowcount 5
select * from MyTable2
set rowcount 0

Here is the result set.

In SQL 2005, the delete and update operations can also be performed using the TOP clause. Let us execute the following query to update the authors name in the first two rows of the table with the suffix 'Mr.'

Update top (2) MyTable2 set authors_name = 'Mr. ' + authors_name

select * from MyTable2

The query result will return all of the rows from the table after updating the top two rows. Notice the the top two rows have been updated with the suffix 'Mr.'

Here is the result set.

The same results can be produced using the set rowcount.

Set rowcount 3
Update  MyTable2 set authors_name = 'Mr. ' + authors_name
set rowcount 0
select * from MyTable2

Here is the result set.

Let us do a delete operation on the table using the TOP clause.

delete top (1) MyTable2 where left(authors_name,2)= 'Mr'
select * from MyTable2

Here is the result set.

The same results can be produced using the set rowcount.

set rowcount 1
delete MyTable2 where left(authors_name,2)= 'Mr'
set rowcount 0
select * from MyTable2

Here is the result set.

Another additional feature of the TOP clause is the number specified after the clause TOP can be substituted by a variable. Using this feature it is easy to change the number of limiting rows on the fly.

Example:

declare @n int
set @n=3
select top (@n) *  from MyTable2

Here is the result set.

The TOP clause also comes with another feauture called TIES. If you would like to include similar criteria together when using TOP clause, then TIES can be used.

For example the following query returns the TOP 10 % of the table.

select top 10 percent
*
from mytable2 order by au_id

Here is the result set.

But we know that there is another row with the same au_id values.

Now let us include the TIES option in the query.

select top 10 percent with ties
*
from mytable2 order by au_id

Here is the result set.

Conclusion

This article has illustrated the new enhancements of the TOP clause in SQL 2005.