"TOP" Clause in SQL Server 2005

September 7, 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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers