All posts by 龙生

SQL Server数据库维度表和事实表概述

SQL Server数据库维度表和事实表概述: 一、事实表每个数据仓库都包含一个或者多个事实数据表。事实数据表可能包含业务销售数据，如现金登记事务所产生的数据，事实数据表通常包含大量的行。事实数据表的主要特点是包含数字数据(事实)，并且这些数字信息可以汇总，以提供有关单位作为历史的数据，每个事实数据表包含一个由多个部分组成的索引，该索引包含作为外键的相关性纬度表的主键，而维度表包含事实记录的特性。事实数据表不应该包含描述性的信息，也不应该包含除数字度量字段及使事实与纬度表中对应项的相关索引字段之外的任何数据。包含在事实数据表中的“度量值”有两中：一种是可以累计的度量值，另一种是非累计的度量值。最有用的度量值是可累计的度量值，其累计起来的数字是非常有意义的。用户可以通过累计度量值获得汇总信息，例如。可以汇总具体时间段内一组商店的特定商品的销售情况。非累计的度量值也可以用于事实数据表，单汇总结果一般是没有意义的，例如，在一座大厦的不同位置测量温度时，如果将大厦中所有不同位置的温度累加是没有意义的，但是求平均值是有意义的。一般来说，一个事实数据表都要和一个或多个纬度表相关联，用户在利用事实数据表创建多维数据集时，可以使用一个或多个维度表。二、维度表维度表可以看作是用户来分析数据的窗口，纬度表中包含事实数据表中事实记录的特性，有些特性提供描述性信息，有些特性指定如何汇总事实数据表数据，以便为分析者提供有用的信息，维度表包含帮助汇总数据的特性的层次结构。例如，包含产品信息的维度表通常包含将产品分为食品、饮料、非消费品等若干类的层次结构，这些产品中的每一类进一步多次细分，直到各产品达到最低级别。在维度表中，每个表都包含独立于其他维度表的事实特性，例如，客户维度表包含有关客户的数据。维度表中的列字段可以将信息分为不同层次的结构级。三、结论: 1、事实表就是你要关注的内容; 2、维度表就是你观察该事务的角度，是从哪个角度去观察这个内容的。例如，某地区商品的销量，是从地区这个角度观察商品销量的。事实表就是销量表，维度表就是地区表。维度表和事实表在SQL Server数据库的操作中是很常用的两种表，学会了这两种表的应用，在操作SQL Server数据库时就会容易很多，初学者也可以首先学习这两种表的操作使用技巧。

龙生 23 Mar 2011

View Details

在SQL Server数据库中拆分字符串函数

SQL Server数据库中拆分字符串函数的具体方法： CREATE FUNCTION uf_StrSplit '1.1.2.50','.'(@origStr varchar(7000), --待拆分的字符串@markStr varchar(100)) --拆分标记，如','RETURNS @splittable table(　　str_id varchar(4000) NOT NULL, --编号ID　　string varchar(2000) NOT NULL --拆分后的字符串)ASBEGINdeclare @strlen int,@postion int,@start int,@sublen int,@TEMPstr varchar(200),@TEMPid intSELECT @strlen=LEN(@origStr),@start=1,@sublen=0,@postion=1,@TEMPstr=",@TEMPid=0if(RIGHT(@origStr,1)<>@markStr )beginset @origStr = @origStr + @markStrendWHILE((@postion<=@strlen) and (@postion !=0))BEGINIF(CHARINDEX(@markStr,@origStr,@postion)!=0)BEGINSET @sublen=CHARINDEX(@markStr,@origStr,@postion)-@postion;ENDELSEBEGINSET @sublen=@strlen-@postion+1;ENDIF(@postion<=@strlen)BEGINSET @TEMPid=@TEMPid+1;SET @TEMPstr=SUBSTRING(@origStr,@postion,@sublen);INSERT INTO @splittable(str_id,string)values(@TEMPid,@TEMPstr)IF(CHARINDEX(@markStr,@origStr,@postion)!=0)BEGINSET @postion=CHARINDEX(@markStr,@origStr,@postion)+1ENDELSEBEGINSET @postion=@postion+1ENDENDENDRETURNEND 例如：select * from uf_StrSplit('1，1，2，50′,’，') 输出结果： str_id string1 12 13 24 50 上文中涉及到很多的字符，对于一些初学者来说，可能是比较难理解，但是这个知识点确实是经常会用到的，而且相当实用，希望大家好好学习，争取能熟练的掌握。

龙生 23 Mar 2011

View Details

SQL Server数据库多种方式查找重复记录

SQL Server数据库多种方式查找重复记录: 示例：表stuinfo，有三个字段recno(自增)，stuid，stuname 建该表的Sql语句如下： CREATE TABLE [StuInfo] ([recno] [int] IDENTITY (1， 1) NOT NULL ，[stuid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ，[stuname] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL) ON [PRIMARY]GO 1.查某一列(或多列)的重复值(只可以查出重复记录的值，不能查出整个记录的信息) 例如:查找stuid，stuname重复的记录 select stuid，stuname from stuinfogroup by stuid，stunamehaving(count(*))>1 2.查某一列有重复值的记录(此方法查出的是所有重复的记录，如果有两条记录重复的，就查出两条) 例如:查找stuid重复的记录 select * from stuinfowhere stuid in (select stuid from stuinfogroup by stuidhaving(count(*))>1) 3.查某一列有重复值的记录(只显示多余的记录，也就是说如果有三条记录重复的，就显示两条) 前提：需有一个不重复的列，此示例为recno。例如:查找stuid重复的记录 select * from stuinfo s1where recno not in (select max(recno) from stuinfo s2where s1.stuid=s2.stuid 关于SQL Server数据库中查询重复记录的方法就为大家介绍到这，这里介绍的方法可能也是不够全面的，以后如果有了更新的方法，我会及时与大家继续分享，希望对大家能有所帮助。

龙生 23 Mar 2011

View Details

提高SQL执行效率的几点建议

提高SQL执行效率的几点建议: 尽量不要在where中包含子查询：关于时间的查询，尽量不要写成：where to_char(dif_date,’yyyy-mm-dd')=to_char('2007-07-01′,’yyyy-mm-dd'); 在过滤条件中，可以过滤掉最大数量记录的条件必须放在where子句的末尾：FROM子句中写在最后的表(基础表，driving table)将被最先处理，在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。如果有三个以上的连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指那个被其他表所引用的表; 采用绑定变量在WHERE中尽量不要使用OR 用EXISTS替代IN、用NOT EXISTS替代NOT IN; 避免在索引列上使用计算：WHERE SAL*12>25000; 用IN来替代OR： WHERE LOC_ID=10 OR LOC_ID=15 OR LOC_ID=20 避免在索引列上使用IS NULL和IS NOT NULL; 总是使用索引的第一个列; 用UNION-ALL替代UNION; 避免改变索引列的类型：SELECT…FROM EMP WHERE EMPNO=’123’，由于隐式数据类型转换，to_char(EMPNO)=’123’，因此，将不采用索引，一般在采用字符串拼凑动态SQL语句出现; '!=' 将不使用索引; 优化GROUP BY; 避免带有LIKE参数的通配符，LIKE '4YE%’使用索引，但LIKE '%YE’不使用索引避免使用困难的正规表达式，例如select * from customer where zipcode like "98___"，即便在zipcode上建立了索引，在这种情况下也还是采用顺扫描的方式。如果把语句改成select * from customer where zipcode>"98000"，在执行查询时就会利用索引来查询，显然会大大提高速度; 尽量明确的完成SQL语句，尽量少让数据库工作。比如写SELECT语句时，需要把查询的字段明确指出表名。尽量不要使用SELECT *语句。组织SQL语句的时候，尽量按照数据库的习惯进行组织。相信每个人都想提高SQL执行效率，那么大家不妨试试这几条建议，肯定不会让大家失望的，希望这些小建议能够对大家有所帮助。

龙生 23 Mar 2011

View Details

浅谈SQL Server2005的几种分页方法

SQL Server分页查询是我们经常会用到的功能，其实现方法也有很多，本文的几种分页方法摘自《SQL Server2005性能调优》一书。希望对您学习SQL Server分页查询方面能有所帮助。用以下脚本生成测试数据：

<ol class="dp-sql"><li class="alt">CREATE TABLE TRANS_TABLE(  </li><li>        MYID   INT IDENTITY(1,1) NOT NULL PRIMARY KEY,  </li><li class="alt">        MYDESC VARCHAR(10),  </li><li>        MYDATE DATETIME,  </li><li class="alt">        MYGROUPID INT)  </li><li>DECLARE @I INT </li><li class="alt">SET @I = 0WHILE @I < 1000000  </li><li>BEGIN </li><li class="alt">    INSERT INTO TRANS_TABLE  </li><li>    SELECT CHAR(ASCII('A') - 2 + (2 * (1 + ABS(CHECKSUM(NEWID())) % 26))),  </li><li class="alt">                 DATEADD(day, ABS(CHECKSUM(NEWID())) % 365, '01/01/2007'),  </li><li>                 (ABS(CHECKSUM(NEWID())) % 10)  </li><li class="alt">    SET @I = @I + 1  </li><li>END </li><li class="alt">CREATE NONCLUSTERED INDEX IX_TRANS_TABLE_MYDATE  </li><li>ON TRANS_TABLE(MYDATE)  </li><li class="alt">CREATE NONCLUSTERED INDEX IX_TRANS_TABLE_MYGROUPID  </li><li>ON TRANS_TABLE(MYGROUPID)  </li><li class="alt"> </li></ol>

1、基于CTE分页 1）用row_number()排名函数，派生表的方式分页

<ol class="dp-sql"><li class="alt">DECLARE @START_ID int, @START_ROW int, @MAX_ROWS int </li><li>SELECT @START_ROW = 1, @MAX_ROWS = 25  </li><li class="alt">select *  </li><li>  from ( select p.*, rownum rnum  </li><li class="alt">         FROM (  </li><li>            SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, *  </li><li class="alt">            FROM TRANS_TABLE (NOLOCK)  </li><li>                ) p  </li><li class="alt">          where rownum <= @START_ROW + @MAX_ROWS - 1  </li><li>       )  </li><li class="alt"> z where rnum >= @START_ROW  </li><li> </li></ol>

2）用CTE方式取代派生表

<ol class="dp-sql"><li class="alt">DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int </li><li>SELECT @START_ROW = 1, @MAX_ROWS = 25;  </li><li class="alt">WITH PAGED AS (  </li><li>            SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum,     *  </li><li class="alt">            FROM TRANS_TABLE (NOLOCK)  </li><li>            )  </li><li class="alt">SELECT *  </li><li>FROM PAGEDWHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS-1 </li></ol>

3）也是CTE方法，但是根据测试数据显示这种性能比前两种都好

<ol class="dp-sql"><li class="alt">DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int </li><li>SELECT @START_ROW = 1, @MAX_ROWS = 25;  </li><li class="alt">WITH PAGED AS (  </li><li>            SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, MYID  </li><li class="alt">            FROM TRANS_TABLE (NOLOCK)  </li><li>            )  </li><li class="alt">SELECT TT.*  </li><li>FROM PAGED PGD  </li><li class="alt">INNER JOIN TRANS_TABLE TT  </li><li>ON PGD.MYID = TT.MYID  </li><li class="alt">WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1  </li><li>ORDER BY MyDate, MYID  </li></ol>

2、基于ROW_COUNT的分页

<ol class="dp-sql"><li class="alt">DECLARE     @START_ID int, @START_ROW int, @MAX_ROWS int,  </li><li>         @START_DATETIME DATETIME, @TOT_ROW_CNT INT </li><li class="alt">SELECT @START_ROW = 1, @MAX_ROWS = 25  </li><li>-- Get the first row for the page  </li><li class="alt">SET ROWCOUNT @START_ROW  </li><li>SELECT @START_ID = MYID, @START_DATETIME = MYDATE FROM TRANS_TABLE (NOLOCK)  </li><li class="alt">        ORDER BY MYDATE, MYID  </li><li>-- Now, set the row count to MaximumRows and get  </li><li class="alt">-- all records >= @first_idSET ROWCOUNT @MAX_ROWS  </li><li>SELECT *  </li><li class="alt">FROM TRANS_TABLE (NOLOCK)  </li><li>WHERE MYID >= @START_ROW  </li><li class="alt">AND MYDATE >= @START_DATETIME  </li><li>ORDER BY MYDATE, MYID  </li><li class="alt">SET ROWCOUNT 0  </li></ol>

3、 TOP @X分页 SQL Server 2005中可以把返回行数做为参数传给top语句。

<ol class="dp-sql"><li class="alt">DECLARE @START_ID int, @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT INT, @START_DESC VARCHAR(10)  </li><li>SELECT @START_ROW = 1, @MAX_ROWS = 25  </li><li class="alt">-- Get the first row for the page  </li><li>SELECT TOP(@START_ROW) @START_ID = MYID, @START_DESC = MYDESC FROM TRANS_TABLE (NOLOCK)  </li><li class="alt">    ORDER BY MYDESC, MYID  </li><li>SELECT TOP(@MAX_ROWS) *  </li><li class="alt">FROM TRANS_TABLE (NOLOCK)  </li><li>WHERE MYID >= @START_ROW  </li><li class="alt">AND MYDESC >= @START_DESC  </li><li>ORDER BY MYDESC, MYID  </li><li class="alt"> </li></ol>

4、 Temp表分页

<ol class="dp-sql"><li class="alt">DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int </li><li>SELECT @START_ROW = 1, @MAX_ROWS = 25;  </li><li class="alt">    SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum,  </li><li>         MYID  </li><li class="alt">    into #TEMP </li><li>    FROM TRANS_TABLE (NOLOCK)  </li><li class="alt">SELECT TT.*  </li><li>FROM TRANS_TABLE (NOLOCK) TT  </li><li class="alt">INNER JOIN #TEMP TON TT.MYID = T.MYID  </li><li>WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1  </li><li class="alt">DROP TABLE #TEMP </li><li> </li></ol>

以上便是这次为您介绍的 SQL Server 2005中几种分页方法，希望对您学习SQL Server分页查询方面能有所帮助。原文链接：http://www.cnblogs.com/qiuwuyu/archive/2011/03/21/1989870.html

龙生 23 Mar 2011

View Details

用SQL查询日月周季

怎么用SQL查询昨天、今天、明天和本周的记录？又怎么用SQL查询一天，三天，一周，一个月，更长一些——一个季度的记录呢？本文中给出了一些方法。 SQL查询今天的记录：

1	<ol class="dp-sql"><li class="alt"><span><span>datediff(</span><span class="func"><font color="#ff1493">day</font></span><span>,[Datetime],getdate())=0  把Datetime换为你的相应字段； </span></span></li></ol>

SQL查询昨天的记录：

1	<ol class="dp-sql"><li class="alt"><span><span>datediff(</span><span class="func"><font color="#ff1493">day</font></span><span>,[Datetime],getdate())=1  把Datetime换为你的相应字段，getdate()-Datetime即为时间差。 </span></span></li></ol>

本月记录：

<ol class="dp-sql"><li class="alt">SELECT * FROM 表 WHERE datediff(month,[dateadd],getdate())=0 </li></ol>

本周记录：

<ol class="dp-sql"><li class="alt">SELECT * FROM 表 WHERE datediff(week,[dateadd],getdate())=0  </li></ol>

本日记录：

<ol class="dp-sql"><li class="alt">SELECT * FROM 表 WHERE datediff(day,[dateadd],getdate())=0 </li></ol>

一天

<ol class="dp-sql"><li class="alt">select * from T_news where datediff(day,addtime,getdate())=0   </li></ol>

三天

一周

<ol class="dp-sql"><li class="alt">select * from T_news WHERE (DATEPART(wk, addtime) = DATEPART(wk, GETDATE())) AND (DATEPART(yy, addtime) = DATEPART(yy, GETDATE()))   </li></ol>

注意：此时不能用 datediff 差值为7,因为，datediff只表示间隔数一月

<ol class="dp-sql"><li class="alt">select * from T_news WHERE (DATEPART(yy, addtime) = DATEPART(yy, GETDATE())) AND (DATEPART(mm, addtime) = DATEPART(mm, GETDATE()))   </li></ol>

一季度

<ol class="dp-sql"><li class="alt">select * from T_news where DATEPART(qq, addtime) = DATEPART(qq, GETDATE()) and DATEPART(yy, addtime) = DATEPART(yy, GETDATE()) </li></ol>

希望以上这些方法，能给大家一些启示。

龙生 23 Mar 2011

View Details

SQL Server索引结构及其使用

一、深入浅出理解索引结构实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别：其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。二、何时使用聚集索引或非聚集索引下面的表总结了何时使用聚集索引或非聚集索引（很重要）：事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。三、结合实际，谈索引使用的误区理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区，以便于大家掌握索引建立的方法。 1、主键就是聚集索引这种想法笔者认为是极端错误的，是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时，如果我们将这个列设为主键，SQL SERVER会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在数据库中按照ID进行物理排序，但笔者认为这样做意义不大。显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则；当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。在办公自动化系统中，无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况，但如果您的系统已建立了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大多数的用户1个月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已。事实上，我们完全可以让用户打开系统首页时，数据库仅仅查询这个用户近3个月来未阅览的文件，通过“日期”这个字段来限制表扫描，提高查询速度。如果您的办公自动化系统已经建立的2年，那么您的首页显示速度理论上将是原来速度8倍，甚至更快。在这里之所以提到“理论上”三字，是因为如果您的聚集索引还是盲目地建在ID这个主键上时，您的查询速度是没有这么高的，即使您在“日期”这个字段上建立的索引（非聚合索引）。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现（3个月内的数据为25万条）：（1）仅在主键上建立聚集索引，并且不划分时间段：

1	<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">Select</font></strong></span><span> gid,fariqi,neibuyonghu,title </span><span class="keyword"><strong><font color="#006699">from</font></strong></span><span> tgongwen </span></span></li></ol>

用时：128470毫秒（即：128秒）（2）在主键上建立聚集索引，在fariq上建立非聚集索引：

用时：53763毫秒（54秒）（3）将聚合索引建立在日期列（fariqi）上：

用时：2423毫秒（2秒）虽然每条语句提取出来的都是25万条数据，各种情况的差异却是巨大的，特别是将聚集索引建立在日期列时的差异。事实上，如果您的数据库真的有1000万容量的话，把主键建立在ID列上，就像以上的第1、2种情况，在网页上的表现就是超时，根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。得出以上速度的方法是：在各个select语句前加：

1	<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">declare</font></strong></span><span> @d datetime  </span></span></li><li><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> @d=getdate() </span></li></ol>

并在select语句后加：

1	<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) </span></span></li></ol>

2、只要建立索引就能显著提高查询速度事实上，我们可以发现上面的例子中，第2、3条语句完全相同，且建立索引的字段也相同；不同的仅是前者在fariqi字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非是在任何字段上简单地建立索引就能提高查询速度。从建表的语句中，我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中，我们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求的：“既不能绝大多数都相同，又不能只有极少数相同”的规则。由此看来，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的 3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度上面已经谈到：在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要，我们可以把他们合并起来，建立一个复合索引（compound index）。很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会减慢吗？带着这个问题，我们来看一下以下的查询速度（结果集都是25万条数据）：（日期列fariqi首先排在复合聚集索引的起始列，用户名neibuyonghu排在后列）：

查询速度：2513毫秒

<ol class="dp-sql"><li class="alt">select gid,fariqi,neibuyonghu,title from Tgongwen  </li><li>where fariqi>''2004-5-5'' and neibuyonghu=''办公室'' </li></ol>

查询速度：2516毫秒

查询速度：60280毫秒从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。四、其他书上没有的索引使用经验总结 1、用聚合索引比用不是聚合索引的主键速度快下面是实例语句：（都是提取25万条数据）

使用时间：3326毫秒

使用时间：4470毫秒这里，用聚合索引比用不是聚合索引的主键速度快了近1/4。 2、用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下

用时：12936

用时：18843 这里，用聚合索引比用一般的主键作order by时，速度快了3/10。事实上，如果数据量很小的话，用聚集索引作为排序列要比使用非聚集索引速度快得明显的多；而数据量如果很大的话，如10万以上，则二者的速度差别不明显。 3、使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个：

用时：6343毫秒（提取100万条）

用时：3170毫秒（提取50万条）

用时：3326毫秒（和上句的结果一模一样。如果采集的数量一样，那么用大于号和等于号是一样的）

<ol class="dp-sql"><li class="alt">select gid,fariqi,neibuyonghu,reader,title from Tgongwen  </li><li>where fariqi>''2004-1-1'' and fariqi<''2004-6-6'' </li></ol>

用时：3280毫秒 4、日期列不会因为有分秒的输入而减慢查询速度下面的例子中，共有100万条数据，2004年1月1日以后的数据有50万条，但只有两个不同的日期，日期精确到日；之前有数据50万条，有5000个不同的日期，日期精确到秒。

<ol class="dp-sql"><li class="alt">select gid,fariqi,neibuyonghu,reader,title from Tgongwen  </li><li>where fariqi>''2004-1-1'' order by fariqi </li></ol>

用时：6390毫秒

<ol class="dp-sql"><li class="alt">select gid,fariqi,neibuyonghu,reader,title from Tgongwen  </li><li>where fariqi<''2004-1-1'' order by fariqi </li></ol>

用时：6453毫秒五、其他注意事项 “水可载舟，亦可覆舟”，索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。所以说，我们要建立一个“适当”的索引体系，特别是对聚合索引的创建，更应精益求精，以使您的数据库能得到高性能的发挥。当然，在实践中，作为一个尽职的数据库管理员，您还要多测试一些方案，找出哪种方案效率最高、最为有效。原文出处：http://www.cnblogs.com/acafaxy/archive/2011/03/17/1987431.html

龙生 23 Mar 2011

View Details

MS Sql Server数据库分区

对于Sql Server数据库分区问题相信很多人还是对其不时很了解，具体Sql Server数据库分区是怎么个情况？下文将为大家解答。什么是数据库分区？数据库分区是一种对表的横向分割，Sql server 2005企业版和之后的Sql server版本才提供这种技术，这种对表的横向分割不同于2000中的表分割，它对访问用户是透明的，用户并不会感觉的表被横向分割了。（2000中的表横向分割是建n个表例如按时间建表每月一个表，表名不同，最后需要做一个大视图）为什么要分区？显而易见分区是为了提高数据库的读写性能，提高数据库的效率；分区是否总是可以提高效率？分区是一把双刃剑，并不总能提高效率，这和具体情况有关系。之所以有分区技术，分区技术用的好的话可以提高性能，是因为一方面分区把一大块数据分成了n小块，这样查询的时候很快定位到某一小块上，在小块中寻址要快很多；另一方面CPU比磁盘IO快很多倍，而硬件上又有多个磁盘，或者是RAID（廉价磁盘冗余阵列），可以让数据库驱动CPU同时去读写不同的磁盘，这样才有可能可以提高效率。分区在有些时候并不能提高读写效率，比如说我们经常看到的按照日期字段去分区MSDN例子，这个实例中是按照记录的生成时间来分区的，把一年的数据分割成12个分区，每月一个。这样的分区导致分区并不能实现CPU同步写并提高写入性能，因为在同一个时段CPU总是要写入到最新的那一个分区对应的磁盘中。另一个问题是：这样分区是否可以提高读取性能呢？答案是不一定，要看根据什么字段来查询，如果是根据时间来查询，根据时间生成报表那么这种分区肯定会提高查询的效率，但是如果是按照某个客户查询客户最近1年内的账单数据，这样数据分布到不同的分区上，这样的话效率就不一定能提高了，这要看数据在同一个分区上连续分布的读性能高，还是CPU从几个磁盘上同步读取，然后在合并数据的性能更高一些，这和读取数据的记录数也有关系。如何分区？用什么字段做分区依据？具体如何分区和涉及的业务有关系，要看业务上最经常的写入和读取操作是什么，然后再考虑分区的策略。既然与具体业务相关，我们就假定一个业务环境，假如我们要做一个论坛，对论坛的帖子和回复表进行分区。论坛中最常见的写操作是1）发帖 2）回复帖子，最常见的读操作是 1）根据帖子id显示帖子详情和分页的帖子回复 2）根据帖子版面帖子列表页根据版面id分页读取帖子列表数据怎么分区更合适呢？现在还没有准确答案，我有两种可能的方案，写下来，大家讨论看看。方案1. 根据帖子ID区域段分区（1-300w一个分区、300w-600w一个分区…），这样理论上可以提高帖子详细页的读取速度，而对于写操作性能没有益处，对于根据版面id读取帖子列表页有可能有益方案2. 根据版面id进行分区，这样对于写性能应该有提高，不同的分区对应不同的版面，当有两个版面同时有发帖回帖操作时，有可能可以并发写。对于根据版面id获得帖子列表页数据也可以提高性能，而对于帖子详细信息页没有性能影响。多大的数据量才需要分区？这个问题我只能说一个内部标准，如果一张表的记录超过在超过1000w，并以每月百万的数据量增长，那就需要分区。上文从这几个方面解析了Sql Server数据库分区问题，现在对数据库分区大家应该都有一个大体的了解，这里介绍的只是Sql Server数据库分区的基本知识，对刚刚入门的初学者来说是很好的学习资料，希望能够帮到大家。

龙生 23 Mar 2011

View Details

教你如何在SQL Server数据库中加密数据

导读：为了防止某些别有用心的人从外部访问SQL Server数据库，盗取SQL Server数据库中的用户姓名、密码、信用卡号等其他重要信息，在我们创建SQL Server数据库驱动的解决方案时，我们首先需要考虑的的第一条设计决策就是如何加密存储SQL Server数据，以此来保证它的安全，免受被他人窥测。 SQL Server数据库中有哪一种支持可以用于加密对象和数据?从一开始就讨论一下SQL Server数据库欠缺什么是明智的，或者是对于SQL Server数据库中的加密部分你不应该做什么。首先，SQL Server数据库有两个内置的密码函数——即，pwdencrypt() 和 pwdcompare()。同时，还有两个SQL Server数据库用来管理密码哈希的没有正式记录的函数:pwdencrypt() 将密码哈希过后进行存储; pwdcompare()将提供的字符串与哈希后的字符串进行比较。不幸的是，这个哈希函数不是非常安全，它可以通过字典攻击算法被破解(类似命令行应用程序!)。这些函数随着SQL Server的版本发展而不断进行修改，这也是另一个没有使用它们的原因。早期版本的SQL Server对密码进行的哈希，在后来的版本中无法解密，所以如果你依赖一个版本中的函数，那么当升级的时候，所有你的加密数据就都没有用了，除非你可以首先对其解密——这也就违背了加密的最初的目的。第二，你可能会尝试去创建一个针对你的数据库的自制的加密解决方案，但是有以下三个理由说明你不要这样做: 除非你是加密专家，否则胡乱编写的加密系统只会提供非常低级的价值不高的保护。新鲜的是，单向密码哈希或者 "ROTx "形式的加密几乎不需要费事就可以被轻松打败。如果由于你自己的能力的缺乏而导致加密被破解，那么你的数据就完蛋了。你需要将所有的东西进行没有加密的备份，是吗?(即使你加密了，那里有没有安全漏洞?) 当市面上提供有专业级别的，具有工业强度的加密解决方案的时候，你就不值得花费时间去自己做。把你的时间用于构建一个好的，坚固的数据库，而不是再重新发明一次车轮。那么，什么才是好的加密数据的方式呢? 对于新手，微软提供了一个自己生成的加密解决方案，CryptoAPI 。对于轻量级的加密，军用级别的安全就不在考虑范围之内，它具有相对容易实现的优势:管理员可以安装一个名为CAPICOM 的ActiveX 控制，它可以在T-SQL存储过程中提供CryptoAPI 功能。CAPICOM 支持各种类型的双向加密和单向哈希算法，所以管理员可以挑选最适合应用程序的问题的部分。如果你对使用微软的解决方案不感兴趣，还有一些很好的第三方的方案可以使用。一家名为ActiveCrypt 的软件有限责任公司制造了XP_CRYPT ，它是SQL Server的插件，可以在视图、程序和触发器中通过扩展存储过程和用户自定义函数(在SQL Server 2000中)来完成加密。你可以下载一个支持无线的MD5,DES ，以及SHA1哈希的免费版本的应用程序; 其他的加密模型就是在比特深度上进行的。(完全版本是无限的。)在你自己的代码中，你可以使用XP_CRYPT，与ActiveX 控制一样(在受限的免费版本中)。对于ASP程序员来说，一个名为AspEncrypt 的组件提供了一种将高级加密整合到你的代码中的简单方式。对数据库文件自身进行加密或者提供传输层上的安全保护怎么样?对于前者，大家可以在Windows系统中持续使用加密文件系统。然而，你必须保存加密密钥的备份，在出现问题的时候，这个数据有可能会丢失。对于后者，有IPSec和SQL Server自己的SSL加密，都是SQL Server和Windows自带的大家的主要精力应该放在避免以明文存储敏感数据，因为从数据库中抽取没有加密的数据同样是最容易受到攻击的薄弱环节。 SQL Server数据库的安全防护工作时很重要的，希望大家能从上文中学到保障SQL Server数据库安全的方法，做好SQL Server数据库的安全工作，确保SQL Server数据库中数据库信息的绝对安全。

龙生 23 Mar 2011

View Details

从外到内提高SQL Server数据库性能

如何提高SQL Server数据库的性能，该从哪里入手呢?笔者认为，该遵循从外到内的顺序，来改善数据库的运行性能。如下图：第一层：网络环境到企业碰到数据库反映速度比较慢时，首先想到的是是否是网络环境所造成的。而不是一开始就想着如何去提高数据库的性能。这是很多数据库管理员的一个误区。因为当网络环境比较恶劣时，你就算再怎么去改善数据库性能，也是枉然。如以前有个客户，向笔者反映数据库响应时间比较长，让笔者给他们一个提高数据库性能的解决方案。那时，笔者感到很奇怪。因为据笔者所知，这家客户数据库的记录量并不是很大。而且，他们配置的数据库服务器硬件很不错。笔者为此还特意跑到他们企业去查看问题的原因。一看原来是网络环境所造成的。这家企业的客户机有200多台，而且都是利用集线器进行连接。这就导致企业内部网络广播泛滥，网络拥塞。而且由于没有部署企业级的杀毒软件，网络内部客户机存在病毒，掠夺了一定的带宽。不仅数据库系统响应速度比较慢，而且其他应用软件，如邮箱系统，速度也不理想。在这种情况下，即使再花十倍、百倍力气去提升SQL Server数据库的性能，也是竹篮子打水一场空。因为现在数据库服务器的性能瓶颈根本不在于数据库本身，而在于企业的网络环境。若网络环境没有得到有效改善，则SQL Server数据库性能是提高不上去的。为此，笔者建议这家企业，想跟他们的网络管理员谈谈，看看如何改善企业的网络环境，减少广播包和网络冲突;并且有效清除局域网内的病毒、木马等等。三个月后，我再去回访这家客户的时候，他们反映数据库性能有了很大的提高。而且其他应用软件，性能也有所改善。所以，当企业遇到数据库性能突然降低的时候，第一个反应就是查看网络环境，看看其实否有恶化。只有如此，才可以少走冤枉路。第二层：服务器配置这里指的服务器配置，主要是讲数据库服务器的硬件配置以及周边配套。虽然说，提高数据库的硬件配置，需要企业付出一定的代价。但是，这往往是一个比较简便的方法。比起优化SQL语句来说，其要简单的多。如企业可以通过增加硬盘的数量来改善数据库的性能。在实际工作中，硬盘输入输出瓶颈经常被数据库管理员所忽视。其实，到并发访问比较多的时候，硬盘输入输出往往是数据库性能的一个主要瓶颈之一。此时，若数据库管理员可以增加几个硬盘，通过磁盘阵列来分散磁盘的压力，无疑是提高数据库性能的一个捷径。如增加服务器的内存或者CPU。当数据库管理员发现数据库性能的不理想是由内存或者CPU所造成的，此时，任何的改善数据库服务器本身的措施都将一物用处。所以，有些数据库管理专家，把改善服务器配置当作数据库性能调整的一个先决条件。如解决部署在同一个数据库服务器上的资源争用问题。虽然我们多次强调，要为数据库专门部署一个服务器。但是，不少企业为了降低信息化的成本，往往把数据库服务器跟应用服务器放在同一个服务器中。这就会导致不同服务器之间的资源争用问题。如把文件服务器跟数据服务器部署在同一个服务器中，当对文件服务器进行备份时，数据库性能就会有明显的下降。所以，在数据库性能发现周期性的变化时，就要考虑是否因为服务器上不同应用对资源的争夺所造成的。故，笔者建议，改善数据库性能时第二个需要考虑的层面，就是要看看能否通过改善服务器的配置来实现。第三层：数据库服务器当通过改善网络环境或者提高服务器配置，都无法达到改善数据库性能的目的时，接下去就需要考察数据库服务器本身了。首先，就需要考虑数据库服务器的配置。一方面，要考虑数据库服务器的连接模式。提供了很多的数据库模式，不同的数据库连接模式对应不同的应用。若数据库管理员能够熟悉企业自身的应用，并且选择合适的连接模式，这往往能够达到改善数据库性能的目的。其次，合理配置数据库服务器的相关作业。如出于安全的需要，数据库管理员往往需要对数据库进行备份。那么，备份的作业放在什么时候合适呢?当然，放在夜晚，夜深人静的时候，对数据库进行备份最好。另外，对于大型数据库，每天都进行完全备份将会是一件相当累人的事情。虽然累得不是我们，可是数据库服务器也会吃不消。差异备份跟完全备份结合将是改善数据库性能的一个不错的策略。第四层：数据库对象若以上三个层面后，数据库性能还不能够得到大幅度改善的话，则就需要考虑是否能够调整数据库对象来完成我们的目的。虽然调整数据库对象往往可以提到不错的效果，但是，往往会对数据库产生比较大的影响。所以，笔者一般不建议用户一开始就通过调整数据库对象来达到改善数据库性能的目的。数据库对象有表、视图、索引、关键字等等。我们也可以通过对这些对象进行调整以实现改善数据库性能的目标。如在视图设计时，尽量把其显示的内容缩小，宁可多增加视图。如出货明细表，销售人员可能希望看到产品编号、产品中英文描述、产品名字、出货日期、客户编号、客户名字等等。但是，对于财务来说，可能就不需要这么全的信息。他们只需要产品编号、客户编号、出货日期等等少量的信息即可。所以，能可浪费一点代码的空间，设计两张视图，对应不同部门的需求。如此，财务部门在查询数据时，不会为不必要的数据浪费宝贵的资源。如可以通过合理设置索引来提高数据库的性能。索引对于提高数据的查询效率，有着非常好的效果。对一些需要重复查询的数据、或者数据修改不怎么多的表设置索引，无疑是一个不错的选择。另外，要慎用存储过程。虽然说存储过程可以帮助大家实现很多需求。但是，在万不得已的情况下，不要使用存储过程。而利用前台的应用程序来实现需求。这主要是因为在通常情况下，前台应用程序的执行效率往往比后台数据库存储过程要高的多。第五层：SQL 语句若以上各个层面你都努力过，但是还不满足由此带来的效果的话，则还有最后一招。通过对SQL语句进行优化，也可以达到改善数据库性能的目的。虽然说SQL Server服务器自身就带有一个SQL语句优化器。他会对用户的SQL语句进行调整、优化，以达到一个比较好的执行效果。但是，据笔者的了解，这个最多只能够优化一些粗略的层面。或者说，80%的优化仍然需要数据库管理员的配合。要数据库管理员跟SQL优化器进行配合，才能够起到非常明显的作用。不过，SQL语句的调整对于普通数据库管理员来说，可能有一定的难度。除非受过专业的训练，一般很难对SQL语句进行优化。还好笔者受过这方面的专业训练，对这方面有比较深的认识。如在SQL语句中避免使用直接量。任何一个包含有直接量的SQL语句都不太可能被再次使用。我们数据库管理员要学会利用主机变量来代替直接量。不然，这些不可再用的查询语句将使得程序缓存被不可再用的SQL语句填满。这都是平时工作中的一些小习惯。总之，笔者认为，在数据库性能调优的时候，若能够遵循如上的顺序，必定可以让我们少走冤枉路，不花无用功。其实，数据库调优并没有我们想象的这么难。只要我们能够掌握其中的诀窍，数据库调优将可以手到擒来。上文中讲到的提高SQL Server数据库性能大家要灵活运用，这些方法也不是什么灵丹妙药，要根据具体情况去操作，希望这篇文章在供大家参考的时候能够帮到大家。

龙生 23 Mar 2011

View Details

« Previous 1 … 391 392 393 … 401 Next »