SQL Server 2005 中的电话号码：第一部分 – 数据类型

引言

在数据库设计方面，基础和细节至关重要。有些人认为每个记录上的几个字节无关紧要。为什么要在内存、磁盘和处理器如此便宜时担心字段大小？好吧，将这几个字节乘以数百万条记录，它很快就会变成需要考虑的事情。再加上索引的开销、低效的数据检索和更新，这几个字节可能不仅仅是压垮骆驼的最后一根稻草，而是几捆干草。

让我们来看一个简单的电话号码。几乎每个商业数据库都有字段和记录来存储联系信息。每个联系人至少有一个电话号码，而大多数人有几个。家庭、工作、传真、手机和寻呼机号码，一个人有五个或更多号码并不罕见。想出一个存储这些号码的有效方法是个好主意。

存储选项

字符数据类型

我见过的大多数数据库（以及我设计的大多数数据库）都将电话号码存储在字符字段中。通常，它可以是高达 varchar(22) 的字段，可以存储格式化的 北美电话号码，并带有分机号，格式为“ (999) 555-1212 ext1234”。这是存储号码的最简单方法，但也是效率最低的方法。我不会讨论 nvarchar(22) 版本，它碰巧是两倍大小但没有好处。由于这是一个 varchar 字段，我们假设平均字段大小约为 17 个字符，因为并非所有电话号码都有分机号。

用格式存储原始数据从来都不是一个好主意（这应该由用户界面处理），因此剥离非数据字符后，我们会得到一个看起来像“9995551212 1234”的字段，其中分机号由空格分隔。情况正在好转，字段定义为 varchar(15)，平均字段大小约为 12 字节；但当然，我们可以做得更多。

数字数据类型

关于电话号码，首先要认识到的是，它们实际上是数字。它们不包含其他字符，为什么不将它们存储在数字类型字段中呢？一种方法是将电话号码存储在 bigint 字段中，并将分机号存储在单独的 smallint 字段中。int 数据类型不够大，无法容纳号码和区号，而下一个可用的数字 bigint，可以轻松存储 18 位以上的数字。对于 smallint 也是如此。我们希望能够容纳高达 9999 的分机号，而 tinyint 只能存储 255，而 smallint 有很多空间用于四位数字的分机号。使用这两种数据类型，总共需要 10 个字节来存储电话号码。

这很好，但能否用一个字段完成，以消除额外字段的开销？当然可以！这似乎是 decimal 或 numeric 数据类型的良好候选者，它们是固定宽度数据类型，这意味着随着数字变大，它们不会丢失精度。我们可以巧妙地将分机号存储在小数部分，定义为四位数字。要存储带有分机号的 10 位电话号码，我们可以将数据类型定义为 decimal(14,4)。这将需要 9 字节的存储。 decimal 数据类型以 4 字节块存储，因此定义为 decimal(19,4) 也占用相同的 9 字节存储，但给我们提供了额外的几位数字用于国际电话号码（如果需要），而无需额外成本。如果您需要更多的分机号位数，可以将数字定义为 decimal(19,5) 或 decimal(19,6)。

将数据存储需求减半已经很好了，但我们还可以做得更好。SQL Server 还有 money 数据类型，它与 decimal 数据类型类似，但它是一个预定义、内置的类型，因此只需要 8 个字节就可以存储带有 14 位数字的分机号和电话号码。

使用 money 数据类型，与基本的 varchar 版本相比，数据空间需求减少了一半以上。事实上，如果我们进行苹果对苹果的比较，等效的非格式化 varchar 大小将是 19 个字符，因为 money 版本最多可以存储 14 位数字。除了存储空间需求较小的明显好处外，对固定数字数据类型的操作比对其他类型更快。固定宽度数据类型不需要跟踪字段中实际存储的字节的开销。出于相同的原因，索引也更有效。别忘了，如果您选择索引电话号码字段，索引大小也将是臃肿的 varchar 版本的一半大小，因此您将获得双倍的节省和效率提升。此外，数据完整性是内置的，因为数字数据类型默认只接受数字；数据库中不能存储任何奇怪的字符。

定义电话数据类型

既然我们已经确定了存储电话号码的数据类型是 money，我们可以显式创建电话号码数据类型。SQL Server 允许您使用 SQL Server 2005 命令创建 用户定义类型 (UDT)

CREATE TYPE [dbo].[phonenumber] FROM [money] NULL

或者通过在 SQL Server Management Studio 中选择“可编程性/类型/用户定义数据类型”菜单。为电话号码创建 用户定义类型 可以明确说明数据字段的意图。它使数据库实现更清晰、更明确。我们还可以将规则绑定到类型以强制执行数据完整性。

money 数据类型是接受负数的数字数据类型。电话号码应该是正数，所以让我们添加一个规则来禁止负数。在 SQL Server 2005 中，这看起来像

CREATE RULE PhoneNumber_Domain
    AS @Value > 0

必须使用命令将规则绑定到新数据类型

EXEC sp_bindrule 'PhoneNumber_Domain', 'phonenumber'

有了用户定义的数据类型和绑定的自定义规则，在需要电话号码字段的地方都可以轻松地将电话号码实现为自定义类型。

在 SQL Server 中的电话号码 - 第二部分 - 格式化 中，我将讨论如何使用 用户定义函数 (UDF) 在 SQL Server 中呈现电话号码。