举几个例子来简单说明 这些例子我引用了Northwind库。
1. TOP 表达式
SQL Server 2000的TOP是个固定值，是不是觉得不爽，现在改进了。
–前n名的订单
declare @n int
set @n = 10
select TOP(@n) * from Orders
declare @n int
set @n = 10
select TOP(@n) * from Orders
2. 分页
http://www.gaodaima.com/?p=37136SQL Server 2005新功能_sqlserver
不知各位过去用SQL Server 2000是怎么分页的，大多都用到了临时表。SQL Server 2005一句话就支持分页，性能据说也非常不错。
—按Freight从小到大排序，求20到30行的结果
select * from(
select OrderId, Freight, ROW_NUMBER() OVER(order by Freight) as row from Orders
) a
where row between 20 and 30
select * from(
select OrderId, Freight, ROW_NUMBER() OVER(order by Freight) as row from Orders
) a
where row between 20 and 30
3. 排名
select * from(
select OrderId, Freight, RANK() OVER(order by Freight) as rank from Orders
) a
where rank between 20 and 30
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没有异常，T-SQL必须逐行检查错误代码，对于习惯了try catch程序员，2005是不是更加亲切：
SET XACT_ABORT ON — 打开 try功能
BEGIN TRY
begin tran
insert into Orders(CustomerId) values(–1)
commit tran
print ‘commited‘
END TRY
BEGIN CATCH
rollback
print ‘rolled back‘
END CATCH
BEGIN TRY
begin tran
insert into Orders(CustomerId) values(–1)
commit tran
print ‘commited‘
END TRY
BEGIN CATCH
rollback
print ‘rolled back‘
END CATCH
5. 通用表达式CTE
通过表达式可免除你过去创建临时表的麻烦。
—例子：结合通用表达式进行分页
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
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
特别，通过表达式还支持递归。
6. 直接发布web Service
想要把store procedure变成Web Service就用这个吧，.NET, IIS都不需要，通过Windows 2003的HTTP Protocol Stack直接发布Webservice,用这个功能需要Windows 2003 sp1
—DataSet CustOrdersOrders(string customerID)
CREATE ENDPOINT Orders_Endpoint
state=started
as http(
path=‘/sql/orders‘,
AUTHENTICATION=(INTEGRATED),
ports=(clear)
)
for soap(
WebMethod ‘CustOrdersOrders‘(
name=‘Northwind.dbo.CustOrdersOrders‘
),
wsdl=default,
database=‘Northwind‘,
namespace=‘http://mysite.org/‘
)
CREATE ENDPOINT Orders_Endpoint
state=started
as http(
path=‘/sql/orders‘,
AUTHENTICATION=(INTEGRATED),
ports=(clear)
)
for soap(
WebMethod ‘CustOrdersOrders‘(
name=‘Northwind.dbo.CustOrdersOrders‘
),
wsdl=default,
database=‘Northwind‘,
namespace=‘http://mysite.org/‘
)
Web Service就发布好了，敲入http://localhost/sql/orders?wsdl得到wsdl
在http通讯时，例子代码使用的是Windows集成验证 。
给个客户端例子。
给个客户端例子。
private void button1_Click(object sender, EventArgs e)
{
localhost.Orders_Endpoint endp = new localhost.Orders_Endpoint();
endp.Credentials = System.Net.CredentialCache.DefaultCredentials;
object[] r = endp.CustOrdersOrders(“VINET“);
foreach(object o in r)
if (o is DataSet) ds = (DataSet)o;
}
{
localhost.Orders_Endpoint endp = new localhost.Orders_Endpoint();
endp.Credentials = System.Net.CredentialCache.DefaultCredentials;
object[] r = endp.CustOrdersOrders(“VINET“);
DataSet ds
= new DataSet();foreach(object o in r)
if (o is DataSet) ds = (DataSet)o;
dataGridView1.DataSource
= ds.Tables[0];}
SQL Server 2005支持basic, digest, sql auth等多种验证，你需要编写不同的验证代码。
欢迎大家阅读《SQL Server 2005新功能_sqlserver》,跪求各位点评,若觉得好的话请收藏本文，by 搞代码
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：SQL Server 2005新功能_sqlserver
转载请注明原文链接：SQL Server 2005新功能_sqlserver
