享受代码

可执行任意进制转换，2进制，8进制，32进制，36进制，进制数依赖于所能表示的字典数，如下'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' 36个字母，最大是36进制，下面关键一句是SET @alldigits = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'，用取字符串位置的方式得到结果。
IF OBJECT_ID('dbo.DecToBase') IS NOT NULLDROP FUNCTION dbo.DecToBase;GO
CREATE FUNCTION dbo.DecToBase(@val  BIGINT,@base  INT)RETURNS VARCHAR(63)ASBEGIN
    DECLARE @r AS VARCHAR(63),@alldigits AS VARCHAR(36)
    SET @alldigits = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'    SET @r = ''
    WHILE @val > 0    BEGIN
        SET @r = SUBSTRING(@alldigits,@val%@base+1,1) + @r
        SET @val = @val / @base    END    RETURN @rENDgo

SELECT a.salesstaff,a.OrderNo,a.OrderDate,a.Amount,
SUM(amount) OVER (PARTITION BY a.SalesStaff)
FROM bdOrder a(NOLOCK)
WHERE a.OrderDate >=’2011-05-01′

SELECT CAST(CAST (100.* 1/CASE(2) WHEN 0 THEN 1 ELSE 2 END AS DECIMAL(5,2)) AS VARCHAR(50)) + ‘%’

WITH tmpArray AS  (    SELECT 'A' NAME , '20,233,2544,25567,14' array
    UNION ALL    SELECT 'B','30,-23433,28'    UNION ALL
    SELECT 'C','12,10,8099,12,1200,13,12,14,10,9'    UNION ALL
    SELECT 'D','-4,-6,-45678,-2')
SELECT a.name,SUBSTRING(a.array,n,CHARINDEX(',',a.array+',',n)-n)FROM tmpArray a
JOIN dbo.fn_nums(10000) fn ON FN.n <=LEN(a.array)  AND SUBSTRING(','+a.array,n,1) = ','
ORDER BY a.name注：CHARINDEX() 可以指定开始查询的位置

将ParentID结构形式的表转换成以OrganID为形式的表
?现有一地区表mdCity，结构为：
ID? ParentID Name
1??? 0??? 安徽省???????????????????????
2??? 1??? 安庆市???????????????????????
3??? 2??? 大观区???????????????????????
4??? 2??? 扬江区???????????????????????
5??? 2??? 宜秀区???????????????????????
6??? 1??? 蚌埠市?
7??? 0??? 福建省???????????????????????
8??? 7??? 福州市???????????????????????
9??? 8??? 仓山区???????????????????????
10??? 7??? 长乐市???????????????????????
11??? 7??? 福清市???????????????????????
12??? 11??? 鼓楼区??

需转换为：???
ID? OrganID Name
1??? 01??? 安徽省???????????????????????
2??? 0101??? 安庆市???????????????????????
3??? 010101??? 大观区???????????????????????
4??? 010102??? 扬江区???????????????????????
5??? 010103??? 宜秀区???????????????????????
6??? 0102??? 蚌埠市?
7??? 02??? 福建省???????????????????????
8??? 0201??? 福州市???????????????????????
9??? 020101??? 仓山区???????????????????????
10??? 0202??? 长乐市???????????????????????
11??? 0203??? 福清市???????????????????????
12??? 020301??? 鼓楼区??

构造以下Sql语句即可：
WITH wmdCity AS (
??? SELECT a.id,a.ParentID,a.Name, convert(varchar(50),RIGHT(‘0’ + convert(varchar(2),ROW_NUMBER () OVER ( ORDER BY? a.ID)),2)) organid
??? FROM mdCity a(NOLOCK)
??? WHERE a.ParentID = 0
??? UNION ALL
??? SELECT a.id,a.ParentID,a.Name,? convert(varchar(50),mc.organid + RIGHT(‘0’ + convert(varchar(2),ROW_NUMBER () OVER ( ORDER BY a.ID)),2))? organid
??? FROM mdCity a(NOLOCK)
??? JOIN wmdCity mc ON a.ParentID = mc.id
)
SELECT * from wmdcity mc
ORDER BY mc.organid

–数据库日志文件增长的很快，每次手工收缩不能及时，用下面的方法建立一job可以定时运行

USE brm_lvjian

–设置数据库恢复模式为简单
ALTER DATABASE BRM_LVJIAN
SET RECOVERY SIMPLE

–收缩日志到1M
DBCC SHRINKFILE (‘BRM_LVJIAN_Log’, 1);

–设置数据库恢复模式为完整
ALTER DATABASE BRM_LVJIAN
SET RECOVERY FULL

WITH orderdata AS(
SELECT bo.OrderNo,bo.OrderDate,bo.Amount,
NTILE(10) OVER (ORDER BY bo.Amount) groupid
FROM bdOrder bo
WHERE bo.OrderDate >=’2011-05-24′ AND bo.OrderDate < '2011-05-25' ) --分为10个组，每组上限与下限，及个数 SELECT a.groupid,MIN(amount),MAX(amount),COUNT(1) FROM orderdata a GROUP BY a.groupid 注：当组数无法整除行数时，前面的r个组比其它组多一行。如有11行，分3个组，那么前两个组分别有4个，后一个组有1个。

查询表空间及行数

SELECT OBJECT_NAME(id) AS TableName ,
rowcnt AS ROWS,
reserved * 8.0 AS Reserved_KB,
dpages * 8.0 AS USED ,
(used – dpages)* 8.0 AS index_size_KB,
( reserved – used ) * 8.0 AS Unused
FROM sysindexes
WHERE indid = 1
–AND OBJECT_NAME(id) = ‘users’
ORDER BY reserved DESC

或者：EXEC sp_spaceused ‘bdorder’

　非(P 且 Q)＝(非 P)或(非 Q) 　　非(P 或 Q)＝(非 P)且(非 Q)

在引用链接服务器时
SELECT * FROM SERVER.brm_lvjian.dbo.frmUser
这种引用将分散到各个存储过程中，若对链接服务器、数据库、表有任何修改，都有逐一修改各分散的地方，维护不方便。
此时，可利用视图做一个封装，那么在以后改变时，只需改变此视图即可。
CREATE VIEW [dbo].[DB201_frmUser]
AS
SELECT * FROM SERVER.brm_lvjian.dbo.frmUser

使用：
SELECT * FROM DB201_frmUser

或者使用同义词
CREATE SYNONYM brm_lvjian_frmUser
FOR SERVER.brm_lvjian.dbo.frmuser

SELECT * FROM brm_lvjian_frmUser

DROP SYNONYM brm_lvjian_frmUser

同义词作用于：表、视图、存储过程、函数、程序集上。

在用@sql拼语句时，对于字符串变量，要传大量的引号，因为要用””表示一个引号
如：.
DECLARE @tname VARCHAR(10)
DECLARE @sql1 VARCHAR(MAX),@sql2 VARCHAR(MAX)
SET @tname = ‘皮松谊’
SET @sql1 = ‘select * from frmuser where username = ‘ + ”” + @tname + ””
EXEC ( @sql1 )

其实可用sql server提供的内置函数quotename解决
SET @sql1 = ‘select * from frmuser where username = ‘ + quotename(@tname,””)
EXEC ( @sql1 )

注：QUOTENAME ( ‘character_string’ [ , ‘quote_character’ ] )
‘quote_character’:
用作分隔符的单字符字符串。可以是单引号 (‘)、左方括号或右方括号 ([ ]) 或者英文双引号 (“)。如果未指定 quote_character，则使用方括号。 方括号可用于表名

常用的可通过：sqlserver提供的配置工具来完成，或者使用sp_configure

sp_configure [ [ @configname = ] ‘option_name’ [ , [ @configvalue = ] ‘value’ ] ]
当不传值时，返回所有可配置的服务器选项及最小值，最大值，当前配置值
如：EXEC sp_configure ‘show advanced option’, ‘1’;

sys.dm_db_index_usage_stats
动态管理视图 sys.dm_db_index_usage_stats 该视图返回不同类型索引操作的计数以及 上次执行每种操作的时间。
只要启动 SQL Server (MSSQLSERVER) 服务，计数器就初始化为空。而且，当分离或关闭数据库时（例如， 由于 AUTO_CLOSE 设置为 ON），便会删除与该数据库关联的所有行。
这个视图最关键的就是最关键的就是这4个字段：
user_seeks 通过用户查询执行的搜索次数,就是利用聚集索引的次数。
user_scans 通过用户查询执行的扫描次数，就是没有利用任何索引的次数，逐行扫描最慢的那种。
user_lookups 通过用户查询执行的查找次数，就是利用非聚集索引的次数。
user_updates 通过用户查询执行的更新次数，就是更新前找到这条数据时利用的索引的次数 。因为更新是先查询到需要更新的数据，然后执行更新命令所以更新的时候这个user_updates字段和 user_seeks、user_lookups、user_scans字段中的一个都会加一。

--查询从未被使用的索引
SELECT t.name,ix.name,ddius.user_seeks,ddius.user_scans,ddius.user_lookups,ddius.user_updates
FROM sys.dm_db_index_usage_stats ddius
JOIN sys.tables t ON ddius.[object_id] = t.[object_id]
JOIN sys.indexes ix ON ix.[object_id] = ddius.[object_id] AND ix.index_id = ddius.index_id
WHERE ddius.database_id = DB_ID('brm_lvjian')
and last_user_seek is null
     and last_user_scan is null
     and last_user_lookup is null
     and last_user_update is not null



索引与页关系
--查询索引使用的页数，每页是8K,页数乘以8，即为空间使用数
SELECT t.name,i.name,sp.in_row_data_page_count,sp.in_row_used_page_count,sp.in_row_reserved_page_count
FROM sys.tables t
JOIN sys.indexes i ON t.[object_id] = i.[object_id]
JOIN sys.dm_db_partition_stats sp ON sp.[object_id] = i.[object_id] AND sp.index_id = i.index_id
ORDER BY t.name,i.name

查询表占用空间，涉及的索引使用空间
SELECT a3.name AS [Schema 名称],
	a2.name AS [表名称],
	a1.rows as 记录条数,
	(a1.reserved + ISNULL(a4.reserved,0))* 8 AS [保留空间(K)],
	a1.data * 8 AS [数据使用空间(k)],
	(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data 
	THEN (a1.used + ISNULL(a4.used,0)) - a1.data 
	ELSE 0 END) * 8 AS [索引使用空间(k)],
	(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used 
	THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used 
	ELSE 0 END) * 8 AS [未用空间(k)],
	a1.data * 8*1024/(CASE WHEN a1.Rows=0 THEN 1 ELSE a1.Rows END) 平均每条记录长度
	FROM
		(
		SELECT
		ps.object_id,
		SUM (
		CASE
		WHEN (ps.index_id < 2) THEN row_count
		ELSE 0
		END
		) AS [rows],
		SUM (ps.reserved_page_count) AS reserved,
		SUM (
		CASE
		WHEN (ps.index_id < 2) THEN 
		(ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
		ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
		END
		) AS data,
		SUM (ps.used_page_count) AS used
		FROM sys.dm_db_partition_stats ps
		GROUP BY ps.object_id) AS a1
		LEFT OUTER JOIN
			(
			SELECT
			it.parent_id,
			SUM(ps.reserved_page_count) AS reserved,
			SUM(ps.used_page_count) AS used
			FROM sys.dm_db_partition_stats ps
			INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
			WHERE it.internal_type IN (202,204)
			GROUP BY it.parent_id
			) AS a4 ON (a4.parent_id = a1.object_id)
	INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
	INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
	WHERE a2.type <> N'S' and a2.type <> N'IT'
	ORDER BY [保留空间(K)] DESC




--打开3604，信息发送到控制台
DBCC TRACEON(3604)

--查看表索引对应的页数明细
DBCC IND ('brm_lvjian_0817', 'frmuser', -1);

--查看页上的数据信息
DBCC PAGE (brm_lvjian_0817, 1, 409259, 3);


注：
DBCC PAGE
(
    ['database name'|database id], -- can be the actual name or id of the database
    file number, -- the file number where the page is found
    page number, -- the page number within the file
    print option = [0|1|2|3] -- display option; each option provides differing levels of information
)

DBCC IND
(
    ['database name'|database id], -- the database to use
    table name, -- the table name to list results
    index id, -- an index_id from sys.indexes; -1 shows all indexes and IAMs, -2 just show IAMs
)

参考：http://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-Using-DBCC-PAGE-and-DBCC-IND-to-find-out-if-page-splits-ever-roll-back.aspx

 索引碎片：1,当索引包含的页中的逻辑顺序与数据文件中的物理排序不匹配时，就存在碎片，碎片非常多的索引会降低查询的性能，导致应用程序响应缓慢，可通过重新组织索引或重新生成索引来修复索引碎片。

2,当索引页不在逻辑顺序上时就会产生外部碎片。索引创建时，索引键按照逻辑顺 序放在一组索引页上。当新数据插入索引时，新的键可能放在存在的键之间。为 了让新的键按照正确的顺序插入， 可能会创建新的索引页来存储需要移动的那些 存在的键。这些新的索引页通常物理上不会和那些被移动的键原来所在的页相 邻。创建新页的过程会引起索引页偏离逻辑顺序。

 3,在有特定搜索或者返回无序结果集的查询的情况下， 偏离顺序的索引页不会引起 问题。 对于返回有序结果集的查询， 搜索那些无序的索引页需要进行额外的处理。造成性能低下。
即：在初建时索引是有序的，但不断的update,insert,delete操作使得索引页变得局部无序，但要求返回有序的结果集时，不得不浪费额外时间进行排序。而索引维护的过程就是将排序归为有序的过程


 索引状况检查:dm_db_index_physical_stats
 当avg_fragmentation_in_percent碎片率 <5%时，是正常级别
 当avg_fragmentation_in_percent碎片率 >=5% AND <=30%时，是轻度级别，需要重新组织索引
 当avg_fragmentation_in_percent碎片率 >30%时，是严重级别，需要重建索引
 alert index 索引名称 ON 架构名称.表名 reorganize/rebulid partition = 分区号
 sys.objects:存储表、视图、存储过程

查看：
SELECT *
FROM SYS.dm_db_index_physical_stats(DB_ID(), object_id('bmdcustomer'),NULL,NULL,'LIMITED') DDIPS

1，索引状态检查与维护
SELECT s.name schema_name,o.name OBJECT_name,ix.name INDEX_name,DDIPS.avg_fragmentation_in_percent 碎片率,
	CASE WHEN avg_fragmentation_in_percent > 30 THEN '严重碎片级别，索引需要重建'
	     WHEN avg_fragmentation_in_percent >= 5 AND avg_fragmentation_in_percent < 30 THEN '轻度碎片级别，索引需要重新组织' 
		 ELSE '正常状态' END 提示
	FROM SYS.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') DDIPS
	JOIN SYS.objects o ON DDIPS.[object_id] = O.[object_id] --得到表名
	JOIN SYS.schemas s ON S.[schema_id] = O.[schema_id] --得到表的架构名
	JOIN SYS.indexes IX ON DDIPS.[object_id] = IX.[object_id] AND DDIPS.index_id = IX.index_id --得到索引名称
WHERE ddips.[object_id] = object_id('bdcustomerallocate') AND s.[schema_id] = SCHEMA_ID('dbo')  

/*
* 1,重新组织索引：alter index IX_bdCustomerAllocate ON dbo.bdCustomerAllocate reorganize
* 2,重建索引：alter index PK_bdCustomerAllocate ON dbo.bdCustomerAllocate rebulid
* */

2，索引状态批量维护
/*索引维护:检查数据库中所有索引，修复有问题的索引
* alert index 索引名称 ON 架构名称.表名 reorganize/rebulid partition = 分区号
* sys.partitions:数据库中所有表和索引的每个分区在表中各对应一行
* sys.objects:存储表、视图、存储过程
* 
* */
DECLARE cur_dx CURSOR FOR

WITH PT AS(
SELECT p.[object_id],p.index_id, COUNT(1) partition_count
FROM SYS.partitions p
GROUP BY p.[object_id],p.index_id),

DIX AS(
	SELECT DDIPS.[object_id],DDIPS.index_id,DDIPS.partition_number,DDIPS.avg_fragmentation_in_percent,
	o.name OBJECT_name,s.name schema_name,ix.name INDEX_name,pt.partition_count,
	CASE WHEN avg_fragmentation_in_percent > 30 THEN '严重碎片级别，索引需要重建'
	     WHEN avg_fragmentation_in_percent >= 5 AND avg_fragmentation_in_percent < 30 THEN '轻度碎片级别，索引需要重新组织' END warning
	
	FROM SYS.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') DDIPS
	JOIN SYS.objects o ON DDIPS.[object_id] = O.[object_id] --得到表名
	JOIN SYS.schemas s ON S.[schema_id] = O.[schema_id] --得到表的架构名
	JOIN SYS.indexes IX ON DDIPS.[object_id] = IX.[object_id] AND DDIPS.index_id = IX.index_id --得到索引名称
	JOIN PT ON DDIPS.[object_id] = PT.object_id AND DDIPS.index_id = pt.index_id --得到分区号
	WHERE ddips.avg_fragmentation_in_percent > 5 AND DDIPS.index_id > 0	
	AND ddips.[object_id]=OBJECT_ID('bdcustomerallocate')
),
DIXSQL AS(
	SELECT ' ALTER INDEX ' + QUOTENAME(INDEX_NAME)
	+ ' ON ' + QUOTENAME(schema_name) + '.' + QUOTENAME(OBJECT_NAME)
	+ CASE WHEN avg_fragmentation_in_percent < 30 THEN ' REORGANIZE '
	  ELSE ' REBUILD ' END 
	+ CASE WHEN partition_count > 1
	THEN ' PARTITION =  ' + CONVERT(NVARCHAR(20),partition_number)
	ELSE '' END  SQL
	FROM DIX
) 
SELECT * FROM DIXSQL

DECLARE @sql NVARCHAR(max)
OPEN cur_dx
FETCH cur_dx INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC sp_executesql @sql
	RAISERROR('Executed:%s',10,1,@sql) WITH NOWAIT
	FETCH cur_dx INTO @sql	
END 
CLOSE cur_dx
DEALLOCATE cur_dx

--ALTER INDEX [PK_bdCustomerAllocate] ON [dbo].[bdCustomerAllocate]   REBUILD 

TSQL一般是对列操作，如聚合等，现要求按行进行聚合，如求出每行中最大的字段
方法：将每行数据转成一列数据集进行操作即可
如：

SELECT f.account,f.username,
( SELECT MAX(c1) FROM ( SELECT f.account c1 UNION ALL SELECT f.UserName )M )cmax
FROM frmuser f

rollup:汇总
在GROUP后加WITH ROLLUP来实现总计与小计，它会对每次分组的结果进行ROLLUP操作，产生汇总的结果，显示在行下。
在显示的时候可用GROUPING来判断当天是数据行还是汇总行，GROUPING为1时是汇总行，为0为数据行。
1，当GROUP只有一个字段时，ROLLUP在最后一行会显示总计
2，当GROUP有多个字段时，在上一个字段改变时显示汇总结果
即：ROLLUP会在上个字段改变时显示汇总结果，当GROUP按一个字段分组，因此字段上个字段不存在，故会在最后显示汇总结果。
如：
SELECT
CASE WHEN GROUPING (convert(varchar(10),a.OrderDate,120)) = 0 THEN convert(varchar(10),a.OrderDate,120)
WHEN GROUPING(convert(varchar(10),a.OrderDate,120)) = 1 THEN ‘总计:’ END,
CASE WHEN GROUPING(a.DepartmentID) = 0 THEN convert(varchar(10),a.DepartmentID)
WHEN GROUPING(convert(varchar(10),a.OrderDate,120)) = 0 AND GROUPING(a.DepartmentID) = 1 THEN ‘小计:’ end,
SUM(amount)
FROM bdOrder a(NOLOCK)
WHERE a.OrderDate>=’2011-05-01′
GROUP BY convert(varchar(10),a.OrderDate,120),a.DepartmentID,a.DepartmentID
WITH ROLLUP
区分总计与小计：按日期，部门ID分组，当日期所在行通过GROUPING判断出是汇总行时，则是总计。
当日期所在行为数据行，而部门ID为汇总时，为小计。
3,若不想要小计，只保留总计与其它数据，用HAVING GROUPING(firstColoum) = 1 OR 其它分组列数据IS NOT NULL