MSSQL中计算两个日期之间相隔 xx天xx小时xx分xx秒
DECLARE @dt1 AS datetime, @dt2 AS datetime;
SELECT @dt1 = '2008-8-4 9:36:41', @dt2 = '2008-8-2 9:33:39';
DECLARE @Days AS int, @Hours AS int, @Minutes AS int, @Seconds AS int;
SET @Seconds = DATEDIFF( second, @dt2, @dt1);
SET @Days = @Seconds / (24 * 60 * 60)
SET @Seconds = @Seconds - @Days * 24 * 60 * 60
SET @Hours = @Seconds / (60 * 60);
SET @Seconds = @Seconds - @Hours * 60 * 60
SET @Minutes = @Seconds / 60;
SET @Seconds = @Seconds - @Minutes * 60;
SELECT CONVERT(varchar(10), @Days ) + '天'
+ CONVERT(varchar(10), @Hours ) + '小时'
+ CONVERT(varchar(10), @Minutes ) + '分'
+ CONVERT(varchar(10), @Seconds ) + '秒';
相关文档：
锁机制
NOLOCK和READPAST的区别。
1. 开启一个事务执行插入数据的操作。
BEGIN TRAN t
INSERT INTO Customer
SELECT 'a','a'
2. 执行一条查询语句。
SELECT * from Customer WITH (NOLOCK)
结果中显示"a"和"a"。当1中事务回滚后，那么a将成为脏数据。(注:1中的事务未提交) 。NOLOCK表明没有对数据表添加共� ......
How is MSSQL using memory?
http://www.sqlhacks.com/Administration/Memory-Usage
Memory is one the most important factor affecting MSSQL performance.
As an administrator, you should be monitoring the memory
regularly. When Microsoft SQL Server runs out of memory, it will use
virtual memory: ie: ......
Sql2005中使用ow_number() partition进行分组实验，
SQL：
select * from stu
select id,row_number() over (partition by snm order by id) from stu
结果：
id snm
----------------
111 111V
111 111W
222 222N
333 3123
444 3123
555 3123
666 3232
777 3232
--分组后的结果
id &n ......
--此代码实现SQL数据库远程备份，放到作业里面执行可以自动备份数据库、自动删除@keepNDays天前备份。
--此代码将本地所有的用户数据库备份到共享目录“\\backupServerIp\ShareName\数据库备份”下。
--并删除天前的备份文件。要备份成功必须能够对共享目录有操作权限！
sp_configure 'xp_cmdshell',1 ......
