sql server笔记

1.类型转换
convert(varchar(5),[Stock Type])：将Stock Type转换成varchar
cast(expression as data_type)
2.null值处理
isnull([Salesman Name],N'未知')
若Salesman Name为null,则用 N'未知' 代替
nullif(expression1,expression2)
若2个值相等，那么返回空值，如果不等，则返回第一个值。
coalesce(expression [,...n])
返回第一个非空值。
2.RIGHT('0000000' + CONVERT(varchar(7),[Stock No]),7)
用Stock No从右边替换‘0000000’
3.RTRIM(Stock Create Domain)
去除Stock Create Domain右边的空格
LTRIM(Stock Create Domain)
去除Stock Create Domain左边的空格
4.货品编号
RIGHT('00' + CONVERT(varchar(2), a.[Stock Group]), 2) + RTRIM(a.[Stock Type]) +
RTRIM(CONVERT(varchar(2), a.[Stock Create Domain])) + RIGHT('0000000' + CONVERT(varchar(7),

a.[Stock No]), 7) AS N'货品编号',
5.日期直接用大于或小于，
如 [Transaction Date] >= '2012-09-01',
(Oracle 却要用to_date('2012-09-01','YYYY-MM-DD'))
6.like里面包含汉字的话，需要在前面加上 N,如
select top 100* from Bank a where a.Description like N'%國%'

8.STR()函数
这是一个将数字转换为字符串的快捷函数。这个函数有3个参数：数值、总长度和小数位数。
SELECT STR(123.4, 8, 4)
结果值的右边以0填充：123.4000。
9.DATEADD(DAY, -7, '2012-10-22')
日期前7天。
10.LEN()函数
LEN()函数用于返回一个代表字符串长度的整型值。
11.SUBSTRING()函数
SUBSTRING()函数能够从字符串的一个位置开始，往右数若干字符，返回一个特定长度的子字符串。
如SUBSTRING('George Washington' , 4, 6)结果为 'rge wa'
STUFF()函数
STUFF 函数将字符串插入另一字符串。它在第一个字符串中从开始位置删除指定长度的字符；然后将第二

个字符串插入第一个字符串的开始位置。
如stuff('ABCDEF',1,1,'')结果为‘BCDEF’
12.FOR XML PATH
13.取月初时间
(1).select CONVERT(DATETIME,CONVERT(VARCHAR(8),GETDATE (),111)+'01')
(2).declare @monthdate datetime
set @monthdate=convert(datetime,CONVERT(char(4),YEAR(getdate()))+'-'+CONVERT(char

(2),month(getdate()))+'-01')

14.刪除臨時表判斷
IF OBJECT_ID('tempdb..#RAMCheck') IS NOT NULL DROP TABLE #RAMCheck

15.查看SQL Server的统计信息
DBCC SHOW_STATISTICS('表名','索引名')

16.显示指定的表的数据和索引的碎片信息
DBCC SHOWCONTIG
17.随机数rand()/newid()
select floor(rand()*100) -- 0~99
select CEILING(rand()*100) -- 1~100
18.更新统计信息
UPDATE STATISTICS table [index]
19.列转行
declare @i char(200)
set @i=(
select distinct ','+rtrim(stock_type) from ics1.dbo.[stock nature]
where jewelry_type=1
for xml path(''))
select @i
20.ROW_NUMBER() OVER(PARTITION BY
语法形式：ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
解释：根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号

（组内连续的唯一的)
21.空格处理 select char(32),ASCII(' ')
22.字符串+null=null
23.开始停止job:
sp_stop_job 'job_name'
sp_start_job 'job_name'
24.压缩然后收缩test数据库到可用空间的30%：dbcc shrinkdatabase(test,30)
压缩然后收缩test数据库里面的一个单独文件到5MB的可用空间：dbcc shrinkfile(test_Data,5)
25.SQL Server 查询表的记录数
--one: 使用系统表.
SELECT object_name (i.id) TableName,rows as RowCnt
FROM sysindexes i
INNER JOIN sys.objects o ON (o.object_id = i.id AND o.type = 'U ')
WHERE indid < 2
ORDER BY TableName
26.临时表加上索引，查询速度会快很多
如 select * from T1 inner join (select a,b from T2) where T1.a=T2.a
改为 select a,b into #T1 from T1
CREATE CLUSTERED INDEX ix_T1_a ON #T1(a)
select * from T1 t1 inner join #T2 t2 on t1.a=t2.a
27.字符转时间
SELECT CAST(( '20130912' + ' ' + '12:34:56' ) AS DATETIME)
SELECT STUFF(STUFF(RIGHT('000000' + CAST(123456 AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
SELECT CAST(CAST(20130912 AS CHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(091245 AS VARCHAR(6)), 6), 3, 0, ':'),6, 0, ':') AS DATETIME)

28.money转财务金额（三位带逗号）
select convert(varchar(1000),cast(-123456789.120012 as money),1)
29.正则式匹配金额
^\-?[\d]+(\.[\d]{1,2})?$ -21132456.23
^\-?[\d]{1,3}(\,[\d]{3}){0,3}(\.[\d]{1,2})?$ -123,456,456,456.78
30.用xp_cmdshell删除备份的文件
declare @str varchar(100),@dir varchar(100)
set @dir='del D:\DBBackup\'
set @str=@dir+'*FULL@20140210*.bak' --*:模糊查询
--print @str
exec master..xp_cmdshell @str
31.cmd命令创建删除文件夹
md D:\20140201
mkdir D:\20140201
rd D:\20140201
rd D:\20140201 /s/q
32.sql判断文件或文件夹是否存在
exec master.dbo.xp_fileexist '路径'
33.sqlserver数据库工具
Red Gate SQL Toolbelt：数据库工具集合:如文档管理，sql prompt,数据库监控，比较...
Foglight Performance Analysis:性能指标监控