本文章旨在让您检测数据库中的常见问题。

分布式查询

如果您使用 OPENROWSET 和 OPENDATASOURCE 访问数据，您需要启用 Ad Hoc 分布式查询。默认情况下它被禁用。要启用 Ad Hoc 分布式查询，请运行以下命令：

sp_configure 'Ad Hoc Distributed Queries', 1;GORECONFIGUREGOsp_configure 'Ad Hoc Distributed Queries', 1;GORECONFIGUREGO

列排序规则与数据库默认值不匹配

排序规则控制字符串的排序和比较方式。排序通常不是问题，因为它不会导致排序规则冲突。它可能不会按照您想要的方式排序，但不会导致错误。这里真正的问题是当你比较数据时。比较可以通过几种不同的方式进行。这可以是 where 子句中的简单比较，也可以是连接条件中的比较。如果您的数据库中的列与数据库的默认排序规则不匹配，您就会遇到一个等待发生的问题。

当您向现有表添加新列或使用字符串列创建新表时，并且您未指定排序规则，它将使用数据库的默认排序规则。如果您随后编写与现有列（具有不同排序规则）连接的查询，您将收到排序规则冲突错误。

在这里要清楚一点，我并不是建议每个字符串列都应该有一个与数据库的默认排序规则相匹配的排序规则。相反，我建议当它不同时，应该有一个很好的理由。有许多成功的数据库，开发人员从不考虑排序规则。在这种情况下，每个字符串列的排序规则最好与数据库的默认排序规则相匹配。

列数据类型 numeric vs int

整数 - 使用数值与整数

当我在数据库上工作时，我遇到了一个奇怪的景象，多个列定义为 numeric(7,0)、numeric(9,0) 等等。似乎有人试图为数据库提供可能针对许多不同数据的最具体的定义。从来没有遇到过这种特殊的做法，我立即开始寻找原因。变小了吗？快点？更好的？

贮存

使用一个非常具体、定义明确的数字实际上已经消耗了我们的存储空间，而不是减少了它。精度值为 1 到 9 的数字（或十进制）需要 5 个字节，而 10-19 需要 9 个字节。将此与多种 int 进行比较：

| Digits  | Int Variety | Int Bytes|Numeric(*,0) Bytes| Difference|| 1 - 2  | tinyint    | 1    | 5 | 4  bytes    || 3 - 4  | smallint| 2    | 5 | 3  bytes    || 5 - 9  | int    | 4    | 5 | 1  byte    || 10 - 18 | bigint    | 8    | 9 | 1  byte    |

因此，对于包含该值的每一行和每个索引，我们都会丢失存储空间。

参考：MSDN 上的 Int 参考和 MSDN 上的Numeric/Decimal 参考

性能#1

当 SQL Server 被要求执行一个数学函数（+、-、*、/）时，它使用一组已定义的规则来确定输出类型，然后将参数隐式转换为该类型（有关相关的子集，请参阅本文到小数）。这意味着在许多情况下，可能存在从 int 到 numeric 的隐式转换，因此有人可能认为我们可以尝试通过将字段定义为 numeric 而不是 int 来调整我们的性能。

让我们测试一下隐式转换：

/*  Creation of some number tables  */Create Table NumberIntTest(Num Int Identity(1,1) Primary Key)goSet NOCOUNT ONBegin TranInsert Into NumberIntTest Default ValuesInsert Into NumberIntTest Default ValuesInsert Into NumberIntTest Default ValuesInsert Into NumberIntTest Default ValuesInsert Into NumberIntTest Default ValuesInsert Into NumberIntTest Default ValuesInsert Into NumberIntTest Default ValuesInsert Into NumberIntTest Default ValuesInsert Into NumberIntTest Default ValuesInsert Into NumberIntTest Default ValuesCommit Trango 100000Create Table NumberNumericTest(Num numeric(7,0) Primary Key)GoSet NOCOUNT ONBegin TranInsert Into NumberNumericTest(Num) Select Num From NumberIntTest WHERE Num > 10001Commit TranGo/*  Execute a variety of test scripts  */DECLARE @Start DateTime;DECLARE @Garbage int, @Junk numeric(7,0);DECLARE @Int int; SET @Int = 1;DECLARE @Num numeric(7,0); SET @Num = 1;-- Divide an int by an intSELECT @start = GETDATE();SELECT @Garbage = Num/@Int FROM NumberIntTest n;SELECT DateDiff(Millisecond, @Start, GetDate());-- Divide a numeric(7,0) by an intSELECT @start = GETDATE();SELECT @Junk = Num/@Int FROM NumberNumericTest n;SELECT DateDiff(Millisecond, @Start, GetDate());-- Divide a numeric(7,0) by a numeric(7,0)SELECT @start = GETDATE();SELECT @Junk = Num/@Num FROM NumberNumericTest n;SELECT DateDiff(Millisecond, @Start, GetDate());-- Divide an int by an int w/ explicit casting to numericSELECT @start = GETDATE();SELECT @Junk = Num/CAST(@Int as numeric(7,0)) FROM NumberIntTest n;SELECT DateDiff(Millisecond, @Start, GetDate());-- Divide an numeric(7,0) by an int w/ explicit castingSELECT @start = GETDATE();SELECT @Junk = Num/CAST(@Int as numeric(7,0)) FROM NumberNumericTest n;SELECT DateDiff(Millisecond, @Start, GetDate());Initially I compared the execution plans and didn’t see much difference, but after some modifications (thanks George!) and additions we can see the differences between a number of different situations.difference, but after some modifications (thanks George!) and additions we can see the differences between a number of different situations.

样本结果：

Test  | time (ms)int/int - No Cast     | 170numeric/int - Implicit Cast | 313numeric/numeric - Implicit Cast    | 296int/CAST(int as numeric) - Implicit Cast| 320numeric/CAST(int as numeric)| 290

在第二个测试的计划中，我们可以看到隐式转换的示例：

带隐式转换的数字/整数：

  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(numeric(7,0),[utils].[dbo].[NumberNumericTest].[Num] as [n].[Num]/CONVERT_IMPLICIT(numeric(10,0),[@Int],0),0)))|--Clustered Index Scan(OBJECT:([utils].[dbo].[NumberNumericTest].[PK__NumberNu__C7D08B630AD2A005] AS [n]))

因此，如果我们确实有一个整数需要使用浮点值（最后一种情况）进行操作，那么在整数参数上添加一个简单的强制转换将使执行性能与拥有两个数字一致，这意味着没有增益将值存储为数字（7,0）。

性能 #2

另一个潜在的性能影响是自动参数化。当您向 SQL Server 提供非参数化的 SQL 语句时，就会发生自动参数化。服务器确定这些参数的类型并将它们参数化（使计划可重用于非参数化查询的魔法的一部分）。我找不到任何非常近期的东西，但早在 SQL Server 6.5 和 7.0 中，引擎就被记录为使用 int 类型来处理任何 9 位或更少的非十进制值。这意味着在您执行内联、非参数化 SQL 语句并在表定义中使用 numeric( ,0) 类型的不太可能的情况下，您实际上会因自动参数化整数的隐式转换而受到性能影响到数字（，0）字段。

如果这还不够糟糕的话，相同的 SQL Server 文档说 SQL Server 将整数视为比数字和小数类型更精确。它没有具体说明文档为什么不遗余力地与我们分享这些信息，但通常当有人不遗余力地在文档中指出这样的事情时，我会有点紧张，并且倾向于更加专注关于他们的“推荐”实践（使用 int）。

有关参数化和SQL 7 比较优化的更多信息

总结

因此，归根结底，使用 numeric(*,0) 需要更多空间，与显式转换相比没有明显的好处，并且如果您对服务器执行非参数化 SQL 语句，实际上可能会伤害您。

列名问题

列名（和表名）中不应包含空格或任何其他无效字符。这被认为是不好的做法，因为它要求您在姓名周围使用方括号。方括号使代码更难阅读和理解。查询（如下所示）还将突出显示名称中带有数字的列和表。大多数时候，当列名中有一个数字时，它代表一个非规范化的数据库。此规则有例外，因此并非所有出现的此问题都需要修复。

如何更正：如果这是一个数字问题，您可能需要重新设计数据库结构以包含更多表。例如，如果您有一个带有 (StudentId, Grade1, Grade2, Grade3, Grade4) 的 StudentGrade 表，您应该将其更改为带有 (StudentId, Grade, Identifier) 的 StudentGrade。每个学生在此表中会有多行（每个年级一个）。您需要添加一个标识符列来指明成绩的用途（11 月 10 日的测试、读书报告等）。

如果这是一个奇怪的字符问题，那么您应该更改列的名称，使其成为一个没有任何空格、数字或符号的简单单词或短语。执行此操作时，请确保检查所有出现的使用 this 的位置。这可能包括过程、函数、视图、索引、前端代码等......

数据类型为 Text 或 nText 的列

对于 SQL2005 之前的 SQL Server 版本，存储大量数据的唯一方法是使用 text、ntext 或 image 数据类型。SQL2005 引入了新的数据类型来替换这些数据类型，同时还允许所有有用的字符串处理函数工作。将数据类型更改为新的 SQL2005+ 等效项应该相对简单且易于实现（取决于表的大小）。那么，为什么要等呢？现在转换数据类型。

如何更正：将数据类型更改为SQL2005+版本。text 应转换为 varchar(max)，ntext 应转换为 nvarchar(max)，image 应转换为 varbinary(max)。

具有浮点数据类型的列

这可能看起来有点苛刻，而且并不总是如此。但是，大多数时候，应该避免使用浮点数据类型。不幸的是，浮点（和实数）数据类型是可能导致严重舍入错误的近似数据类型。

如何更正：检查您正在使用的数据并确定所需的精度和规模。更改数据类型（或代码）以使用具有所需精度和比例的小数。

具有图像数据类型的列

对于 SQL2005 之前的 SQL Server 版本，存储大量数据的唯一方法是使用 text、ntext 或 image 数据类型。SQL2005 引入了新的数据类型来替换这些数据类型，同时还允许所有有用的字符串处理函数工作。将数据类型更改为新的 SQL2005+ 等效项应该相对简单且易于实现（取决于表的大小）。那么，为什么要等呢？现在转换数据类型。

如何更正：将数据类型更改为SQL2005+版本。text 应转换为 varchar(max)，ntext 应转换为 nvarchar(max)，image 应转换为 varbinary(max)。

数据库整理

当您的用户数据库的排序规则与 TempDB 的排序规则不匹配时，您就有一个潜在的问题。临时表和表变量在 TempDB 中创建。当您没有为表变量和临时表中的字符串列指定排序规则时，它们将继承 TempDB 的默认排序规则。每当您比较和/或加入临时表或表变量时，您可能会遇到排序规则冲突。

在正常情况下，最好所有排序规则都匹配。这包括 TempDB、Model（用于创建新数据库）、您的用户数据库和所有字符串列（varchar、nvarchar、char、nchar、text、ntext）。

如何纠正：有几种方法可以纠正这个问题。长期解决方案是更改数据库的默认排序规则（影响新的字符串列），然后更改现有列的排序规则。或者，您可以修改任何创建临时表或表变量的代码，以便它在您的字符串列上指定排序规则。您可以硬编码排序规则或使用默认的数据库排序规则。

前任：

创建表 #AnyNameYouWant(Id Int, EyeColor VarChar(20) Collat​​e Database_Default)

十进制大小问题

当您使用小数（或数字）数据类型时，您应该始终确定其精度和小数位数。如果不这样做，则精度默认为 18，小数位数默认为 0。当小数位数为 0 时，不能存储小数。如果您不想存储小数，则应使用不同的数据类型，如 bigint、int、smallint 或 tinyint。

转发记录

HEAP 表和转发记录是一个主要且被忽视的性能问题。

要解决此问题，您只需执行以下操作：

ALTER TABLE 表名 REBUILD

碎片化索引

如果您进行大量更新或插入和删除，您的索引将随着时间的推移而变得碎片化。

修复碎片有两种方法，一种是重组索引，另一种是重建索引。

重组是在线操作，而重建不是，除非您指定 ONLINE = ON

ONLINE = ON 仅适用于 SQL Server 企业版。

ALTER INDEX indexName ON tableName REORGANIZE;

ALTER INDEX indexName ON tableName REBUILD;

缺少外键

参考文献是数据库的核心。可以使用完美运行的前端代码创建一个漂亮的数据库，始终 100% 的时间对您的数据做正确的事情。但是，写代码很难。很难！您的数据通常是您拥有的最重要的资产。你需要用你能找到的每一点技术来保护它。保护数据的核心是参照完整性。这是什么意思？这意味着您永远不应该丢失数据！

测试将检查列名称中具有 ID 的列，其中该列不是主键或外键约束的一部分。通常，这表示缺少约束，但并非总是如此。

如何纠正它：纠正这个问题起初似乎很简单。只需声明您的外键，对吗？好吧，这不是那么简单。你看，可能有代码正在运行，从相关表中删除所有必要的数据。如果您的代码以错误的顺序删除相关表中的数据，则会出现引用约束错误。更新和插入可能会出现类似问题。当你有引用约束时，你做事的顺序很重要。

孤立用户（Only）

在执行恢复或重新附加数据库时，最常见的情况是数据库用户成为孤立用户。

这仅仅意味着数据库用户与 sql server 登录无关。

有关解决此问题的脚本，请参阅：https ://dba.stackexchange.com/questions/12817/is-there-a-shorthand-way-to-auto-fix-all-orphaned-users-in-an- sql-server-2008

超强干货来袭 云风专访：近40年码龄，通宵达旦的技术人生