- 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。
- 在所有的存储过程、触发器或者没有必要返回受影响行的处理中。在SQL的开始处设置 SET NOCOUNT ON; ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。 (如有需要防止脏数据或者并发数据,加上锁SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;)
SQL收藏:
取第10-15的记录
select top 5 * from YX_T_Khb where khid not in (select top 10 khid from YX_T_Khb)
从EXCEL获取信息
select * from
openrowset('MicroSoft.Jet.OleDB.4.0','Excel 5.0;HDR=yes;Database=d://web_tlerp/kpinfo.xls',aa$)
备份sql server
--创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:/mssql7backup/MyNwind_1.dat'
--sp_addumpdevice:存储过程会将一个备份设备添加到 sys.backup_devices 目录视图中
--sp_addumpdevice 不执行对物理设备的任何访问。
--select * from sys.backup_devices (查询所有备份目录视图)
--开始 备份
BACKUP DATABASE pubs TO testBack
随机取出10条数据
select top 10 * from tablename order by newid()
获取表结构相关信息
Select 表名 = CASE WHEN a.colorder = 1 THEN d .name ELSE '' END,
表说明 = CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END,
字段序号 = a.colorder, 字段名 = a.name,
标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')
= 1 THEN '√' ELSE '' END, 主键 = CASE WHEN EXISTS
(Select 1
FROM sysobjects
Where xtype = 'PK' AND parent_obj = a.id AND name IN
(Select name
FROM sysindexes
Where indid IN
(Select indid
FROM sysindexkeys
Where id = a.id AND colid = a.colid))) THEN '√' ELSE '' END,
类型 = b.name, 占用字节数 = a.length, 长度 = COLUMNPROPERTY(a.id, a.name,
'PRECISION'), 小数位数 = isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),
允许空 = CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END, 默认值 = isnull(e.text,
''), 字段说明 = isnull(g.[value], '')
FROM syscolumns a LEFT JOIN
systypes b ON a.xusertype = b.xusertype INNER JOIN
sysobjects d ON a.id = d .id AND d .xtype = 'U' AND d .name <> 'dtproperties' and d.name='Orders' LEFT
JOIN
syscomments e ON a.cdefault = e.id LEFT JOIN
sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT JOIN
sysproperties f ON d .id = f.id AND f.smallid = 0
ORDER BY a.id, a.colorder
--获取指定表的字段
Select a.name AS 字段名, b.name AS 类型
FROM syscolumns a INNER JOIN
systypes b ON a.xusertype = b.xusertype INNER JOIN
sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'
Where (d.name = 'Products')
ORDER BY a.id
--SQL中用正则匹配查询
select * from tab1 where title like '%[0-9]abc[0-9]%'
索引列上>=代替>
低效:select * from emp where deptno > 3
高效:select * from emp where deptno >=4
两者的区别在于,前者dbms将直接跳到第一个deptno等于4的记录,而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的
用exists替代in ,可以提高查询的效率。
用not exists 替代 not in
优化group by(当然是你要group by 的字段了,其它字段用where就OK了~)
提高group by语句的效率,可以将不需要的记录在group by之前过滤掉。如:
低效:select job, avg(sal) from emp group by job having job = 'president' or job='manager'
高效:select job, avg(sal) from emp having job='president' or job='manager' group by job
---------------------------------------------------------------
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同
AS
BEGIN
RETURN Day(dateadd(mi,-3,DATEADD(m, DATEDIFF(m,0,@date)+1,0)))
END
或
SELECT DATEADD(week, DATEDIFF(week,0,getdate()),0)
4.一个季度多少天
declare @m tinyint,@time smalldatetime
select @m=month(getdate())
select @m=case when @m between 1 and 3 then 1
when @m between 4 and 6 then 4
when @m between 7 and 9 then 7
else 10 end
select @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01'
select datediff(day,@time,dateadd(mm,3,@time))
5.本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
6.如何取得某个月的天数
declare @m int
set @m=2 --月份
select datediff(day,'2003-'+cast(@m as varchar)+'-15' ,'2003-'+cast(@m+1 as varchar)+'-15')
7.判断是否闰年
SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))) when 28 then '平年' else '闰 end
或者
select case datediff(day,datename(year,getdate())+'-02-01',dateadd(mm,1,datename(year,getdate())+'-02-01'))
when 28 then '平年' else' 闰年' end