【IT168 专稿】如何优化应用程序的SQL Server编程
现在，应用程序和数据库设计应该已经完成，而且都使用快速原型技术进行了性能和可扩展性的测试。现在我们需要为应用程序编写与SQL Server协同的代码。
第一篇：SQL Server应用程序性能调优之硬件配置
第二篇：SQL Server应用程序性能调优之设计优化
如何进行应用程序编程，对性能和可扩展性也有很大影响，就如同数据库设计和整体应用程序设计对性能的影响一样。有的时候，选择一个更适合的简单编程技巧就可以带来较大的性能提高。实现一个任务的代码可能有很多种，不过获得最优性能的往往只有一个。
如何优化你的T-SQL代码
和任何编程语言一样，T-SQL提供了多种方式来实现同一个任务。其中有的方法所实现的性能要高于其它方法。在这一部分中，我将向大家介绍一些编写高性能T-SQL代码的诀窍。
选择合适的数据类型：数据类型选择好，可以大大提高SQL Server执行SELECT、INSERT、UPDATE和DELETE操作的速度。不过，选择最优的数据类型并不总是一件很简单的事情。在创建SQL Server物理表的时候，以下建议可以有助于获得最优性能。
选择能满足你需要的最小数据类型。例如，如果某一列需要存储的是数字1到10，那么该列的数据类型选择TINYINT会比INT更好。CHAR和VARCHAR的选择也是遵循同样的原则。另外，对于字符列的字符数不要设定太大，满足自己需要就可以，这样SQL Server能够在其数据和索引页面中存储更多行记录，降低读取它们时所需的I/O次数。另外，它将减少从服务器移动到客户端的数据量，降低网络流量和延时。
如果某一列的文本数据在长度上差别很大，使用VARCHAR数据类型来取代CHAR数据类型。尽管VARCHAR数据类型比CHAR数据类型的开销略微有些大，但是使用VARCHAR数据类型可以大大节省空间，可以降低I/O，提高整体SQL Server性能。
除非你需要存储Unicode数据，不要使用NVARCHAR或NCHAR数据类型。它们所占用的空间是VARCHAR或CHAR的两倍，可以增加服务器I/O开销。
如果你需要存储较大的字符串数据，而且它们不超过8000字符，那么最好使用VARCHAR数据类型，而不要使用TEXT数据类型。TEXT数据类型开销较大，会降低性能。
如果有一列只用来存储数字，使用数值型数据类型，诸如INTERGER，而不要使用VARCHAR或CHAR数据类型。Numeric数据类型一般会需要较小的空间来存储数值。这样有助于降低数据列的大小，而且当列内容被搜索或与其它列联合时，可以提高性能。
谨慎使用触发器
在T-SQL中，触发器是一个强大的工具，但是由于它们每次被执行的时候，需要对表进行INSERT、UPDATE或DELETE操作，这可能带来大量开销。以下是如何优化触发器性能的一些技巧。
保持触发器中的代码最精简以降低开销。触发器中运行的代码越多，它所进行的每一个INSERT、UPDATE和DELETE就会越慢。
不过某个任务可以使用更高效的技术实现，就不要使用触发器。
尽量不要使用回滚触发器，因为其相关开销太大。与其让触发器发现问题后对事务处理进行回滚操作，不如在它进入触发器之前就捕获该错误。与让触发器回滚相比，在触发器启动之前提前发现错误会消耗更少的服务器资源。
只访问所需要的数据
尽管这个建议听起来很简单，实现起来却很难，在很多SQL Server应用程序中，这是一个非常常见的性能相关的问题。通过以下思路，可以减少返回客户端的数据量。
不要返回非必须的更多行或列数据给客户端。这会加大磁盘I/O开销和网络流量，都会影响性能。在SELECT语句中，不要使用SELECT *来返回行数据，只根据你的需要来返回需要的列。在多数情况下，务必要包含WHERE条件子句来降低返回数值或行的数量。
如果你的应用允许用户运行查询，但是你又不能防止用户返回数千行、数万行非必须的数据，可以考虑在SELECT语句中使用TOP运算符。这样，你可以限制返回行的数量。
避免使用游标（Cursors）
T-SQL擅长处理结果集，而并非单条记录。这时候游标有了用武之地。它们让你可以处理单条记录。单条记录处理的唯一问题是非常慢。理想情况下，对于高性能SQL Server应用程序来说，应尽量避免使用游标。
如果你需要实现逐行操作，尽量使用另一种方法来完成这个任务。在客户端某些选项可以完成逐行操作的任务，例如使用服务器端的tempdb表，或者使用一个关联子查询。
不幸的是，这些方法并不通用，有时候你可能必须使用一个游标。如果你发现必须在你的应用程序中使用游标功能，那么以下这些建议或许对你有所帮助。
SQL Server为你提供了几个不同类型的游标，每一个都有不同的性能特点。你应该选择可以实现你的目的同时开销又最小的游标类型。你可以选择的最高效游标是快速只进游标。
在使用服务器端游标时，尽可能取最小的数据集。这包括只获取客户端立即需要的行和列。无论哪一种类型的服务器端游标，它越小，所使用的资源就越少，性能自然就越高。
当你使用完一个游标后，不要简单的CLOSE它，你还必须对其进行DEALLOCATE操作，这样才会释放该游标所使用的SQL Server资源。如果你只是关闭它，锁虽然被释放，但是SQL Server资源却并未被释放，因此会降低服务器的性能。
合理使用联合（Joins）
对表进行联合（join）是造成性能问题的另一个主要做法，尤其是在联合的表超过了3个或者表非常大的情况下，更是如此。
不幸的是，联合在关系数据库中是必不可少的。既然我们做不到不使用它，那么就看一下如何尽可能最优的使用它吧，请看以下技巧。
如果你有两个或更多表需要频繁的进行联合，那么用于联合的列应该具有一个合适的索引。如果用于联合的列不简洁，那么可以考虑想数据表中增加代理键，以降低键值的大小，这样可以降低联合过程中的读I/O，从而提高整体性能。
另外，你还需要了解更多的索引相关的信息。
为使性能最大化，用于联合的列应该数据类型相同。而且尽可能为数值型数据类型而非字符类型。
避免根据值不唯一的列进行表联合。如果用于联合的列在数值上不唯一，即使这些列上有索引，SQL Server优化器也将会执行表扫描然后进行联合。因此，为了获得更好的性能，要使用具有唯一索引的列来进行联合。
如果你必须对四个以上的表定期进行联合，以获得所需的数据集，那么你可以考虑对这些表进行反规范化（denormalizing），这样联合表的数量会减少。一般情况下，通过从一个表向其它表中增加1-2列，就可以大大降低联合的需要。
将代码封装在存储过程中
实际上，在你的SQL Server应用程序中使用的所有T-SQL代码都应该被封装在存储过程中，而不应作为动态SQL或脚本来运行。这不仅会降低网络流量，而且会加速T-SQL执行，因为服务器上存储过程中的代码已经被预编译。在编写存储过程时要想获得最优性能，需牢记以下几点。
当一个存储过程第一次被执行时，它会被优化，一个查询计划被编译并缓存在SQL Server内存中。如果相同的存储过程被再次调用，它将不会在创建一个新查询计划，而是使用缓存的计划，这会节省时间、提高性能。这可能并非一直都是你所希望的。如果存储过程中的该查询每次都完全相同，那么这是一件好事。但是如果查询是动态的（例如WHERE子句可能会有所不同），那么这将非常糟糕，因为查询运行时不会再给优化，该查询的性能可能会受影响。如果你知道存储过程中的查询每次运行时会有所不同，那么在你创建存储过程时，你可以考虑增加WITH RECOMPILE选项。这将会强制存储过程每次运行时需要重新编译。
记住在你的存储过程中增加“SET NOCOUNT ON”这个声明。如果你没有打开这个功能，那么每次一个SQL语句被执行是，SQL Server将会发送一个响应给客户端，显示该语句所影响的行数。实际上客户端一般不需要这个信息。使用这个声明可以有助于减少服务器和客户端之间的通信。
为避免在你的SQL Server应用程序中发生死锁现象，按照以下建议来设计你的应用程序：1、应用程序每次按照相同的顺序来访问服务器对象；2、在事务处理过程中，不允许任何用户输入。在事务处理开始前对其进行收集；3、保持事务处理简短且在一个单一批处理中。
如何选择索引来最优化数据库性能
对于许多SQL Server数据库管理员和开发者来说，索引选择是一件神秘的事情。当然，我们知道它们可以做什么以及它们如何提高性能。问题在于如何选择理想类型的索引（聚集索引还是非聚集索引），以及需要索引的列的数量（是否需要对多个列进行索引），和哪一个列应该被索引。
在这一部分中，我们将就如上问题给出简短的答案。不过，这些答案并不一定适合每一种情景。与大多数SQL Server性能调优和优化一样，你必须进行一些试验来发现最理想的索引。下面让我们先开始看看一些通用的索引创建规则，然后详细的看一下如何选择聚集和非聚集索引。
索引有可能是坏事吗？是的。有的人认为只要把所有一切都索引，然后所有性能问题就会被解决。事实并非如此。索引可以加速数据访问，但是如果它被不合适的选择，同样也会降低数据访问。
索引过多所带来的问题是，每次一条记录被插入、删除或更新操作时，SQL Server必须对索引进行维护。维护1、2个索引或许不会给SQL Server带来太大开销，但是如果你的索引在5个以上，这可能会给数据表带来较大的性能负担。理想情况下，索引应该越少越好。为了获得最优化性能，选择合适的索引数需要你掌握好一个平衡的度。
大概来说，只有你确信真正需要添加索引的时候，才对表添加索引。你必须非常了解对数据所进行的查询类型，然后根据这些信息选择合适的索引，索引创建后，必须通过测试来查看它是否真正可以提高性能。
对于OLTP应用来说，选择非常好的索引通常比较困难，因为这类应用通常要进行大量的插入、更新和删除行为。尽管你需要好的索引来迅速定为那些需要进行插入、更新或删除操作的记录，但是如果索引太多，会使得每一次操作带来太大的开销。另一方面，如果你有一个仅对数据库进行只读操作的OLAP应用，那么你可以根据自己的需要来尽可能多的添加索引。由此可见，你的应用程序使用方式不同，会给你的索引建立策略带来很大差别。
还有两种情况，索引不会给你的SQL Server带来性能上的提高，一是数据表非常小、记录非常少的情况下；二是被索引的列有超过95%以上的数值不是唯一的。这两种情况下SQL Server查询优化器不会使用你选择的索引，因此它们对SQL Server是一个负担，需要删除之。
选择聚集索引的技巧
由于对每一个表只能创建一个聚集索引，因此有必要多花些时间来仔细的考虑如何创建它。考虑下需要对表进行查询的类型，判断一下哪一个索引是最关键的，以及具有一个聚集索引是否对该查询的性能提高有帮助。一般来说，当选择一个列进行聚集索引时，需要规则来进行衡量。
表的主键并不一定总是一个聚集索引。如果你创建了主键，且没有指定其它聚集索引，那么SQL Server会自动将主键作为聚集索引。将主键作为聚集索引的推荐情况如下。
如果你需要一个分类结果，那么聚集索引对该查询比较适合。因为数据已经在索引中预先进行了分类。例如，当你的查询中使用了BETWEEN、<、>、GROUP BY、ORDER BY以及诸如MAX、MIN和COUNT之类的聚合操作时，适合使用聚集索引。
当你需要查询一个具有唯一值的记录，以及当你需要一条记录中的多数或全部数据时，选择聚集索引比较合适。因为这类查询会被该索引所包含。
聚集索引适合那些使用JOIN或GROUP BY子句的查询。
聚集索引适合那些需要返回大量行数据的查询，因为这些数据在索引中，就不再用到处查阅。
如果在你的表中有多列需要进行聚集，那么以上建议可能适用性不大。但是我们知道，每一个表只能有一个聚集索引。这种情况下，我们需要对所有可能进行评估，然后选择可以带来最大整体性能提高的那一个。
选择非聚集索引的技巧
选择非聚集索引相对来说简单一些，因为你可以根据需要对你的表建立多个索引。以下技巧可以帮助你选择对哪一列添加非聚集索引。
对于需要返回较少行数据的查询，最适合使用非聚集索引。不过，如同上面所提到的，如果数据表中某一列的数值不满足95%上是唯一的，那么SQL Server查询优化器不会使用该列的非聚集索引，因此不要对该列添加非聚集索引。例如，如果某一列的数据就是“yes”和“no”，它就不适合使用非聚集索引。
保持索引的“width”尽可能的窄，尤其是当你创建组合（多列）索引时更是如此。这可以降低索引的大小，降低对该索引进行读操作的数量，从而提升性能。
如果可能的话，尽量针对包含整数值的列创建索引，尽量不要对字符列创建索引。整数值所需要的开销比字符值要少。
如果你确信你的应用程序会反复对同一个表进行相同的查询，那么可以考虑对该表创建一个covering索引。一个covering索引包含在查询中引用的全部列。这样，索引包含你要查找的数据，SQL Server就不用必须去表中查看实际的数据，可以减少逻辑或物理上的I/O。另一方面，如果索引太大，也会增加I/O，降低性能。
总体来说，如果表只需要一个索引，可以对其添加聚集索引。如果表需要多个索引，那么你只能使用非聚集索引。通过遵循以上建议，你应该更容易为你的数据表选择非常好的索引。
