“TOP” Clause in SQL Server 2005

We all know the “TOP” clause returns the first n number of rows or percentage of rows thereby limiting the number of resulting rows displayed when we are selecting rows in a table.


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.








































au_id


AuthorsName


110


Mathew Arnold


140


Keith Davis


76


David Taylor


127


Agatha Christy


12


Sidney Sheldon


12


Mcarthur


76


Alan Smiles


100


Kreisler


88


Jeff Watch


99


William Anderson


99


William Anderson


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

Select top 5 * from MyTable2

Here is the result set.






















au_id


authors_name


110


Mathew Arnold


140


Keith Davis


76


David Taylor


127


Agatha Christy


12


Sidney Sheldon


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.






















au_id


authors_name


110


Mathew Arnold


140


Keith Davis


76


David Taylor


127


Agatha Christy


12


Sidney Sheldon


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.








































au_id


authors_name


110


Mr. Mathew Arnold


140


Mr. Keith Davis


76


David Taylor


127


Agatha Christy


12


Sidney Sheldon


12


Mcarthur


76


Alan Smiles


100


Kreisler


88


Jeff Watch


99


William Anderson


99


William Anderson


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.








































au_id


authors_name


110


Mr. Mr. Mathew Arnold


140


Mr. Mr. Keith Davis


76


Mr. David Taylor


127


Agatha Christy


12


Sidney Sheldon


12


Mcarthur


76


Alan Smiles


100


Kreisler


88


Jeff Watch


99


William Anderson


99


William Anderson


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.





































au_id


authors_name


140


Mr. Mr. Keith Davis


76


Mr. David Taylor


127


Agatha Christy


12


Sidney Sheldon


12


Mcarthur


76


Alan Smiles


100


Kreisler


88


Jeff Watch


99


William Anderson


99


William Anderson


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.


































au_id


authors_name


76


Mr. David Taylor


127


Agatha Christy


12


Sidney Sheldon


12


Mcarthur


76


Alan Smiles


100


Kreisler


88


Jeff Watch


99


William Anderson


99


William Anderson


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.
















au_id


authors_name


76


Mr. David Taylor


127


Agatha Christy


12


Sidney Sheldon


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.










au_id


authors_name


12


Sidney Sheldon


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.













au_id


authors_name


12


Sidney Sheldon


12


Mcarthur


Conclusion


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


» See All Articles by Columnist MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles