Friday, 14 March 2008

SQL server 2005 : TOP Clause

There are few enhancements in the TOP clause, (SQL Server 2005). I have pointed out few, which I would like to share with you all. It’s very important to keep ourselves updated with the new features and enhancements, so keep reading when ever you get time.

1) In SQL 2005, the delete and update operations can also be performed using the TOP clause

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


The query will update the top two rows. Similarly we can use it with DELETE.


2) 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


3) The TOP clause also comes with another feature 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 Sanjeev sharma


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 Sanjeev Sharma

12 Gourav Verma





Happy Programming J

kick it on DotNetKicks.com

No comments:

Post a Comment