JOIN: 如果表中有至少一个匹配，则返回行 LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行 RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行 FULL JOIN: 只要其中一个表中存在匹配，就返回行
View Details海量数据（百万以上），其中有些全部字段都相同，有些部分字段相同，怎样高效去除重复？ 如果要删除手机(mobilePhone)，电话(officePhone)，邮件(email)同时都相同的数据，以前一直使用这条语句进行去重： delete from 表 where id not in ( select max(id) from 表 group by mobilePhone,officePhone,email ) or delete from 表 where id not in ( select min(id) from 表 group by mobilePhone,officePhone,email ) 复制代码 其中下面这条会稍快些。上面这条数据对于100万以内的数据效率还可以，重复数1/5的情况下几分钟到几十分钟不等，但是如果数据量达到300万以上，效率骤降，如果重复数据再多点的话，常常会几十小时跑不完，有时候会锁表跑一夜都跑不完。无奈只得重新寻找新的可行方法，今天终于有所收获： //查询出唯一数据的ID,并把他们导入临时表tmp中 select min(id) as mid into tmp from 表 group by mobilePhone,officePhone,email //查询出去重后的数据并插入finally表中 insert into finally select (除ID以外的字段) from customers_1 where id in (select mid from tmp) 复制代码 效率对比：用delete方法对500万数据去重（1/2重复）约4小时 […]
View Details1.SQL SERVER的数据类型 数据类弄是数据的一种属性，表示数据所表示信息的类型。任何一种计算机语言都定义了自己的数据类型。当然，不同的程序语言都具有不同的特点，所定义的数据类型的各类和名称都或多或少有些不同。SQLServer 提供了 25 种数据类型： ·Binary [(n)] ·Varbinary [(n)] ·Char [(n)] ·Varchar[(n)] ·Nchar[(n)] ·Nvarchar[(n)] ·Datetime ·Smalldatetime ·Decimal[(p[,s])] ·Numeric[(p[,s])] ·Float[(n)] ·Real ·Int ·Smallint ·Tinyint ·Money ·Smallmoney ·Bit ·Cursor ·Sysname ·Timestamp ·Uniqueidentifier ·Text ·Image ·Ntext (1)二进制数据类型 二进制数据包括 Binary、Varbinary 和 Image Binary 数据类型既可以是固定长度的(Binary),也可以是变长度的。 Binary[(n)] 是 n 位固定的二进制数据。其中，n 的取值范围是从 1 到 8000。其存储窨的大小是 n + 4 个字节。 Varbinary[(n)] 是 n 位变长度的二进制数据。其中，n 的取值范围是从 1 到 8000。其存储窨的大小是 n + 4个字节，不是n 个字节。 在 Image 数据类型中存储的数据是以位字符串存储的，不是由 SQL Server 解释的，必须由应用程序来解释。例如，应用程序可以使用BMP、TIEF、GIF 和 JPEG 格式把数据存储在 Image 数据类型中。 (2)字符数据类型 字符数据的类型包括 Char，Varchar 和 Text 字符数据是由任何字母、符号和数字任意组合而成的数据。 Varchar 是变长字符数据，其长度不超过 8KB。Char 是定长字符数据，其长度最多为 8KB。超过 8KB 的ASCII 数据可以使用Text数据类型存储。例如，因为 Html 文档全部都是 ASCII 字符，并且在一般情况下长度超过 8KB，所以这些文档可以 Text 数据类型存储在SQL Server 中。 (3)Unicode 数据类型 Unicode 数据类型包括 Nchar,Nvarchar 和Ntext 在 Microsoft SQL Server 中，传统的非 Unicode 数据类型允许使用由特定字符集定义的字符。在 SQL Server安装过程中，允许选择一种字符集。使用 Unicode 数据类型，列中可以存储任何由Unicode 标准定义的字符。在 Unicode 标准中，包括了以各种字符集定义的全部字符。使用Unicode数据类型，所战胜的窨是使用非 Unicode 数据类型所占用的窨大小的两倍。 在 SQL Server 中，Unicode […]
View DetailsOracle数据库在开始菜单中使用自带的程序卸载后，我们经常为了避免下次再次安装过程中这样那样的错误发生，我们要对Oracle进行下完全的卸载，下面是手工处理删除不掉的一些文件夹和目录。 常用Oracle数据库的同仁都知道，Oracle经常会莫名的出现一些错误，导致数据库无法正常启动或使用。 经过对出现这些问题的解决，后来找到了问题的根源，也就是再次安装Oracle数据库时，未对之前安装的Oracle进行彻底的卸载所导致。下面我就详细谈谈如何才能彻底卸载Oracle数据库 一、Linux 平台 Linux 平台下卸载Oracle 非常简单，即：删除Oracle安装目录下的所有文件和文件夹即可。 二、Windows 平台 其实这篇文章我主要想就在Windows平台上如何彻底的卸载Oracle进行说明。 因为Oracle在Windows下的卸载颇有一些麻烦，如果不能完全卸载有可能影响将来的再次安装！常规卸载方法是运行Oracle的自带的 卸载程序，可遗憾的是我在卸载时总不能完全卸载，当我再次安装Oracle时，就会出现莫名其妙的问题，并且这种卸载方式比较麻烦，比较慢，下面我介绍一 种比较快并且能够彻底卸载Oracle的方法。 1.关闭oracle所有的服务。可以在windows的服务管理器中关闭； 2.打开注册表：regedit 打开路径： 这一步中，可以用到我们在注册表清理软件一文中介绍的一款及其方便查找路径的小软件。Registry Manager HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ 删除该路径下的所有以oracle开始的服务名称，这个键是标识Oracle在windows下注册的各种服务！ 3.打开注册表，找到路径： HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 删除该oracle目录，该目录下注册着Oracle数据库的软件安装信息。 4.删除注册的oracle事件日志，打开注册表 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application 删除注册表的以oracle开头的所有项目。 5.删除环境变量path中关于oracle的内容。 鼠标右键右单击“我的电脑-->属性-->高级-->环境变量-->PATH 变量。 删除Oracle在该值中的内容。注意:path中记录着一堆操作系统的目录，在windows中各个目录之间使用分号（;）隔开的，删除时注意。 建议：删除PATH环境变量中关于Oracle的值时，将该值全部拷贝到文本编辑器中，找到对应的Oracle的值，删除后，再拷贝修改的串，粘贴到PATH环境变量中，这样相对而言比较安全。 6.重新启动操作系统。 以上1~5个步骤操作完毕后，重新启动操作系统。 7.重启操作系统后各种Oracle相关的进程都不会加载了。这时删除Oracle_Home下的所有数据。（Oracle_Home指Oracle程序的安装目录） 8.删除C:\Program Files下oracle目录。 （该目录视Oracle安装所在路径而定） 9.删除开始菜单下oracle项，如： C:\Documents and Settings\All Users\「开始」菜单\程序\Oracle – ora10g 不同的安装这个目录稍有不同。 如果不删除开始菜单下的Oracle相关菜单目录，没关系，这个不影响再次安装Oracle.当再次安装Oracle时，该菜单会被替换。 至此，Windows平台下Oracle就彻底卸载了。 到这里，我们的任务就完成了， 如果哪位人才有感兴趣的同仁可以自己编写一个小工具，用来彻底删除Oracle.提供上来大家一起用用，我想很多人会感激涕零的。 FROM:http://www.tree360.cn/blog/article.asp?id=123
View DetailsSQL语句先前写的时候，很容易把一些特殊的用法忘记，我特此整理了一下SQL语句操作，方便自己写SQL时方便一点，想贴上来，一起看看，同时希望大家能共同多多提意见，也给我留一些更好的佳句，整理一份《精妙SQL速查手册》，不吝赐教! 一、基础 1、说明：创建数据库
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">CREATE</span><span> </span><span class="keyword">DATABASE</span><span> </span><span class="keyword">database</span><span>-</span><span class="keyword">name</span><span> </span></span></li></ol> |
2、说明：删除数据库
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">drop</span><span> </span><span class="keyword">database</span><span> dbname </span></span></li></ol> |
3、说明：备份sql server
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="comment">--- 创建 备份数据的 device</span><span> </span></span></li><li><span>USE master </span></li><li class="alt"><span><span class="keyword">EXEC</span><span> sp_addumpdevice </span><span class="string">'disk'</span><span>, </span><span class="string">'testBack'</span><span>, </span><span class="string">'c:\mssql7backup\MyNwind_1.dat'</span><span> </span></span></li><li><span><span class="comment">--- 开始 备份</span><span> </span></span></li><li class="alt"><span>BACKUP <span class="keyword">DATABASE</span><span> pubs </span><span class="keyword">TO</span><span> testBack </span></span></li></ol> |
4、说明：创建新表
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">create</span><span> </span><span class="keyword">table</span><span> tabname(col1 type1 [</span><span class="op">not</span><span> </span><span class="op">null</span><span>] [</span><span class="keyword">primary</span><span> </span><span class="keyword">key</span><span>],col2 type2 [</span><span class="op">not</span><span> </span><span class="op">null</span><span>],..) </span></span></li><li><span><span class="comment">-------根据已有的表创建新表：</span><span> </span></span></li><li class="alt"><span>A：<span class="keyword">create</span><span> </span><span class="keyword">table</span><span> tab_new </span><span class="op">like</span><span> tab_old (使用旧表创建新表) </span></span></li><li><span>B：<span class="keyword">create</span><span> </span><span class="keyword">table</span><span> tab_new </span><span class="keyword">as</span><span> </span><span class="keyword">select</span><span> col1,col2… </span><span class="keyword">from</span><span> tab_old definition </span><span class="keyword">only</span><span> </span></span></li></ol> |
5、说明：删除新表
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">drop</span><span> </span><span class="keyword">table</span><span> tabname </span></span></li></ol> |
6、说明：增加一个列
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">Alter</span><span> </span><span class="keyword">table</span><span> tabname </span><span class="keyword">add</span><span> </span><span class="keyword">column</span><span> col type </span></span></li></ol> |
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">Alter</span><span> </span><span class="keyword">table</span><span> tabname </span><span class="keyword">add</span><span> </span><span class="keyword">primary</span><span> </span><span class="keyword">key</span><span>(col) </span></span></li></ol> |
说明：删除主键
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">Alter</span><span> </span><span class="keyword">table</span><span> tabname </span><span class="keyword">drop</span><span> </span><span class="keyword">primary</span><span> </span><span class="keyword">key</span><span>(col) </span></span></li></ol> |
8、说明：创建索引
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">create</span><span> [</span><span class="keyword">unique</span><span>] </span><span class="keyword">index</span><span> idxname </span><span class="keyword">on</span><span> tabname(col….) </span></span></li></ol> |
说明：删除索引
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">drop</span><span> </span><span class="keyword">index</span><span> idxname </span></span></li></ol> |
注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">create</span><span> </span><span class="keyword">view</span><span> viewname </span><span class="keyword">as</span><span> </span><span class="keyword">select</span><span> statement </span></span></li></ol> |
说明：删除视图
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">drop</span><span> </span><span class="keyword">view</span><span> viewname </span></span></li></ol> |
10、说明：几个简单的基本的sql语句
|
1 |
<ol class="dp-sql"><li class="alt"><span><span>选择：</span><span class="keyword">select</span><span> * </span><span class="keyword">from</span><span> table1 </span><span class="keyword">where</span><span> 范围 </span></span></li><li><span>插入：<span class="keyword">insert</span><span> </span><span class="keyword">into</span><span> table1(field1,field2) </span><span class="keyword">values</span><span>(value1,value2) </span></span></li><li class="alt"><span>删除：<span class="keyword">delete</span><span> </span><span class="keyword">from</span><span> table1 </span><span class="keyword">where</span><span> 范围 </span></span></li><li><span>更新：<span class="keyword">update</span><span> table1 </span><span class="keyword">set</span><span> field1=value1 </span><span class="keyword">where</span><span> 范围 </span></span></li><li class="alt"><span>查找：<span class="keyword">select</span><span> * </span><span class="keyword">from</span><span> table1 </span><span class="keyword">where</span><span> field1 </span><span class="op">like</span><span> ’%value1%’ </span><span class="comment">---like的语法很精妙，查资料!</span><span> </span></span></li><li><span>排序：<span class="keyword">select</span><span> * </span><span class="keyword">from</span><span> table1 </span><span class="keyword">order</span><span> </span><span class="keyword">by</span><span> field1,field2 [</span><span class="keyword">desc</span><span>] </span></span></li><li class="alt"><span>总数：<span class="keyword">select</span><span> </span><span class="func">count</span><span> </span><span class="keyword">as</span><span> totalcount </span><span class="keyword">from</span><span> table1 </span></span></li><li><span>求和：<span class="keyword">select</span><span> </span><span class="func">sum</span><span>(field1) </span><span class="keyword">as</span><span> sumvalue </span><span class="keyword">from</span><span> table1 </span></span></li><li class="alt"><span>平均：<span class="keyword">select</span><span> </span><span class="func">avg</span><span>(field1) </span><span class="keyword">as</span><span> avgvalue </span><span class="keyword">from</span><span> table1 </span></span></li><li><span>最大：<span class="keyword">select</span><span> </span><span class="keyword">max</span><span>(field1) </span><span class="keyword">as</span><span> maxvalue </span><span class="keyword">from</span><span> table1 </span></span></li><li class="alt"><span>最小：<span class="keyword">select</span><span> </span><span class="keyword">min</span><span>(field1) </span><span class="keyword">as</span><span> minvalue </span><span class="keyword">from</span><span> table1 </span></span></li></ol> |
11、说明：几个高级查询运算词 A： UNION 运算符 UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL)，不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 B： EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。 C： INTERSECT […]
View Details自从上一篇文章发出之后，收到了很朋友的关注。很多朋友要求多多实践，而不是纯粹的理论。确实，从打算出这个系列开始，我就本着实践的思想来进行的!同时，为了使得大家更好的理解、消化这些知识，我会定期的就所写内容进行在线的视频讲座，朋友们可以去参与这个小组：http://www.agilesharp.com/c/sqlprofiler.aspx，报名活动开始啦：http://www.agilesharp.com/Event.aspx/T-2 议程如下: 实践概述 图形化执行计划实战 执行计划信息解读 实践概述 执行计划可以辅助我们写出高效率的T-SQL代码，同时也可以找出现有T-SQL代码的问题，还可以监控数据库!当然，最后如何使用执行计划还是取决于我们自己了，但是不管怎么样，我们首先学会解析执行计划中所包含的信息，最快的学习方法就是实践。下面，我们就从一个实践开始。 为了使得大家易于理解，这里的例子不会太复杂，随着课程的不断深入，后续的示例也会越来越复杂。同时，如果大家也想跟着一起动手实践，那么希望朋友们安装SQL2005或更高版本，同时记得安装AdventureWorks数据库。下载地址为：http://msftdbprodsamples.codeplex.com 另外，有一个需要注意的是，由于数据库中数据，操作和时间的关系，可能大家在运行脚本产生的执行计划和我这里不完全一样，这是没有任何问题的! 图形化执行计划实战 下面我们正式进入要讨论的话题。 首先,为了使得我们可以查看执行计划,最起码要确保我们在登录数据库的时候,要被授予权限,如下语句所示:
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">GRANT</span><span> SHOWPLAN </span><span class="keyword">TO</span><span> [username] </span></span></li></ol> |
为了将讨论集中在执行计划(估计执行计划和实际执行计划)上,我们这里这是运行一个比较简单的查询,如下代码所示:
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">SELECT</span><span> * </span><span class="keyword">FROM</span><span> [dbo].[DatabaseLog]; </span></span></li></ol> |
下面,我们就来看看这个语句的估计执行计划,正如之前文章讲述的:估计执行计划是优化器使用了的元数据,成本分析算法等而产生的计划,这个计划是查询语句执前的一个分析! 显示估计执行计划 我们可以采用以下几种方式显示估计执行计划: 点击Sql Server Studio工具栏上的按钮: 在查询窗口右击鼠标，如下所示： 使用快捷键“CTRL + L”. 对以上面的查询语句，显示的图形化的估计查询计划如下： 显示执行查询计划 与估计执行计划不同，实际的执行计划不是优化器产生的，实际的执行计划是底层的存储引擎在执行时候产生的，这个计划中包含了大量的实际的底层数据和相关的信息。 我们可以采用以下方式获得实际的执行计划，如下所示： 点击工具栏上面的按钮： 在查询窗口右击鼠标，如下： 快捷键“CTRL + M” 上述查询的实际执行计划如下所示： 大家初一看，以为两者没有区别，但是它们包含的数据信息很多是不一样的。 下面，我们就开始对图形化的执行计划进行解读。 执行计划信息解读 刚刚大家已经看了图形化的执行计划了,相关大家比较关心的问题有两个:如何解读执行计划中提供的各种信息;如何采用执行计划来进行性能调优。 我们首先来看看第一个问题。 一般而言，我们在阅读图形化的执行计划的时候顺序是这样的：从右向左，从下往上。也就说：sql执行的第一步就显示在执行计划的右下角。 在图形化执行计划中的每一个图标，都表示一个操作，在之前的执行计划中就有两个操作。并且每个操作之前采用箭头连接起来，表明了数据流动的方向，其中箭头的粗细就反应了数据量的大小。 另外，在每个操作下面都显示了一个百分比。 对于估计执行计划而言，这个数字就是优化器对执行计划中每一个操作步骤进行成本分析后的结果。例外，在我们的例子中，整个查询最后会有两个操作会进行，Select和Table Scan，其中整个查询的成本将会落在Table Scan(整表扫描)上。 操作提示信息 当我们把鼠标放在每个操作或箭头上面的时候，就会弹出更多的相关信息，我们下面就来具体的看一看。 例如，当我们把鼠标放在执行计划的Select操作上面，显示如图： 上面图中给出的信息非常清楚了，我这里只是解释一下“估计子树大小”。因为执行计划可以看出是sql语句的逻辑执行步骤，这个选项就告诉我们：在我们现在所看的这个操作步骤以及后面的所有步骤的开销是多少，是一个总计数字。 如何朋友们还有有什么不清楚的，我们在后续将要展开的在线讲座中讲述! […]
View Details我们做软件开发的，大部分人都离不开跟数据库打交道，特别是erp开发的，跟数据库打交道更是频繁，存储过程动不动就是上千行，如果数据量大，人员流动大，那么我么还能保证下一段时间系统还能流畅的运行吗?那么还能保证下一个人能看懂我么的存储过程吗?那么我结合公司平时的培训和平时个人工作经验和大家分享一下，希望对大家有帮助。 要知道sql语句，我想我们有必要知道sqlserver查询分析器怎么执行我么sql语句的，我么很多人会看执行计划，或者用profile来监视和调优查询语句或者存储过程慢的原因，但是如果我们知道查询分析器的执行逻辑顺序，下手的时候就胸有成竹，那么下手是不是有把握点呢? 一：查询的逻辑执行顺序 (1) FROM < left_table> (2) ON < join_condition> (3) < join_type> JOIN < right_table> (4) WHERE < where_condition> (5) GROUP BY < group_by_list> (6) WITH {cube | rollup} (7) HAVING < having_condition> (8) SELECT (9) DISTINCT (11) < top_specification> < select_list> (10) ORDER BY < order_by_list> 标准的SQL 的解析顺序为: (1).FROM 子句 组装来自不同数据源的数据 (2).WHERE 子句 基于指定的条件对记录进行筛选 (3).GROUP BY 子句 将数据划分为多个分组 (4).使用聚合函数进行计算 (5).使用HAVING子句筛选分组 (6).计算所有的表达式 (7).使用ORDER BY对结果集进行排序 二 执行顺序： […]
View Details虽然查询速度慢的原因很多，但是如果通过一定的优化，也可以使查询问题得到一定程度的解决。 查询速度慢的原因很多，常见如下几种： 没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) I/O吞吐量小，形成了瓶颈效应。 没有创建计算列导致查询不优化。 内存不足 网络速度慢 查询出的数据量过大(可以采用多次查询，其他的方法降低数据量) 锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷) sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。 返回了不必要的行和列 查询语句不好，没有优化 可以通过如下方法来优化查询 : 把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb应放在RAID0上，SQL2000不在支持。数据量(尺寸)越大，提高I/O越重要. 纵向、横向分割表，减少表的尺寸(sp_spaceuse) 升级硬件 根据查询条件,建立索引、优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当(最好是使用默认值0)。索引应该尽量小，使用字节数小的列建索引好(参照索引的创建),不要对有限的几个值的字段建单一索引如性别字段 提高网速; 扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server 2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍。如果另外安装了全文检索功能，并打算运行 Microsoft 搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍(虚拟内存大小设置的一半)。 增加服务器CPU个数;但是必须明白并行处理比串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作UPDATE,INSERT，DELETE还不能并行处理。 如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引耗空间。 like 'a%' 使用索引 like '%a' 不使用索引。用 like '%a%' 查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。 DB Server 和APPLication Server分离;OLTP和OLAP分离 分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。(参照SQL帮助文件’分区视图') 在实现分区视图之前，必须先水平分区表 在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。 重建索引DBCC REINDEX ,DBCC INDEXDEFRAG,收缩数据和日志DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。 在T-sql的写法上有很大的讲究，下面列出常见的要点：首先，DBMS处理查询计划的过程是这样的： 查询语句的词法、语法检查 将语句提交给DBMS的查询优化器 优化器做代数优化和存取路径的优化 由预编译模块生成查询规划 然后在合适的时间提交给系统处理执行 最后将执行结果返回给用户其次，看一下SQL SERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。 Commit和rollback的区别Rollback:回滚所有的事务。Commit:提交当前的事务.没有必要在动态SQL里写事务，如果要写请写在外面。如： begin tran exec(@s) commit […]
View DetailsMicrosoft SQL Server 2005 提供了一些工具来监控数据库。方法之一是动态管理视图。动态管理视图 (DMV) 和动态管理函数 (DMF) 返回的服务器状态信息可用于监控服务器实例的运行状况、诊断问题和优化性能。 常规服务器动态管理对象包括： dm_db_*：数据库和数据库对象 dm_exec_*：执行用户代码和关联的连接 dm_os_*：内存、锁定和时间安排 dm_tran_*：事务和隔离 dm_io_*：网络和磁盘的输入/输出 此部分介绍为监控 SQL Server 运行状况而针对这些动态管理视图和函数运行的一些常用查询。 示例查询 您可以运行以下查询来获取所有 DMV 和 DMF 名称：
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">SELECT</span><span> * </span><span class="keyword">FROM</span><span> sys.system_objects </span></span></li><li><span><span class="keyword">WHERE</span><span> </span><span class="keyword">name</span><span> </span><span class="op">LIKE</span><span> </span><span class="string">'dm_%'</span><span> </span></span></li><li class="alt"><span><span class="keyword">ORDER</span><span> </span><span class="keyword">BY</span><span> </span><span class="keyword">name</span><span> </span></span></li></ol> |
监控 CPU 瓶颈 CPU 瓶颈通常由以下原因引起：查询计划并非最优、配置不当、设计因素不良或硬件资源不足。下面的常用查询可帮助您确定导致 CPU 瓶颈的原因。 下面的查询使您能够深入了解当前缓存的哪些批处理或过程占用了大部分 CPU 资源。
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">SELECT</span><span> </span><span class="keyword">TOP</span><span> 50 </span></span></li><li><span> <span class="func">SUM</span><span>(qs.total_worker_time) </span><span class="keyword">AS</span><span> total_cpu_time, </span></span></li><li class="alt"><span> <span class="func">SUM</span><span>(qs.execution_count) </span><span class="keyword">AS</span><span> total_execution_count, </span></span></li><li><span> <span class="func">COUNT</span><span>(*) </span><span class="keyword">AS</span><span> number_of_statements, </span></span></li><li class="alt"><span> qs.sql_handle </span></li><li><span><span class="keyword">FROM</span><span> sys.dm_exec_query_stats </span><span class="keyword">AS</span><span> qs </span></span></li><li class="alt"><span><span class="keyword">GROUP</span><span> </span><span class="keyword">BY</span><span> qs.sql_handle </span></span></li><li><span><span class="keyword">ORDER</span><span> </span><span class="keyword">BY</span><span> </span><span class="func">SUM</span><span>(qs.total_worker_time) </span><span class="keyword">DESC</span><span> </span></span></li></ol> |
下面的查询显示缓存计划所占用的 CPU 总使用率(带 SQL 文本)。
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">SELECT</span><span> </span></span></li><li><span> total_cpu_time, </span></li><li class="alt"><span> total_execution_count, </span></li><li><span> number_of_statements, </span></li><li class="alt"><span> s2.text </span></li><li><span> <span class="comment">--(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text</span><span> </span></span></li><li class="alt"><span><span class="keyword">FROM</span><span> </span></span></li><li><span> (<span class="keyword">SELECT</span><span> </span><span class="keyword">TOP</span><span> 50 </span></span></li><li class="alt"><span> <span class="func">SUM</span><span>(qs.total_worker_time) </span><span class="keyword">AS</span><span> total_cpu_time, </span></span></li><li><span> <span class="func">SUM</span><span>(qs.execution_count) </span><span class="keyword">AS</span><span> total_execution_count, </span></span></li><li class="alt"><span> <span class="func">COUNT</span><span>(*) </span><span class="keyword">AS</span><span> number_of_statements, </span></span></li><li><span> qs.sql_handle <span class="comment">--,</span><span> </span></span></li><li class="alt"><span> <span class="comment">--MIN(statement_start_offset) AS statement_start_offset, </span><span> </span></span></li><li><span> <span class="comment">--MAX(statement_end_offset) AS statement_end_offset</span><span> </span></span></li><li class="alt"><span> <span class="keyword">FROM</span><span> </span></span></li><li><span> sys.dm_exec_query_stats <span class="keyword">AS</span><span> qs </span></span></li><li class="alt"><span> <span class="keyword">GROUP</span><span> </span><span class="keyword">BY</span><span> qs.sql_handle </span></span></li><li><span> <span class="keyword">ORDER</span><span> </span><span class="keyword">BY</span><span> </span><span class="func">SUM</span><span>(qs.total_worker_time) </span><span class="keyword">DESC</span><span>) </span><span class="keyword">AS</span><span> stats </span></span></li><li class="alt"><span> <span class="op">CROSS</span><span> APPLY sys.dm_exec_sql_text(stats.sql_handle) </span><span class="keyword">AS</span><span> s2 </span></span></li></ol> |
下面的查询显示 CPU 平均占用率最高的前 50 个 SQL 语句。
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">SELECT</span><span> </span><span class="keyword">TOP</span><span> 50 </span></span></li><li><span>total_worker_time/execution_count <span class="keyword">AS</span><span> [</span><span class="func">Avg</span><span> CPU </span><span class="keyword">Time</span><span>], </span></span></li><li class="alt"><span>(<span class="keyword">SELECT</span><span> </span><span class="func">SUBSTRING</span><span>(text,statement_start_offset/2,(</span><span class="func">CASE</span><span> </span><span class="keyword">WHEN</span><span> statement_end_offset = -1 </span><span class="keyword">then</span><span> LEN(</span><span class="func">CONVERT</span><span>(nvarchar(</span><span class="keyword">max</span><span>), text)) * 2 </span><span class="keyword">ELSE</span><span> statement_end_offset </span><span class="keyword">end</span><span> -statement_start_offset)/2) </span><span class="keyword">FROM</span><span> sys.dm_exec_sql_text(sql_handle)) </span><span class="keyword">AS</span><span> query_text, * </span></span></li><li><span><span class="keyword">FROM</span><span> sys.dm_exec_query_stats </span></span></li><li class="alt"><span><span class="keyword">ORDER</span><span> </span><span class="keyword">BY</span><span> [</span><span class="func">Avg</span><span> CPU </span><span class="keyword">Time</span><span>] </span><span class="keyword">DESC</span><span> </span></span></li></ol> |
下面显示用于找出过多编译/重新编译的 DMV 查询。
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">select</span><span> * </span><span class="keyword">from</span><span> sys.dm_exec_query_optimizer_info </span></span></li><li><span><span class="keyword">where</span><span> </span></span></li><li class="alt"><span> counter = <span class="string">'optimizations'</span><span> </span></span></li><li><span> <span class="op">or</span><span> counter = </span><span class="string">'elapsed time'</span><span> </span></span></li></ol> |
下面的示例查询显示已重新编译的前 25 个存储过程。plan_generation_num 指示该查询已重新编译的次数。
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">select</span><span> </span><span class="keyword">top</span><span> 25 </span></span></li><li><span> sql_text.text, </span></li><li class="alt"><span> sql_handle, </span></li><li><span> plan_generation_num, </span></li><li class="alt"><span> execution_count, </span></li><li><span> dbid, </span></li><li class="alt"><span> objectid </span></li><li><span><span class="keyword">from</span><span> sys.dm_exec_query_stats a </span></span></li><li class="alt"><span> <span class="op">cross</span><span> apply sys.dm_exec_sql_text(sql_handle) </span><span class="keyword">as</span><span> sql_text </span></span></li><li><span><span class="keyword">where</span><span> plan_generation_num > 1 </span></span></li><li class="alt"><span><span class="keyword">order</span><span> </span><span class="keyword">by</span><span> plan_generation_num </span><span class="keyword">desc</span><span> </span></span></li></ol> |
效率较低的查询计划可能增大 CPU 占用率。 下面的查询显示哪个查询占用了最多的 CPU 累计使用率。
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">SELECT</span><span> </span></span></li><li><span> highest_cpu_queries.plan_handle, </span></li><li class="alt"><span> highest_cpu_queries.total_worker_time, </span></li><li><span> q.dbid, </span></li><li class="alt"><span> q.objectid, </span></li><li><span> q.number, </span></li><li class="alt"><span> q.encrypted, </span></li><li><span> q.[text] </span></li><li class="alt"><span><span class="keyword">from</span><span> </span></span></li><li><span> (<span class="keyword">select</span><span> </span><span class="keyword">top</span><span> 50 </span></span></li><li class="alt"><span> qs.plan_handle, </span></li><li><span> qs.total_worker_time </span></li><li class="alt"><span> <span class="keyword">from</span><span> </span></span></li><li><span> sys.dm_exec_query_stats qs </span></li><li class="alt"><span> <span class="keyword">order</span><span> </span><span class="keyword">by</span><span> qs.total_worker_time </span><span class="keyword">desc</span><span>) </span><span class="keyword">as</span><span> highest_cpu_queries </span></span></li><li><span> <span class="op">cross</span><span> apply sys.dm_exec_sql_text(plan_handle) </span><span class="keyword">as</span><span> q </span></span></li><li class="alt"><span><span class="keyword">order</span><span> </span><span class="keyword">by</span><span> highest_cpu_queries.total_worker_time </span><span class="keyword">desc</span><span> </span></span></li></ol> |
下面的查询显示一些可能占用大量 CPU 使用率的运算符(例如 ‘%Hash Match%’、‘%Sort%’)以找出可疑对象。
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword">select</span><span> * </span></span></li><li><span><span class="keyword">from</span><span> </span></span></li><li class="alt"><span> sys.dm_exec_cached_plans </span></li><li><span> <span class="op">cross</span><span> apply sys.dm_exec_query_plan(plan_handle) </span></span></li><li class="alt"><span><span class="keyword">where</span><span> </span></span></li><li><span> <span class="func">cast</span><span>(query_plan </span><span class="keyword">as</span><span> nvarchar(</span><span class="keyword">max</span><span>)) </span><span class="op">like</span><span> </span><span class="string">'%Sort%'</span><span> </span></span></li><li class="alt"><span> <span class="op">or</span><span> </span><span class="func">cast</span><span>(query_plan </span><span class="keyword">as</span><span> nvarchar(</span><span class="keyword">max</span><span>)) </span><span class="op">like</span><span> </span><span class="string">'%Hash Match%'</span><span> </span></span></li></ol> |
[…]
View Details想要将值插入到自动编号（或者说是标识列，IDENTITY）中去，需要设定 SET IDENTITY_INSERT 示例： 1.首先建立一个有标识列的表：CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40)) 2.尝试在表中做以下操作：INSERT INTO products (id, product) VALUES(3, 'garden shovel') 结果会导致错误：“当 IDENTITY_INSERT 设置为 OFF 时，不能向表 'products' 中的标识列插入显式值。” 3.改用：SET IDENTITY_INSERT products ONINSERT INTO products (id, product) VALUES(1, 'garden shovel') 返回正确。 4.建立另外一个表products2，尝试相同插入操作：CREATE TABLE products2 (id int IDENTITY PRIMARY KEY, product varchar(40)) 然后执行：SET IDENTITY_INSERT products2 ONINSERT INTO products2 (id, product) VALUES(1, 'garden shovel') 导致错误：“表 'material.dbo.products' 的 IDENTITY_INSERT 已经为 ON。无法对表 'products2' 执行 SET 操作。” 改为执行：SET IDENTITY_INSERT products OFFSET IDENTITY_INSERT products2 ONINSERT INTO products2 (id, product) VALUES(2, 'garden shovel') 执行通过。 5.尝试以下操作：SET IDENTITY_INSERT […]
View Details