New Features in SQL Server 2005 - SQL TOP Clause
Microsoft SQL Server has introduced many T-SQL enhancements for sql developers in order to create rapid and more powerful database applications with a better database performance.
What is new in SQL Server 2005 ?
One of these t-sql enhancements and new features in SQL Server 2005 first introduced with SQL Server 2005 aka Yukon is TOP clause.
Let's see what is new in MS SQL Server 2005 related with TOP clause.
T-SQL TOP Clause
SQL developers know that TOP clause is not new with SQL Server 2005, but it has serious improvements that will effect development process with code-name Yukon, SQL Server 2005 T-SQL enhancements.
In previous versions, MS SQL Server 2000 t-sql programmers could only get limited benefit from sql TOP expression.
Because developers should set the TOP argument as a constant value.
For example if tsql developer want to fetch top 10 records, he should set the "10" within the sql codes like :
SELECT TOP 10 * FROM Customers
A sql developer should definitely know the items count he wants to select from a sql table or from a sql view.
This is the case seen in the above SELECT statement.
Actually what is new in t-sql for sql TOP statement is, now with SQL Server 2005 developers can build dynamic sql statements using expression in TOP clause.
But how you can use an expression in TOP clause?
Here is a sql sample TOP select statement which returns sql top 10 rows from Customers table:
DECLARE @top INT
SET @top = 10
-- Afterwards TOP can be used as follows in T-SQL codes
SELECT TOP (@top) * FROM Customers
-- Or
SELECT TOP (SELECT @top) * FROM Customers
-- Or
SELECT TOP (SELECT 10) * FROM Customers
Unfortunately if you use the numeric variable in TOP clause as follows, it will cause a sql syntax error:
Incorrect syntax near '@top'.
SELECT TOP @top * FROM Customers
Although there is a note saying "()" is not required in order to keep backward compability, the above usage without "()" surrounding the numeric variable expression MS SQL Server 2005 Beta 2 is throwing the sql syntax error.
As a sql developers, I'm excited with SQL Server 2005 new features especially with the t-sql improvements.
I'll be demonstrating new features of SQL Server 2005 in sql tutorial in this web site.