[tips]SQL 2005 AND 2008

本篇文章将会持续更新，是08年一个帖子（[tips]T_SQL @ 2000 AND 2005）的更新

主要是比较难找了，而且工作中发现有一些有趣的tips记录下来，方便以后查找。

  

====================May/26/2010============================

获取数据库服务器的当前的链接session信息，侧重于sql方面的一些信息

select * from sys.dm_exec_sessions
where is_user_process = 1

 

获取数据库服务器当前连接的信息，侧重于网络信息

select * from sys.dm_exec_connections

 

 

====================Aug/24/2012============================

paging based on row_number of SQL Server

 

declare @p0 int, @p1 int
set @p0=1000
set @p1=100;

With ct As 
(
    Select ROW_NUMBER() Over (Order By column0) As Row,
        column0  
     From table0 --where column1= 'key word'
)
Select 
        [t0].[column0], 
        [t0].[column1], 
        [t0].[column2], 
        [t0].[column3],
        [t0].[column4]
From ct c
Inner Join table0 t0 on c.column0 = t0.column0
Where c.Row Between @p0 + 1 AND @p0 + @p1
Order By c.Row Asc

 

 

 

转载于:https://www.cnblogs.com/yang_sy/archive/2010/05/26/1744093.html