Category Archives: Database

SQL Server：安全设计从头起

最基本的要点 如果你不能理解SQL Server security基本的概念，就马上先停止开发并先阅读这些开发准则，你不可能在不知道这些概念的基础上就能够正确地使一个数据库安全化。程序的安全正如一辆卡车一样。你具有一个发动机，一把钥匙，当钥匙打开发动机即发动机启动之后，就有可能发生的全部过程。如果你忽略了某些细节，驾驶过程中就会发生很多麻烦。在问题产生之后，你可以将卡车交给一个修理工，然而对于程序的安全问题，你自己将是数据库的修理工。 开始，你必须选择以下两种安全模式： Windows 认证模式：用户通过一个现成的Windows用户帐号来连接服务器。当一个用户试图与一个服务器连接的时候，SQL Server将会认证用户的Windows帐号的名称和密码。用户不能同时进入网络和SQL Server，只能进入其中的一个。这一方法也被称为一个被依赖的连接。 混合模式：这一模式将Windows认证模式和SQL Server认证联接在一起。用户可以通过一个Windows用户帐号进行连接，这正如Windows认证一样。但是，你也可以在SQL Server中直接建立用户帐号。每一个SQL Server帐号都存储了一个用户名和密码。 我们建议在可能的情况下都使用Windows认证模式。然而，混合模式很可以使用，特别是在SQL Server 7.0(或更早版本)都可以使用。.对于早期的SQL Server版本，SQL Server认证存在一些缺点。Windows认证已经集成了操作系统的安全系统，这就提供了比SQL Server认证更多的安全特性，很容易的使用，效率更加高，安全性更加好。在设计开始的时候，你应该认真考虑选择哪一种模式最为合适。 密码 无论采用哪一些模式，你应该记住在SQL Server中为系统管理者(sa)设置一个密码。当安装SQL Server时，安装程序会自动建立一个带有SQL Server注册名称(sa)和一个空白密码的管理用户。如果你保持这些用户设置原样而使用混合安全模式，任何具备一点SQL Server基础知识的用户都可以很容易地进入到你的数据库中并做任何他想要做的事情。如果你使用的是Windows认证模式，在理论上你无需为sa用户设置一个密码，因为SQL Server注册不会接受这些的设置。但是设置是一个很良好的操作，尤其是当你被迫转移到将来使用的混合模式的时候。 如果安全模式已经启动，注册将成为关键。你必须使用正确的方法来启动程序，注册也是同样的，如果你不能正确地输入用户名和密码，程序将不能连接到网络，同时也不能连接到SQL Server。 作为一个管理者，一旦你已经启动了程序，你就做好安全管理的准备。可以通过定义以下的特性而实现： 用户(帐号)：一个SQL Serve安全帐号代表着一个唯一的用户。一个用户也只有一个Windows帐号或者一个SQL Server注册，这与数据库中的用户帐号相统一。 组(帐号)：每一个用户都属于一个或多个组，这由认证模式决定。每一个组都具有特定许可。作为一个组的成员，你将获得所有组的许可。 对象所有权：所有权属于建立对象的用户。所有者可以将访问权限分配给用户。如果你是一个视的所有者，你还可以决定哪些用户可以通过视来查看数据。 许可：一个许可代表着具有执行某些操作的权利，比如打开一个视或者更改一个存储程序。SQL Server承认许可的三种状态：GRANT给你一个用户访问;REVOKE删除访问;DENY防止用户访问对象。 任务：这是一个SQL Server安全帐号，可以将帐号的集合作为一个简单的单元来处理。任务定义在特定数据库中用户可以做哪些和哪些不可以做。 从安全的角度而考虑 设计过程应该用效地定义哪些地方需要进行安全设置和如何设置。在这一过程之前你应该从两个方面考虑： 敏感数据; 可以查看敏感数据的人。 敏感数据包括所有可能的数据，包括整个数据库中的所有数据，虽然这样的安全级别很少存在。你的工作就是定义为敏感数据并进行保护。 你选择的认证模式和建立的注册将通过限制哪些用户可以进入到数据库而实施第一步安全步骤。 第二步就要列举可以访问数据库的所有用户，然后决定所有数据是否对所有的用户都适用。通常，你需要对一些数据进行保护，比如工资或者其他私人数据。这就意味着只有特定的用户可以访问和查看数据。你还可以设置哪些用户更改数据。 始终记住的一条规则是“最小权利”概念。如果有人在他的工作中不需要访问数据，那即不要给他访问的权限。应该避免所有的用户都具有sa用户的访问权限。 具体的建议 当进行安全设置的时候，经验也是一个很好的老师，但通常也会有适用于通用数据库的设计准则： 从开始就获得数据库和对象的所有权。当建立一个新的数据库时，你将会成为数据库的所有者，并能够设置数据库中所有发生一切。你可以以管理者的身份注册数据库。然而，对象的所有权属于建立对象的用户。虽然这可能造成所有权的转移，但可以确定这些的注册能够被用于建立所有的对象。 理解所有权链。这一安全特性防止用户建立自己的视而偷看一些敏感数据。例如，假设你建立一个从两个表中集中的数据的视，如果你是这两个表的所有者，当你允许其他用户使用视的许可时，SQL Server不会检查表的有关许可。 使用视和存储程序以分配给用户访问数据的权利，而不是让用户编写一些直接访问表格的特别查询语句。通过这种方式，你无需在表格中将访问权利分配给用户。视和存储程序也可以限制查看的数据。例如，如果你的雇员表格包含一些秘密的工资信息，你可以建立一个省略了工资栏的视。 如果用户从特定程序中进入你的程序，你可以建立程序任务。一个程序任务就是分配到特定程序的用户，并给予用户的有关许可。使用程序任务，用户不能直接地认证数据库，相反，他们先认证他们自己的程序，这就决定哪些程序任务与服务器相连接。 时刻注意程序补丁。不可否认的，程序补丁是一些诀窍的集合。程序的发布，更新等都会引入新的问题，使用程序补丁是防止外界干扰和保护数据的最好和最容易的方法。可以访问微软主页上的SQL Service Pack下载页，查看当前最新的程序补丁。 结论 程序安全机制是每一个开发人员都必须面临的问题。不要等到数据库开发过程中和使用中才考虑到安全问题──安全是设计过程中的重要组成部分。除此之外，不要随意地应用安全设置而希望达到最佳的效果，应该学会选择安全模式并正确地应用。SQL Server的安全设计就为大家讲解到这里，希望文章中的内容对您有帮助。

构造SQL Server的安全门

SQL Server的数据安全是数据库的重中之重，数据库安全门一定要完善好，做好安全保护工作，以下就是构造SQL Server的安全门的方法。 一、验证方法选择 本文对验证(authentication)和授权(authorization)这两个概念作不同的解释。验证是指检验用户的身份标识;授权是指允许用户做些什么。在本文的讨论中，验证过程在用户登录SQL Server的时候出现，授权过程在用户试图访问数据或执行命令的时候出现。 构造安全策略的第一个步骤是确定SQL Server用哪种方式验证用户。SQL Server的验证是把一组帐户、密码与Master数据库Sysxlogins表中的一个清单进行匹配。Windows NT/2000的验证是请求域控制器检查用户身份的合法性。一般地，如果服务器可以访问域控制器，我们应该使用Windows NT/2000验证。域控制器可以是Win2K服务器，也可以是NT服务器。无论在哪种情况下，SQL Server都接收到一个访问标记(Access Token)。访问标记是在验证过程中构造出来的一个特殊列表，其中包含了用户的SID(安全标识号)以及一系列用户所在组的SID。正如本文后面所介绍的，SQL Server以这些SID为基础授予访问权限。注意，操作系统如何构造访问标记并不重要，SQL Server只使用访问标记中的SID。也就是说，不论你使用SQL Server 2000、SQL Server 7.0、Win2K还是NT进行验证都无关紧要，结果都一样。 如果使用SQL Server验证的登录，它最大的好处是很容易通过Enterprise Manager实现，最大的缺点在于SQL Server验证的登录只对特定的服务器有效，也就是说，在一个多服务器的环境中管理比较困难。使用SQL Server进行验证的第二个重要的缺点是，对于每一个数据库，我们必须分别地为它管理权限。如果某个用户对两个数据库有相同的权限要求，我们必须手工设置两个数据库的权限，或者编写脚本设置权限。如果用户数量较少，比如25个以下，而且这些用户的权限变化不是很频繁，SQL Server验证的登录或许适用。但是，在几乎所有的其他情况下(有一些例外情况，例如直接管理安全问题的应用)，这种登录方式的管理负担将超过它的优点。 二、Web环境中的验证 即使最好的安全策略也常常在一种情形前屈服，这种情形就是在Web应用中使用SQL Server的数据。在这种情形下，进行验证的典型方法是把一组SQL Server登录名称和密码嵌入到Web服务器上运行的程序，比如ASP页面或者CGI脚本;然后，由Web服务器负责验证用户，应用程序则使用它自己的登录帐户(或者是系统管理员sa帐户，或者为了方便起见，使用Sysadmin服务器角色中的登录帐户)为用户访问数据。 这种安排有几个缺点，其中最重要的包括：它不具备对用户在服务器上的活动进行审核的能力，完全依赖于Web应用程序实现用户验证，当SQL Server需要限定用户权限时不同的用户之间不易区别。如果你使用的是IIS 5.0或者IIS 4.0，你可以用四种方法验证用户。 第一种方法是为每一个网站和每一个虚拟目录创建一个匿名用户的NT帐户。此后，所有应用程序登录SQL Server时都使用该安全环境。我们可以通过授予NT匿名帐户合适的权限，改进审核和验证功能。 第二种方法是让所有网站使用Basic验证。此时，只有当用户在对话框中输入了合法的帐户和密码，IIS才会允许他们访问页面。IIS依靠一个NT安全数据库实现登录身份验证，NT安全数据库既可以在本地服务器上，也可以在域控制器上。当用户运行一个访问SQL Server数据库的程序或者脚本时，IIS把用户为了浏览页面而提供的身份信息发送给服务器。如果你使用这种方法，应该记住：在通常情况下，浏览器与服务器之间的密码传送一般是不加密的，对于那些使用Basic验证而安全又很重要的网站，你必须实现SSL(Secure Sockets Layer，安全套接字层)。 在客户端只使用IE 5.0、IE 4.0、IE 3.0浏览器的情况下，你可以使用第三种验证方法。你可以在Web网站上和虚拟目录上都启用NT验证。IE会把用户登录计算机的身份信息发送给IIS，当该用户试图登录SQL Server时IIS就使用这些登录信息。使用这种简化的方法时，我们可以在一个远程网站的域上对用户身份进行验证(该远程网站登录到一个与运行着Web服务器的域有着信任关系的域)。 最后，如果用户都有个人数字证书，你可以把那些证书映射到本地域的NT帐户上。个人数字证书与服务器数字证书以同样的技术为基础，它证明用户身份标识的合法性，所以可以取代NT的Challenge/Response(质询/回应)验证算法。Netscape和IE都自动在每一个页面请求中把证书信息发送给IIS。IIS提供了一个让管理员把证书映射到NT帐户的工具。因此，我们可以用数字证书取代通常的提供帐户名字和密码的登录过程。 由此可见，通过NT帐户验证用户时我们可以使用多种实现方法。即使当用户通过IIS跨越Internet连接SQL Server时，选择仍旧存在。因此，你应该把NT验证作为首选的用户身份验证办法。 三、设置全局组 构造安全策略的下一个步骤是确定用户应该属于什么组。通常，每一个组织或应用程序的用户都可以按照他们对数据的特定访问要求分成许多类别。例如，会计应用软件的用户一般包括：数据输入操作员，数据输入管理员，报表编写员，会计师，审计员，财务经理等。每一组用户都有不同的数据库访问要求。 控制数据访问权限最简单的方法是，对于每一组用户，分别地为它创建一个满足该组用户权限要求的、域内全局有效的组。我们既可以为每一个应用分别创建组，也可以创建适用于整个企业的、涵盖广泛用户类别的组。然而，如果你想要能够精确地了解组成员可以做些什么，为每一个应用程序分别创建组是一种较好的选择。例如，在前面的会计系统中，我们应该创建Data Entry Operators、Accounting Data Entry Managers等组。请记住，为了简化管理，最好为组取一个能够明确表示出作用的名字。 除了面向特定应用程序的组之外，我们还需要几个基本组。基本组的成员负责管理服务器。按照习惯，我们可以创建下面这些基本组：SQL Server Administrators，SQL Server Users，SQL Server Denied Users，SQL Server DB Creators，SQL Server Security Operators，SQL Server Database Security Operators，SQL Server Developers，以及 DB_Name Users(其中DB_Name是服务器上一个数据库的名字)。当然，如果必要的话，你还可以创建其他组。 创建了全局组之后，接下来我们可以授予它们访问SQL Server的权限。首先为SQL Server Users创建一个NT验证的登录并授予它登录权限，把Master数据库设置为它的默认数据库，但不要授予它访问任何其他数据库的权限，也不要把这个登录帐户设置为任何服务器角色的成员。接着再为SQL Server Denied Users重复这个过程，但这次要拒绝登录访问。在SQL Server中，拒绝权限始终优先。创建了这两个组之后，我们就有了一种允许或拒绝用户访问服务器的便捷方法。 为那些没有直接在Sysxlogins系统表里面登记的组授权时，我们不能使用Enterpris Managr，因为Enterprise Manager只允许我们从现有登录名字的列表选择，而不是域内所有组的列表。要访问所有的组，请打开Query […]

如何在SQL Server数据库中成批导入数据

导读：在软件项目实施的时候，数据导入一直是项目人员比较头疼的问题。其实，在SQL Server中集成了很多成批导入数据的方法。有些项目实施顾问头疼的问题，在我们数据库管理员眼中，是小菜一碟。现在的重点就是，如何让用户了解这些方法，让数据导入变得轻松一些。下文介绍几种方法，让数据导入不在麻烦。 第一种方法：使用Select Into语句 若企业数据库都采用的是SQL Server数据库的话，则可以利用Select Into语句来实现数据的导入。Select Into语句，他的作用就是把数据从另外一个数据库中查询出来，然后加入到某个用户指定的表中。 　在使用这条语句的时候，需要注意几个方面的内容。 一是需要在目的数据库中先建立相关的表。如想把进销存系统数据库(SQLServer)中的产品信息表(Product)导入到ERP系统中的产品信息表(M_Product)中。则前期是在ERP系统的数据库中已经建立了这张产品信息表。 二是这种方法只复制表中的数据，而不复制表中的索引。如在进销存系统数据中的产品信息表中，在产品编号、产品种类等字段上建立了索引。则利用Select Into语句把数据复制到ERP系统的表中的时候，只是复制了数据内容的本身，而不会复制索引等信息。 三是这条语句使用具有局限性。一般情况下，这只能够在SQL Server数据库中采用。不过，对于SQL Server不同版本的数据库，如2008或者2003，还都是兼容的。若需要导入的对象数据库不是SQL Server的，则需要采用其他的方法。 四是采用这条语句的话，在目的表中必须不存在数据。否则的话，目的表中的数据会被清除。也就是说，这个语句不支持表与表数据的合并。在SQL Server中，有一条类似的语句，可以实现这个功能。这条语句就是：Insert Into。他的作用就是把另外一张表中的数据插入到当前表中。若用户想要的时表与表数据的合并，则可以采用这条语句。两者不能够混淆使用，否则的话，很容易导致数据的丢失。 五是以上两条语句都支持兼容的不同类型的数据类型。如在原标中，某个字段的数据类型是整数型，但是在目的表中这个字段的数据类型则是浮点型，只要这个两个数据类型本来就兼容的，则在导入的时候，数据库是允许的。 第二种方法：利用Excel等中间工具进行控制 虽然第一种方法操作起来比较简单，但是其也有一些缺点。如他只支持同一种类型的数据库;不能够对数据进行过多的干预等等。一般情况下，若用户原数据准确度比较高，不需要过多的修改就可以直接拿来用的话，则笔者就已采用第一种方式。 但是，若在原数据库中，数据的准确度不是很高，又或者，有很多数据是报废的。总之，需要对原数据库的数据进行整理，才能够使用的情况，笔者不建议先导入进去，再进行更改。笔者在遇到这种情况时，喜欢利用Excle作为中间工具。也就是说，先把数据中原数据库中导到Excle中。有些数据库，如Oracle数据库，他不支持Excle格式。但是，我们可以把它导为CSV格式的文件。这种文件Excle也可以打得开。 然后，再在Excle中，对记录进行修改。由于Excle是一个很强的表格处理软件，所以，其数据修改，要比在数据库中直接修改来得方便，来得简单。如可以利用按时间排序等功能，把一些长久不用的记录清楚掉。也可以利用替换等功能，把一些不规范的字符更改掉。这些原来在数据库中比较复杂的任务，在Excle等工具中都可以轻松的完成。 等到表中的内容修改无误后，数据库管理员就可以把Excle表格中的文件直接导入到SQL Server数据库中。由于SQL Server与Excel是同一个父母生的，所以，他们之间的兼容性很好。在Sql Server中提供了直接从Excel文件中导入数据的工具。 虽然这要借助中间工具导入数据，但是，因为其处理起来方便、直观，所以，笔者在大部分时候都是采用这种方式。 第三种方式：使用数据转换服务导入数据 数据转换服务是SQL Server数据库中提供的一个非常强大的工具。在SQLServer中，数据转换功能有一个图形用户接口，用户可以在图形界面中导入数据，并对数据进行相应的编辑。 另外，数据转换服务还支持COM组件的编程接口。这也就是说，在前台应用程序开发的时候，可以直接调用数据转换服务。让用户通过前台应用系统，而不用在后台数据库系统进行任何的操作，就可以把数据导入数据库系统中去。在前台对数据库系统进行导入，有一个明显的好处，就可以预先对数据的合法性进行检查。如可以利用VB等脚本语言对数据进行检验、净化和一定的转换，以符合目的数据库的需要。 如在员工信息表中的婚姻状况字段，在Oracle数据库系统中，可能是用0或者1来表示婚姻状况。0表示未婚，1表示已婚。而在SQL Server数据库中，则利用Y或者N来表示婚姻状况。Y表示已婚，N表示未婚。在导入数据的时候，若直接把Oracle数据库表中的数据导入到SQL Server数据库中，因为婚姻状况这个字段存储的内容类型不同，所以，不能够直接导。遇到这种情况的话，则就可以在导入数据之前，先利用脚本语言对数据类型进行验证。若不符合要求的，则可以通过脚本语言对数据进行一定的转换，把0转换为N，把1转换为Y等等。 所以，有时候程序员在开发前台应用程序的时候，若要开发数据导入功能的话，我们都是建议采用这个数据转换服务。不但有现成的接口，而且，还可以对数据进行验证与一定程度的转换。另外，数据转换服务的数据导入效率非常的高。即使通过前台程序调用，其性能也比其他方法在同等条件下，要高一个档次。而且，随着数据量的增加，数据转换服务的优势会越来越明显。 不过，在前台应用程序调用数据转换服务的时候，需要注意。数据转换服务提供的COM接口比较复杂，所以，前台程序调用数据转换服务的代码也比较复杂。若再加上一些脚本语言的话，可能处理起来更加的繁琐。故一般只有在大型系统上才会用到这个接口。若数据不多，否则不需要复杂验证与转换的话，利用这个接口是大刀小用，得不偿失。 第四种方式：异构数据库之间的导入导出 虽然第二种、第三种方式都可以完成异构数据库之间数据的导入导出作业。不过，在SQL Server中，还提供了另外一种解决方案。即直接在SQL Server数据库中连接到其他类型的数据库上，然后采用Select Into等语句实现数据的导入作业。 在SQL Server中，提供了两个函数可以帮助我们实现对非SQL Server数据库的连接。这两个函数分别为Opendatesource与Openrowset。他们的功能基本相同，只是在细节上有所差异。 如Opendatesource这个函数至能够打开源数据库的表和视图，而不能够对其进行过滤。若用户只想把源表中的部分数据导入到SQL Server数据库的表中，则不能对源表直接进行过滤。过滤的动作需要在SQL Server数据库中进行。而Openrowset这个函数，可以在打开对方数据库的表或者视图的时候，直接利用Where等条件限制语句对记录进新过滤。为此，在实际应用中，还是Openrowset这个函数使用的频率比较高。 不过由于其需要用户写复杂的参数，而且，又不能够提供复杂的数据验证功能，所以在实际工作中用的并不是很多。在一些小的应用系统中，偶尔还可以见到其的踪影。在一些大的成熟的商业软件中，很少采用这种方式，对数据进行导入。 有时候，选择多了，用户反而不知道如何下手。笔者平时最喜欢采用的是第二种处理方式。他比较直观，而且，可以对数据进行成批的更改与整理。但是，其缺陷就是效率比较低，特别是Excle软件对于处理大量记录的时候，速度比较慢。若这种方式行不通的话，则笔者比较倾向于采用数据转换的处理方式。这个操作起来虽然比较复杂，但是，其可以提供比较复杂的验证，而且可以在图形化的界面中对数据进行修改，同时效率也比较高。 学会了上文中介绍的四种方法，数据导入将会变的容易很多，很高兴与大家分享这些方法，希望对大家有用。

减少SQL Server数据库死锁的技巧

导读：如果两个用户进程分别锁定了不同的资源，接着又试图锁定对方所锁定的资源，就会产生死锁。此时，SQL Server数据库将自动地选择并中止其中一个进程以解除死锁，使得另外一个进程能够继续处理。系统将回退被中止的事务，并向被回退事务的用户发送错误信息。 　大多数设计良好的应用都会在接收到这个错误信息之后重新提交该事务，此时提交成功的可能性是很大的。但是，如果服务器上经常出现这种情况，就会显著地降低服务器性能。由此可知，减少SQL Server数据库死锁是非常有必要的。 为避免死锁，设计应用应当遵循一定的原则，包括： 让应用每次都以相同的次序访问服务器资源。 　 在事务期间禁止任何用户输入。应当在事务开始之前收集用户输入。 尽量保持事务的短小和简单。 如合适的话，为运行事务的用户连接指定尽可能低的隔离级别。[适用于6.5，7.0，2000] 此外，对于SQL Server的死锁问题，下面是几则实践中很有用的小技巧。 使用SQL Server Profiler的Create Trace Wizard运行“Identify The Cause of a Deadlock”跟踪来辅助识别死锁问题，它将提供帮助查找数据库产生死锁原因的原始数据。[适用于7.0，2000] 如果无法消除应用中的所有死锁，请确保提供了这样一种程序逻辑：它能够在死锁出现并中止用户事务之后，以随机的时间间隔自动重新提交事务。这里等待时间的随机性非常重要，这是因为另一个竞争的事务也可能在等待，我们不应该让两个竞争的事务等待同样的时间，然后再在同一时间执行它们，这样的话将导致新的死锁。[适用于6.5，7.0，2000] 尽可能地简化所有T-SQL事务。此举将减少各种类型的锁的数量，有助于提高SQL Server应用的整体性能。如果可能的话，应将较复杂的事务分割成多个较简单的事务。[适用于6.5，7.0，2000] 所有条件逻辑、变量赋值以及其他相关的预备设置操作应当在事务之外完成，而不应该放到事务之内。永远不要为了接受用户输入而暂停某个事务，用户输入应当总是在事务之外完成。[适用于6.5，7.0，2000] 在存储过程内封装所有事务，包括BEGIN TRANSACTION和COMMIT TRANSACTION语句。此举从两个方面帮助减少阻塞的锁。首先，它限制了事务运行时客户程序和SQL Server之间的通信，从而使得两者之间的任何消息只能出现于非事务运行时间(减少了事务运行的时间)。其次，由于存储过程强制它所启动的事务或者完成、或者中止，从而防止了用户留下未完成的事务(留下未撤销的锁)。[适用于6.5，7.0，2000] 如果客户程序需要先用一定的时间检查数据，然后可能更新数据，也可能不更新数据，那么最好不要在整个记录检查期间都锁定记录。假设大部分时间都是检查数据而不是更新数据，那么处理这种特殊情况的一种方法就是：先选择出记录(不加UPDATE子句。UPDATE子句将在记录上加上共享锁)，然后把它发送给客户。 如果用户只查看记录但从来不更新它，程序可以什么也不做;反过来，如果用户决定更新某个记录，那么他可以通过一个WHERE子句检查当前的数据是否和以前提取的数据相同，然后执行UPDATE。 类似地，我们还可以检查记录中的时间标识列(如果它存在的话)。如果数据相同，则执行UPDATE操作;如果记录已经改变，则应用应该提示用户以便用户决定如何处理。虽然这种方法需要编写更多的代码，但它能够减少加锁时间和次数，提高应用的整体性能。[适用于6.5，7.0，2000] 尽可能地为用户连接指定具有最少限制的事务隔离级别，而不是总是使用默认的READ COMMITTED。为了避免由此产生任何其他问题，应当参考不同隔离级别将产生的效果，仔细地分析事务的特性。[适用于6.5，7.0，2000] 使用游标会降低并发性。为避免这一点，如果可以使用只读的游标则应该使用READ_ONLY游标选项，否则如果需要进行更新，尝试使用OPTIMISTIC游标选项以减少加锁。设法避免使用SCROLL_LOCKS游标选项，该选项会增加由于记录锁定引起的问题。[适用于6.5，7.0，2000] 如果用户抱怨说他们不得不等待系统完成事务，则应当检查服务器上的资源锁定是否是导致该问题的原因。进行此类检查时可以使用SQL Server Locks Object: Average Wait Time (ms)，用该计数器来度量各种锁的平均等待时间。 如果可以确定一种或几种类型的锁导致了事务延迟，就可以进一步探究是否可以确定具体是哪个事务产生了这种锁。Profiler是进行这类具体分析的最好工具。[适用于7.0，2000] 使用sp_who和sp_who2(SQL Server Books Online没有关于sp_who2的说明，但sp_who2提供了比sp_who更详细的信息)来确定可能是哪些用户阻塞了其他用户。[适用于6.5，7.0，2000] 试试下面的一个或多个有助于避免阻塞锁的建议：1)对于频繁使用的表使用集簇化的索引;2)设法避免一次性影响大量记录的T-SQL语句，特别是INSERT和UPDATE语句;3)设法让UPDATE和DELETE语句使用索引;4)使用嵌套事务时，避免提交和回退冲突。[适用于6.5，7.0，2000] 这就是我要为大家介绍的减少SQL Server数据库死锁的小技巧，学会了这些技巧，在以后的操作中将会更加方便快捷。 原文链接：http://database.51cto.com/art/201103/247849.htm

SQL server安全指导

SQL server功能的强大性，安全问题首当前冲，众所周知，安全性问题一直是DBA比较关心的问题，因为建立数据库的目的就是让相关的的客户端来进行访问，所以很难避免不出现安全隐患，例如客户端链接的权限、数据传输过程中的安全等问题，所以大家在考虑SQL Server服务器安全的问题。 客户端 客户端安全：首先需要保证客户端必须是安全的，例如需要为你的客户端安装防病毒软件，防火墙，安装升级补丁等。 数据传输 数据传输过程中的安全：数据在一般的传输过程中，很容易被监听或被捕获，所以大家应该使用加密机制来保证数据的机密性。 安全机制 需要在数据库服务器上实现安全机制以此来保证安全性。SQL Server数据库是一个非常安全的数据库，由于客户端如果想对数据库里的数据进行操作(select、insert等)，就必须经历三道检查，也就是我们要经过的三道门(登录验证、数据库验证、对象权限)。 备份密码 SQL Server中的一个选项就是创建用密码创建备份。这是你在创建备份的时候可以使用的另一个选择，但是在企业版管理器或者SQL Server管理套件中，并没有提供这个选项。这里是一个使用密码选项备份的例子: backup database northwind to disk=’c:\northwind.bak' with mediapassword = 'Backup2006' 这个过程需要密码来重新存储文件，但是使用文本编辑器，这些数据仍然是可以访问的。还有，重新存储不能使用GUI来完成，所以它必须通过T-SQL重新存储命令和密码一起完成任务。 假如我们把SQL Server服务器看成一座大厦，我们首先要现有权利进入大厦，即通过大厦的大门(登录验证)，然后还得拥有对某个房间的访问权限(对数据库的权限)，进入房间后还得拥有打开保险柜的权利(对表的操作权限)。也就是首先我们必须先建立登录帐户，而登录帐户分为两种：windows帐户和SQL帐户(因为SQL Server有两种身份验证，即windows身份验证和混合身份验证)。 具体操作方法：打开“SSMS—SQL Server实例—安全性—登录名”，右键选择“新建登录名”，选择身份验证模式(身份验证模式不同，帐户类型也不一样，注意：建立windows登录帐户，必须先在windows操作系统上先建立该账户)，输入名字，并为该用户选择一个默认数据库(比如默认为master数据库)。该账户建立好之后，我们通过“新建”—“数据库引擎查询”的方式来测试用户，我们会发现该用户只能连接数据库，并不能对数据库进行任何其他操作。所以下面是第二道门，建立数据库用户，以便用户可以访问数据库，对数据库进行操作。我们在建立数据库用户时，其实就是映射登录用户，所以在一般情况下，我们的登录名和数据库用户名是一致的。 操作方法：第一种：打开“SSMS—SQL Server实例—具体的数据库—安全性—数据库用户”;第二种：直接在以前建立过的登录用户上映射数据库就可以了。最后一道门是在具体的数据库对象(比如表)上授予具体的权限，三种权限如下：授予、回收和拒绝。  SQL server的安全性问题不可马虎忽略，为了确保SQL server中数据信息的安全，一定要做好安全工作方面的工作，比如安全审计等等。

SQL server的一道入门面试题背后的思考

最近看到一个SQL Server的小例子，发现完全可以作为SQL server的一道入门面试题。题目如下： 例：有一合同表Contract Id Name Total buget 1 合同名称 100  102,22 2 合同名称2 300 ,102,22, 3 合同名称3 200  103,23, 要求:用SQL语句更新表的buget字段，如果前后没有","要加上","（即一个英文逗号）。（10分） 创建表数据： View Code

 分析：这道题乍看很简单，由于肯定用到Replace，所以很自然的结合left,right,从而得到以下语句 方法一：

如果能写成一个 SQL语句，可以加1分。

如果能从字符串的开关和结尾这个思路出发，结合Reverse，可以提到如下方法： 方法二：

该方法，主要涉及charindex函数和reverse函数。 说实话，我当时就这两种思路，这也是SQL中常见的基本用法。但出人意料的第三种方法出现了。 方法三：

解析：该方法最主要的亮点不在于语法的精妙，而在于其思路的异于常规。先给两边补上逗号，再替换双逗号为单逗号。这在实际编程中确实难能可贵。换句话说，如果没有事先思考过的话，这反映了解题者反应敏捷，思路开放。因此，至少可以再加3分。 当然，此语句其实还是有bug,比如如果原bug字段中间有两个逗号，那么在Replace时就会更新掉不应该更新的内容。不过，稍加修正，限定replace的范围即可， 受此思路启发，可以引申得到以下类似方法： 方法四：

该方法是先去掉两边的逗号，再给每条记录加上逗号，比起方法三来，稍显繁琐，这也反衬了方法三的巧妙。 当然，也可以结合前面的思路稍作修正，这里就不再赘述，请读者自己思考。 感悟：释迦牟尼说过“人生需要经过六项修炼：布施、持戒、忍辱、精进、禅定、智慧。”，SQL编程，或C#、Java，甚至Javascrip的某个领域也是如此。技术是死的，思路是鲜活的，有时候，思路能轻易地突破技术很难实现的死角。到了一定程度时，会发现潜意识里已经被惯性思维塞满，而无法接受新鲜思维方式或思路，如果一段时间内持续如此，那么，我们应该警醒，把自己的头脑放空，把自己置于一个初学者的地位，重新开始“精进”的修炼！ 原文链接：http://www.cnblogs.com/downmoon/archive/2011/03/02/1968615.html

写出高性能SQL语句的十三条法则

1、 首先要搞明白什么叫执行计划？ 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的，比如一条SQL语句如果用来从一个10万条记录的表中查1条记录，那查询优化器会选择“索引查找”方式，如果该表进行了归档，当前只剩下5000条记录了，那查询优化器就会改变方案，采用“全表扫描”方式。 可见，执行计划并不是固定的，它是“个性化的”。产生一个正确的“执行计划”有两点很重要： (1)    SQL语句是否清晰地告诉查询优化器它想干什么？ (2)    查询优化器得到的数据库统计信息是否是最新的、正确的？ 2、 统一SQL语句的写法 对于以下两句SQL语句，程序员认为是相同的，数据库查询优化器认为是不同的。

其实就是大小写不同，查询分析器就认为是两句不同的SQL语句，必须进行两次解析。生成2个执行计划。所以作为程序员，应该保证相同的查询语句在任何地方都一致，多一个空格都不行！ 3、 不要把SQL语句写得太复杂 我经常看到，从数据库中捕捉到的一条SQL语句打印出来有2张A4纸这么长。一般来说这么复杂的语句通常都是有问题的。我拿着这2页长的SQL语句去请教原作者，结果他说时间太长，他一时也看不懂了。可想而知，连原作者都有可能看糊涂的SQL语句，数据库也一样会看糊涂。 一般，将一个Select语句的结果作为子集，然后从该子集中再进行查询，这种一层嵌套语句还是比较常见的，但是根据经验，超过3层嵌套，查询优化器就很容易给出错误的执行计划。因为它被绕晕了。像这种类似人工智能的东西，终究比人的分辨力要差些，如果人都看晕了，我可以保证数据库也会晕的。 另外，执行计划是可以被重用的，越简单的SQL语句被重用的可能性越高。而复杂的SQL语句只要有一个字符发生变化就必须重新解析，然后再把这一大堆垃圾塞在内存里。可想而知，数据库的效率会何等低下。 4、 使用“临时表”暂存中间结果 简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。 5、 OLTP系统SQL语句必须采用绑定变量

以上两句语句，查询优化器认为是不同的SQL语句，需要解析两次。如果采用绑定变量

@chgtime变量可以传入任何值，这样大量的类似查询可以重用该执行计划了，这可以大大降低数据库解析SQL语句的负担。一次解析，多次重用，是提高数据库效率的原则。 6、 绑定变量窥测 事物都存在两面性，绑定变量对大多数OLTP处理是适用的，但是也有例外。比如在where条件中的字段是“倾斜字段”的时候。 “倾斜字段”指该列中的绝大多数的值都是相同的，比如一张人口调查表，其中“民族”这列，90%以上都是汉族。那么如果一个SQL语句要查询30岁的汉族人口有多少，那“民族”这列必然要被放在where条件中。这个时候如果采用绑定变量@nation会存在很大问题。 试想如果@nation传入的第一个值是“汉族”，那整个执行计划必然会选择表扫描。然后，第二个值传入的是“布依族”，按理说“布依族”占的比例可能只有万分之一，应该采用索引查找。但是，由于重用了第一次解析的“汉族”的那个执行计划，那么第二次也将采用表扫描方式。这个问题就是著名的“绑定变量窥测”，建议对于“倾斜字段”不要采用绑定变量。 7、 只在必要的情况下才使用begin tran SQL Server中一句SQL语句默认就是一个事务，在该语句执行完成后也是默认commit的。其实，这就是begin tran的一个最小化的形式，好比在每句语句开头隐含了一个begin tran，结束时隐含了一个commit。 有些情况下，我们需要显式声明begin tran，比如做“插、删、改”操作需要同时修改几个表，要求要么几个表都修改成功，要么都不成功。begin tran 可以起到这样的作用，它可以把若干SQL语句套在一起执行，最后再一起commit。好处是保证了数据的一致性，但任何事情都不是完美无缺的。Begin tran付出的代价是在提交之前，所有SQL语句锁住的资源都不能释放，直到commit掉。 可见，如果Begin tran套住的SQL语句太多，那数据库的性能就糟糕了。在该大事务提交之前，必然会阻塞别的语句，造成block很多。 Begin tran使用的原则是，在保证数据一致性的前提下，begin tran 套住的SQL语句越少越好！有些情况下可以采用触发器同步数据，不一定要用begin tran。 8、 一些SQL查询语句应加上nolock 在SQL语句中加nolock是提高SQL Server并发性能的重要手段，在oracle中并不需要这样做，因为oracle的结构更为合理，有undo表空间保存“数据前影”，该数据如果在修改中还未commit，那么你读到的是它修改之前的副本，该副本放在undo表空间中。这样，oracle的读、写可以做到互不影响，这也是oracle广受称赞的地方。SQL Server 的读、写是会相互阻塞的，为了提高并发性能，对于一些查询，可以加上nolock，这样读的时候可以允许写，但缺点是可能读到未提交的脏数据。使用nolock有3条原则。 (1)    查询的结果用于“插、删、改”的不能加nolock ！ (2)    查询的表属于频繁发生页分裂的，慎用nolock ！ (3)    使用临时表一样可以保存“数据前影”，起到类似oracle的undo表空间的功能， 能采用临时表提高并发性能的，不要用nolock 。 9、 聚集索引没有建在表的顺序字段上，该表容易发生页分裂 比如订单表，有订单编号orderid，也有客户编号contactid，那么聚集索引应该加在哪个字段上呢？对于该表，订单编号是顺序添加的，如果在orderid上加聚集索引，新增的行都是添加在末尾，这样不容易经常产生页分裂。然而，由于大多数查询都是根据客户编号来查的，因此，将聚集索引加在contactid上才有意义。而contactid对于订单表而言，并非顺序字段。 比如“张三”的“contactid”是001，那么“张三”的订单信息必须都放在这张表的第一个数据页上，如果今天“张三”新下了一个订单，那该订单信息不能放在表的最后一页，而是第一页！如果第一页放满了呢？很抱歉，该表所有数据都要往后移动为这条记录腾地方。 SQL Server的索引和Oracle的索引是不同的，SQL Server的聚集索引实际上是对表按照聚集索引字段的顺序进行了排序，相当于oracle的索引组织表。SQL Server的聚集索引就是表本身的一种组织形式，所以它的效率是非常高的。也正因为此，插入一条记录，它的位置不是随便放的，而是要按照顺序放在该放的数据页，如果那个数据页没有空间了，就引起了页分裂。所以很显然，聚集索引没有建在表的顺序字段上，该表容易发生页分裂。 曾经碰到过一个情况，一位哥们的某张表重建索引后，插入的效率大幅下降了。估计情况大概是这样的。该表的聚集索引可能没有建在表的顺序字段上，该表经常被归档，所以该表的数据是以一种稀疏状态存在的。比如张三下过20张订单，而最近3个月的订单只有5张，归档策略是保留3个月数据，那么张三过去的15张订单已经被归档，留下15个空位，可以在insert发生时重新被利用。在这种情况下由于有空位可以利用，就不会发生页分裂。但是查询性能会比较低，因为查询时必须扫描那些没有数据的空位。 重建聚集索引后情况改变了，因为重建聚集索引就是把表中的数据重新排列一遍，原来的空位没有了，而页的填充率又很高，插入数据经常要发生页分裂，所以性能大幅下降。 对于聚集索引没有建在顺序字段上的表，是否要给与比较低的页填充率？是否要避免重建聚集索引？是一个值得考虑的问题！ 10、加nolock后查询经常发生页分裂的表，容易产生跳读或重复读 加nolock后可以在“插、删、改”的同时进行查询，但是由于同时发生“插、删、改”，在某些情况下，一旦该数据页满了，那么页分裂不可避免，而此时nolock的查询正在发生，比如在第100页已经读过的记录，可能会因为页分裂而分到第101页，这有可能使得nolock查询在读101页时重复读到该条数据，产生“重复读”。同理，如果在100页上的数据还没被读到就分到99页去了，那nolock查询有可能会漏过该记录，产生“跳读”。 上面提到的哥们，在加了nolock后一些操作出现报错，估计有可能因为nolock查询产生了重复读，2条相同的记录去插入别的表，当然会发生主键冲突。 11、使用like进行模糊查询时应注意 有的时候会需要进行一些模糊查询比如

关键词%yue%，由于yue前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%， 12、数据类型的隐式转换对查询效率的影响 sql server2000的数据库，我们的程序在提交sql语句的时候，没有使用强类型提交这个字段的值，由sql […]

SQL中Case的使用方法

Case具有两种格式。简单Case函数和Case搜索函数。 —简单Case函数 CASE sex          WHEN '1' THEN '男'          WHEN '2' THEN '女' ELSE '其他' END --Case搜索函数 CASE WHEN sex = '1' THEN '男'          WHEN sex = '2' THEN '女' ELSE '其他' END     这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。 还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。   —比如说，下面这段SQL，你永远无法得到“第二类”这个结果 CASE WHEN col_1 IN ( 'a', 'b') THEN '第一类'          WHEN col_1 IN ('a')       THEN '第二类' ELSE'其他' END   下面我们来看一下，使用Case函数都能做些什么事情。   一，已知数据按照另外一种方式进行分组，分析。   有如下数据为了看得更清楚，我并没有使用国家代码，而是直接用国家名作为Primary Key) 国家（country）人口（population） 中国600 美国100 加拿大100 英国200 法国300 日本250 德国200 墨西哥50 印度250   根据这个国家人口数据，统计亚洲和北美洲的人口数量。应该得到下面这个结果。 洲人口 亚洲1100 北美洲250 其他700   想要解决这个问题，你会怎么做？生成一个带有洲Code的View，是一个解决方法，但是这样很难动态的改变统计的方式。 如果使用Case函数，SQL代码如下: SELECT SUM(population),         CASE […]

SQL Server 2000的安全配置

    数据库是电子商务、金融以及ERP系统的基础，通常都保存着重要的商业伙伴和客户信息。大多数企业、组织以及政府部门的电子数据都保存在各种数据库中，他们用这些数据库保存一些个人资料，比如员工薪水、个人资料等等。数据库服务器还掌握着敏感的金融数据。包括交易记录、商业事务和帐号数据，战略上的或者专业的信息，比如专利和工程数据，甚至市场计划等等应该保护起来防止竞争者和其他非法者获取的资料。数据完整性和合法存取会受到很多方面的安全威胁，包括密码策略、系统后门、数据库操作以及本身的安全方案。但是数据库通常没有象操作系统和网络这样在安全性上受到重视。     微软的SQL Server是一种广泛使用的数据库，很多电子商务网站、企业内部信息化平台等都是基于SQL Server上的，但是数据库的安全性还没有被人们更系统的安全性等同起来，多数管理员认为只要把网络和操作系统的安全搞好了，那么所有的应用程序也就安全了。大多数系统管理员对数据库不熟悉而数据库管理员有对安全问题关心太少，而且一些安全公司也忽略数据库安全，这就使数据库的安全问题更加严峻了。数据库系统中存在的安全漏洞和不当的配置通常会造成严重的后果，而且都难以发现。数据库应用程序通常同操作系统的最高管理员密切相关。广泛SQL Server数据库又是属于“端口”型的数据库，这就表示任何人都能够用分析工具试图连接到数据库上，从而绕过操作系统的安全机制，进而闯入系统、破坏和窃取数据资料，甚至破坏整个系统。     这里，我们主要谈论有关SQL Server2000数据库的安全配置以及一些相关的安全和使用上的问题。     在进行SQL Server 2000数据库的安全配置之前，首先你必须对操作系统进行安全配置，保证你的操作系统处于安全状态。然后对你要使用的操作数据库软件（程序）进行必要的安全审核，比如对ASP、PHP等脚本，这是很多基于数据库的WEB应用常出现的安全隐患，对于脚本主要是一个过滤问题，需要过滤一些类似 , ‘ ; @ / 等字符，防止破坏者构造恶意的SQL语句。接着，安装SQL Server2000后请打上补丁sp1以及最新的sp2。     在做完上面三步基础之后，我们再来讨论SQL Server的安全配置。     1、使用安全的密码策略     我们把密码策略摆在所有安全配置的第一步，请注意，很多数据库帐号的密码过于简单，这跟系统密码过于简单是一个道理。对于sa更应该注意，同时不要让sa帐号的密码写于应用程序或者脚本中。健壮的密码是安全的第一步！ SQL Server2000安装的时候，如果是使用混合模式，那么就需要输入sa的密码，除非你确认必须使用空密码。这比以前的版本有所改进。     同时养成定期修改密码的好习惯。数据库管理员应该定期查看是否有不符合密码要求的帐号。比如使用下面的SQL语句：     Use master     Select name,Password from syslogins where password is null     2、使用安全的帐号策略。     由于SQL Server不能更改sa用户名称，也不能删除这个超级用户，所以，我们必须对这个帐号进行最强的保护，当然，包括使用一个非常强壮的密码，最好不要在数据库应用中使用sa帐号，只有当没有其它方法登录到 SQL Server 实例（例如，当其它系统管理员不可用或忘记了密码）时才使用 sa。建议数据库管理员新建立一个拥有与sa一样权限的超级用户来管理数据库。安全的帐号策略还包括不要让管理员权限的帐号泛滥。 SQL Server的认证模式有Windows身份认证和混合身份认证两种。如果数据库管理员不希望操作系统管理员来通过操作系统登陆来接触数据库的话，可以在帐号管理中把系统帐号“BUILTINAdministrators”删除。不过这样做的结果是一旦sa帐号忘记密码的话，就没有办法来恢复了。     很多主机使用数据库应用只是用来做查询、修改等简单功能的，请根据实际需要分配帐号，并赋予仅仅能够满足应用要求和需要的权限。比如，只要查询功能的，那么就使用一个简单的public帐号能够select就可以了。     3、加强数据库日志的记录。     审核数据库登录事件的“失败和成功”，在实例属性中选择“安全性”，将其中的审核级别选定为全部，这样在数据库系统和操作系统日志里面，就详细记录了所有帐号的登录事件。     请定期查看SQL Server日志检查是否有可疑的登录事件发生，或者使用DOS命令。     findstr /C:"登录" d:Microsoft SQL ServerMSSQLLOG*.*     4、管理扩展存储过程     对存储过程进行大手术，并且对帐号调用扩展存储过程的权限要慎重。其实在多数应用中根本用不到多少系统的存储过程，而SQL Server的这么多系统存储过程只是用来适应广大用户需求的，所以请删除不必要的存储过程，因为有些系统的存储过程能很容易地被人利用起来提升权限或进行破坏。     如果你不需要扩展存储过程xp_cmdshell请把它去掉。使用这个SQL语句：     use master     sp_dropextendedproc 'xp_cmdshell'     xp_cmdshell是进入操作系统的最佳捷径，是数据库留给操作系统的一个大后门。如果你需要这个存储过程，请用这个语句也可以恢复过来。     sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll' […]