SQL Server 2005 实用例子
- 获取最后插入的一条记录:scope_indentity()
scope_indentity() 返回插入到同一作用域中(存储过程,.......)的indentity列内的最后一个indentity值
declare @dt table
( _id int identity(1,1),
_name char(10)
)
insert into @dt (_name) values('scottxu')
insert into @dt (_name) values('psg')
insert into @dt (_name) values('nanfangxiaogui')
select * from @dt where _id=scope_identity()
结果:
3 nanfangxiaogui
- 高性能分页:ROW_NUMBER()
以下示例将返回行号为50
到60
(含)的行,并以OrderDate
排序。
USE AdventureWorks;
GO
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;
- 高效获取记录数:sysindexes
select rows from sysindexes where id = object_id('tablename') and indid in (0,1)