Search This Blog

Enhancements of existing features in 2005

 

The Microsoft has made some significant improvements for some of the existing features of SQL server 2000 in SQL Server 2005.

One of the most important enhancements was done for the ‘TOP ‘Clause. The previous versions of SQL server accept only ‘Constant value’ for the ‘TOP’ clause, but in SQL Server 2005, we can pass any numeric expression.

Another important enhancement is, now, we can use ‘TOP’ even for Delete, Update and Insert statements.

Syntax: TOP (Expression) [PERCENT] [WITH TIES]

Expression can be any valid arithmetic expression and this specifies the no of rows affected or to be returned.

[PERCENT] Indicates that the query returns only the first expression percent of rows from the result set.

[WITH TIES] specifies, additional rows to be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP ...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.

Example1 :

USE AdventureWorks

DECLARE @n INT;

SET @n = 10 ;

SELECT TOP ( @n ) [AddressID], [AddressLine1],[City]

FROM Person.Address

 

 

Example2: TOP Clause WITH TIES

USE AdventureWorks

DECLARE @n INT;

SET @n = 10 ;

SELECT TOP ( @n ) WITH TIES [AddressID],[AddressLine1], [City]

FROM Person.Address

ORDER BY [City]

 

Example3: Use of ‘TOP’ while updating a table

DECLARE @updateTopTest table (

i int identity,

value int

)

INSERT INTO @updateTopTest

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6

SELECT * FROM @updateTopTest

--update top 3 rows

UPDATE TOP (3) @updateTopTest -– Top requires parenthesis for UPDATE

SET Value = Value * 10

SELECT TOP 5 * -- parenthesis is not mandatoty for SELECT

FROM @UpdateTopTest

 

Note: You should enclose the ‘expression’ in a parenthesis, in case you are using TOP clause with all operations other than SELECT.

Example4: TOP with DELETE

DECLARE @updateTopTest table (

i int identity,

value int

)

INSERT INTO @updateTopTest

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6

SELECT * FROM @updateTopTest

--delete first row

DELETE TOP (1)

FROM @updateTopTest

SELECT *

FROM @UpdateTopTest

No comments: