SQL Server 2005 practical examples

A gain finally insertion record：scope_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()

Result：

3 nanfangxiaogui

High performance paging：ROW_NUMBER()

USE AdventureWorks;

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;

Gains the record number highly effective：sysindexes

select rows from sysindexes where id = object_id('tablename') and indid in (0,1)

Sponsor