2011年5月 的存档
2011五月31

查询表空间及行数

查询表空间及行数

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’

2011五月27

逻辑等价公式-狄摩根定律

sql server 评论关闭

 非(P 且 Q)=(非 P)或(非 Q)   非(P 或 Q)=(非 P)且(非 Q)

2011五月27

分布式数据库表的封装-VIEW

sql server 评论关闭

在引用链接服务器时
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

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

2011五月17

sql字符串拼接引号问题

sql server 评论关闭

在用@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,则使用方括号。 方括号可用于表名

2011五月17

配置sqlserver-sp_configure

sql server 评论关闭

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

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

2011五月16

索引维护2-索引状态查看:sys.dm_db_index_usage_stats

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
2011五月15

索引维护1-索引碎片整理:.dm_db_index_physical_stats

 索引碎片: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 
2011五月14

对一行数据的字段进行操作如求每行的最大值

sql server 评论关闭

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

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

2011五月14

总计与小计–WITH ROLLUP

sql server 评论关闭

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

2011五月7

可视化的数据结构和算法[转载]

C#语法 评论关闭

基础

索引

  • 排序

    • Comparison Sorting 比较式排序
      • Bubble Sort 冒泡排序
      • Selection Sort 选择排序
      • Insertion Sort 插入排序
      • Shell Sort 希尔排序
      • Merge Sort 归并排序
      • Quck Sort 快速排序
    • Bucket Sort 桶排序
    • Counting Sort 计数排序
    • Radix Sort 基数排序

    堆数据结构

    图 算法

    动态编程

    • 计算 Fibonacci 数 ( java 版本演示)

    其它…

    (全文完)