SQL Server 2005 New Features-TSQL_PHP Tutorial

SQL Server 2005 has great improvements over SQL Server 2000, and some of them are very practical.
Give a few examples to briefly explain these examples. I quoted the Northwind library.
1. TOP expression
The TOP of SQL Server 2000 is a fixed value. Do you feel uncomfortable? It has been improved now.

--Top n orders
declare @n int
set @n = 10
select TOP(@n) * from Orders
2. Pagination
I don’t know How did you use SQL Server 2000 for paging in the past? Most of them used temporary tables. SQL Server 2005 supports paging in one sentence, and the performance is said to be very good.

--Sort by Freight from small to large, find the results of rows 20 to 30
select * from(
select OrderId, Freight, ROW_NUMBER() OVER(order by Freight) as row from Orders
) a
where row between 20 and 30
3. Ranking
select * from(
select OrderId, Freight, RANK() OVER(order by Freight) as rank from Orders
) a
where rank between 20 and 30
4. try ... catch
SQL Server 2000 has no exceptions. T-SQL must check the error code line by line. For programmers who are used to try catch, 2005 Is it more friendly:
SET XACT_ABORT ON -- Turn on the try function
BEGIN TRY
begin tran
insert into Orders(CustomerId) values(-1)
commit tran
print ' committed'
END TRY
BEGIN CATCH
rollback
print 'rolled back'
END CATCH
5. Universal Expression CTE
Expressions can save you from creating temporary Table trouble.
www.knowsky.com
--Example: Combined with universal expressions for paging
WITH OrderFreight AS(
select OrderId, Freight, ROW_NUMBER() OVER(order by Freight) as row from Orders
)
select OrderId, Freight from OrderFreight where row between 10 and 20
Specially, recursion is also supported through expressions.
6. Directly publish Web Service
If you want to turn the store procedure into a Web Service, use this. .NET and IIS are not required. Directly publish the Web Service through the HTTP Protocol Stack of Windows 2003. Using this function requires Windows 2003 sp1
--DataSet CustOrdersOrders(string customerID)
CREATE ENDPOINT Orders_Endpoint
state=started
as http(
path='/sql/orders',
AUTHENTICATION=(INTEGRATED ),

http://www.bkjia.com/PHPjc/631100.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631100.htmlTechArticleSQL Server 2005 has great improvements over SQL Server 2000, and some of them are very practical. Let's give a few examples to briefly explain. I quoted the Northwind library for these examples. 1. TOP expression SQL Serv...