1. 数据类型总体规划先搞清分类逻辑这两天在帮某项目做数据库层重构的时候遇到一位朋友提出了一个很典型的问题建表的时候到底该用 varchar 还是 nvarchar为什么网上搜到的答案五花八门甚至有把 int 和 bigint 用混了导致 ID 溢出直接把业务写挂的情况。说实话SQL Server 的数据类型文档铺天盖地但真正能把每个类型讲透、讲清楚背后取舍依据的内容反而少见。我打算把这几年建表、调优、迁移过程中对 SQL Server 数据类型的理解系统性地整理一遍。无论你是刚接触 SQL Server 的初学者还是已经写了几年存储过程的老手这篇文章都值得花时间看完因为很多坑恰恰来自那些你自以为了解的基础概念。先把类型总量摊开。SQL Server 中的数据类型大体分为九大类精确数值型、近似数值型、日期时间型、字符串型、Unicode 字符串型、二进制型、其他数据类型如 uniqueidentifier、xml、table、空间数据类型以及 SQL CLR 自定义类型。每一类下面又有若干具体类型加起来数量超过了三十种。但实际生产环境中大部分业务用到的连一半都不到。问题的核心不是让你把每种类型都背下来而是你得知道在什么场景下该去查哪个分类、不同分类之间为什么不能互相替换。1.1 为什么分类是选型的起点举个例子数值型里的 int 看起来简单但你可知道 int 在 32 位有符号范围内的上限是 21 亿多。如果你设计的主键用 int而业务日增数据量超过两百万两年出头就会撞到上限。这不是危言耸听我曾见过某系统的订单表就是因为预估不足int 溢出了应用程序大量报错最后只能凌晨停机改表结构。如果当初选用 bigint存储空间不过是从 4 字节变成 8 字节但彻底杜绝了这个隐患。分类思维的核心在于每一种数据类型不仅仅是存什么数据的标签它同时决定三件事——存储空间大小、取值范围或精度、以及系统对它的行为方式比如比较规则、排序规则、隐式转换规则。拿字符串类型举例varchar 和 nvarchar 表面上看只是前缀多了个 n但它们的存储机制、字符集支持、以及是否会发生隐式转换导致索引失效差别非常大。很多人就因为不清楚这一点明明建了索引查询却全表扫描。1.2 存储最小化贯穿整个选型的底层原则我在给团队做代码评审的时候最常说的一句话是每一种数据类型的选择都要追问一句为什么必须用这个类型。弱化类型的意识往往会造成存储浪费和性能下降同时出现。举个最简单的例子一个存性别或状态的列用 bit 只占 1 字节有人偏偏要用 char(1) 存Y/N看起来无所谓但如果有 5000 万行数据char(1) 按 1 字节算非 Unicode虽然也小但在聚集索引中行大小变大会直接导致一页能存的行数变少进而增加页读取次数和内存压力。更极端的情况是有人用 nvarchar 存手机号nvarchar 每个字符占 2 字节一个 11 位手机号就是 22 字节而用 varchar(11) 是 11 字节行大小翻倍。这个问题在单表测试时看不见一旦关联多张表扫描成本成倍上涨。所以选类型的根本原则我总结为三句话不放大——能用小类型不用大类型不混用——同一类语义的列保持一致的类型不滥用——能用系统内置类型解决就不要引入自定义类型和字符串序列化。2. 数值型详解精确与近似的边界数值型是 SQL Server 里用到的最高频类型看起来门槛最低但涉及精度和存储空间时错误率反而最高。我先按整数、定点数、浮点数三类逐一拆开说。2.1 整数家族tinyint 到 bigint 的选择依据SQL Server 提供了四种整数类型边界非常清晰tinyint1 字节无符号范围 0 到 255只能存非负整数。适合存状态码、开关值、很小的枚举但不适合存负值。smallint2 字节有符号范围 -32768 到 32767如果业务数据的置信区间明确小于 3 万多可以用它。int4 字节范围 -2^31 到 2^31-1也就是 -2147483648 到 2147483647这是绝大多数场景下的默认选择。bigint8 字节范围 -2^63 到 2^63-1适合做大数据量表的主键或雪花 ID 的存储列。从存储空间来看每升一级空间翻倍。但要注意tinyint、smallint 和 int 在 SQL Server 中做运算时会发生一个很容易被忽略的行为两个 smallint 相乘的结果会自动提升为 int但如果你把结果插回一个 smallint 列就会出现溢出错误。我见过不少开发者在计算累计值或做聚合时忽略了这个隐式提升导致莫名其妙的 Arithmetic overflow 错误。2.2 decimal 与 numeric业务金额的可靠选择decimal(p,s) 和 numeric(p,s) 在功能上是完全等价的p 是精度总位数s 是小数位数scale。它们的取值范围非常有意思最大的 decimal(38,s) 可以表示极大的数存储大小由精度决定5 到 9 位数字占 5 字节10 到 19 位数字占 9 字节20 到 28 位占 13 字节29 到 38 位占 17 字节。在业务金额场景中我几乎无条件推荐使用 decimal。原因很简单decimal 是精确数值类型它的存储机制是定点方式不会出现二进制小数表示导致的误差。而 float 是近似类型0.1 这样的十进制小数在二进制世界里无法被精确表示累计运算以后误差会被放大。金融、账单、库存单价统统用 decimal 不要犹豫。精度设置上有一句经验金额类型常用 decimal(18,4) 或 decimal(18,2)。如果你做汇率或者需要更细的计价单位小数点位数最好比实际需求多留 2 位以免除法运算导致最终结果被四舍五入到不可接受的程度。很多人一开始定义 decimal(10,2)做了几个月的报表对账之后发现报表差异在分币上就是要查找除法的精度丢失问题。2.3 money 类型到底能不能用SQL Server 提供了 money 和 smallmoney 两种货币专用类型。money 的精度是 19 位小数固定 4 位范围约 -922337203685477.5808 到 922337203685477.5807。smallmoney 范围在 -214748.3648 到 214748.3647。我的建议是新项目建议直接选 decimal。原因有两个层面第一money 类型虽然固定小数位但默认的舍入行为和某些除法运算中的精确度规则与普通数值不同在跨系统对接时容易踩坑第二ORM 框架对 money 的支持不如 decimal 广泛类型映射容易出现意外。2.4 float 与 real精度陷阱float 是近似数值类型这意味着你存储的 0.1 实际上可能被保存成 0.1000000000000000055511151231257827021181583404541015625而当你读取并比较时可能得到不一致的结果。float 占 8 字节real 占 4 字节real 等价于 float(24)精度约 7 位float 默认是 float(53)精度约 15 位。什么时候用 float科学计算、统计分析、图形坐标这类允许微小误差的场景。什么时候不能碰金额、数量、标识符、任何需要精确相等的场景。这里尤其要提醒不要用 float 存电话号码或证件号。有一次我给某排查一个莫名其妙的数据不对的问题最后发现用户的身份证号被应用层转成 float 存进数据库长数字被四舍五入后面几位全部变成 0。类型选错数据不可逆。3. 字符与 Unicode 字符串varchar 还是 nvarchar字符类型是另一个重灾区尤其是中文字符环境下varchar 和 nvarchar 的取舍直接关系到存储翻倍和排序规则问题。3.1 char 与 varchar定长与变长的代价char(n) 是定长当存储的内容不足 n 时系统会在末尾补空格读出来的时候如果没设置好 ANSI_PADDING会出现带尾巴的字符串varchar(n) 是变长实际占用的存储空间是真实数据长度加上存储开销2 字节用于记录长度。从存储特性看char 适合固定长度的编码值比如省市区编号、国家代码、币种代码varchar 适合大多数长度不定的文本。但有一点在 SQL Server 中需要特别注意varchar 中一个字符占 1 字节但它实际能存放的字节数与使用的代码页有关如果是扩展中文字符集某些汉字可能占 2 个字节而列定义中的 n 限制的是存储的字节数不是字符数。3.2 nchar 与 nvarchar为什么中文环境默认选它nvarchar(n) 和 nchar(n) 按 Unicode 编码存储每个字符固定占 2 字节数据库默认的 UTF-16 编码情况下。这意味着同样的字符串长度nvarchar 占用的空间是 varchar 的两倍。但它的好处是不管数据库的排序规则和代码页怎么设置都能正确存储世界上绝大多数语言的字符包括中文、日文、韩文、表情符号补充平面字符需要 4 字节需要用到 nvarchar(max) 配合正确的排序规则。我的经验是如果你确定系统只面向单一语言环境比如纯 GBK 中文场景用 varchar 可以节省一半存储但只要存在国际化预期或者不确定输入的字符集直接用 nvarchar省心远大于省空间。实践中SQL Server 默认安装的排序规则通常是 Chinese_PRC_CI_ASvarchar 列存储中文没有任何问题关键是排序规则要保持一致否则在 JOIN 时会出现 Cannot resolve the collation conflict 这样的错误。3.3 被淘汰的 text 与 ntext别再用了text 和 ntext 是 SQL Server 2005 之前遗留下来的大文本类型它们的行为有很多限制不能直接使用字符串函数不能参与某些查询操作而且 text 的数据存储是非行的读取开销大。从 SQL Server 2005 开始varchar(max)、nvarchar(max) 已经能存储最大 2GB 的文本并支持所有字符串操作。强烈建议把历史遗留表中的 text/ntext 统一迁移到 varchar(max)/nvarchar(max)。这里有一个很隐蔽的差异varchar(max) 以及 nvarchar(max) 在表行中的存储方式有一个溢出机制。当字符串长度小于 8000 字节nvarchar(max) 为 4000 字符时它存储在行内行为与普通 varchar 相似一旦超过限制数据就会被移到单独的 LOB Allocation Unit 中查询时会产生额外的读写。这意味着你不能因为反正有 varchar(max)就把所有短字符串都设计成 max 类型这会让行尺寸不可控索引效率下降。4. 日期时间类型精度与存储的博弈日期时间类型在不同版本 SQL Server 中的变化比较大很多人还在用老旧的 datetime不知道新版提供了更高效的选择。4.1 datetime、smalldatetime 与 datetime2 对比datetime 是 SQL Server 2005 时代的老类型精度是 3.33 毫秒范围从 1753 年 1 月 1 日到 9999 年 12 月 31 日每个值占用 8 字节。它的精度限制非常坑当你在 .NET 里用 DateTime.Now 写入一个带毫秒的值时SQL Server 会做舍入可能导致两侧值不一致进而影响查询条件。smalldatetime 范围是 1900 年 1 月 1 日到 2079 年 6 月 6 日精度只到分钟采用两个 2 字节存储共 4 字节。它唯一的优点是省空间但代价是精度过低基本上只适合记录哪天几点这个粒度业务里已经很少用。我最推荐的是 datetime2。它从 SQL Server 2008 开始引入精度范围可配置从 0 到 7 位小数秒默认 7 位存储大小从 6 字节到 8 字节不等取决于精度。datetime2 的精度更高范围更大还避免了 datetime 的舍入行为偏差。更重要的是它在做日期边界查询时不会出现少一秒的问题。datetimeoffset 则是 datetime2 的扩展附加了时区偏移量适合记录全球化事件时间。如果你做跨时区的业务系统而不是把时间统一转成 UTC 存入 datetime2那么 datetimeoffset 是更合理的选择它保留了原始时区信息方便展示端还原。4.2 date、time、datetimeoffset 的应用场景date 只存日期不存时间占用 3 字节。time 只存时间不存日期占用 3 到 5 字节取决于精度。这两个类型很适合把日期和时间拆开存储比如一个业务表中日期可以用 date 列做分区时间用 time 列做统计这样在按天聚合的时候不需要在 datetime 上做 cast 或者 range 筛选索引更有效。这里提醒一点SQL Server 2016 及以后支持 temporal table时态表它的 ValidFrom/ValidTo 字段推荐使用 datetime2这样可以保留到微秒级的精度。如果你仍然用 datetime当时态表切换版本时同一时刻可能生成完全相同的值导致主键冲突。4.3 日期类型常见坑第一个坑是字符串与日期的隐式转换。假设有个查询条件写成 WHERE create_time 2023-06-01当我们 create_time 是 datetime 类型时SQL Server 会将字符串转成 datetime没有问题但如果你传入的是 2023-06-01 12:00:00 而 create_time 是 date 类型就会先做隐蔽转换最终很可能产生非 SARGable 条件导致索引无法使用。最稳妥的做法是所有应用层传入的日期时间参数一律用强类型参数化不要拼字符串。第二个坑是闰秒。UTC 的时间标准里有闰秒但 datetime2 不会处理闰秒也不会标记这是否是闰秒。一般业务系统不必考虑但如果有天文级的时间敏感应用要提前确认 SQL Server 是否满足需求。5. 二进制与专用类型从文件存储到系统字段很多人看数据类型清单的时候看到 varbinary、rowversion 这些类型会觉得这些跟我没关系。实际上它们在很多场景下扮演关键角色。5.1 binary 与 varbinary 的用途binary(n) 是定长二进制不足 n 位时右侧补 0x00varbinary(n) 是变长二进制。如果你需要存储文件的校验值、加密后的密文、序列化后的对象varbinary 是标准选择。varbinary(max) 可以用来存储最大 2GB 的二进制大对象。在 SQL Server 2012 之后还可以通过 FileTable 来管理外部文件但底层依然依赖 varbinary(max) 的存储机制。一个不得不提的点是在 SQL Server 中binary 类型做相等比较时是逐字节比较的如果两个值在末尾有补零差异可能被判定为不同。如果你用 binary 存储需要匹配的数据建议统一用 varbinary 并且严格保证写入长度一致。5.2 标识与唯一性类型bit、uniqueidentifier、rowversionbit 类型表面上是布尔值但它并非严格的布尔类型它存储 0、1 或 NULL。在 SQL Server 中一个表中多个 bit 列会共享存储字节每 8 个 bit 列合并为一个字节存储这个细节会让行大小变化出乎意料。比如你定义 8 个 bit 列实际只占 1 字节但如果定义一个 bit 列再加 7 个 tinyint 列行大小就会明显增长。uniqueidentifier也叫 GUID存储 16 字节的全局唯一标识符。用它做主键的优点是可以在多数据库或分布式环境下生成不冲突的 ID但缺点是它在聚集索引中的随机分布特性会让索引页频繁分裂导致写入性能急剧下降。如果必须用 GUID 做业务标识我建议把它设为非聚集索引键另设一个自增 bigint 作为聚集索引键。rowversion旧称 timestamp是数据库自动维护的二进制数字每次行更新时自动递增非常适合做乐观并发控制的版本号。它不是真正的时间戳和 datetime 没有任何关系有点反直觉。当你在表中增加一个 rowversion 列每次做 UPDATE 时该列会自动变化。在做读-改-写的并发场景中你可以用这个列判断数据是否被其他事务修改过能省掉很多复杂的锁设计。5.3 XML、空间与其他辅助类型xml 类型可以存储格式良好的 XML 文档它自带 XQuery 支持可以直接在 SQL Server 中查询和修改 XML 节点。我的态度是能用关系表表示的数据就别存 XML只有当文档结构经常变化、不适合固定列建模时再用 xml。同时xml 类型列不能被直接比较和排序这给查询带来不少限制。空间数据类型 geography 和 geometry 用于地理位置相关数据。geography 处理球面坐标纬度/经度geometry 处理平面坐标。业务上如果你做基于地图的查询直接用 geography 和内置的 STDistance 方法可以省去大量自建计算。其他辅助类型包括 sql_variant可以存储不同类型值、table 类型用于表值参数、hierarchyid用于树形结构等。sql_variant 用起来非常灵活但它几乎抛弃了类型约束性能较差不建议在业务表里用。hierarchyid 在组织架构、分类树场景里很实用它用变长编码存储节点路径比传统的 parent_id 递归查询高效得多。6. 选型决策真实场景中的取舍建议讲了这么多类型下面落到方法上。面对一个具体的业务字段怎么快速定类型我提供一个我在项目里反复训练的心智模型。6.1 表设计时如何快速选出正确类型第一步判断数据的业务属性。如果是数值确定语义是整数、金额、比例、还是标识号。整数用整数类型金额用 decimal比例一般也用 decimal精度开高一些标识号如果是长整型雪花 ID 用 bigint如果是字符串形式用 varchar。第二步判断是否参与运算。参与数学运算的字段一定要用数值类型。比如手机号不参与数学运算用 varchar(11) 或 nvarchar(11) 都可但绝对不能用 numeric 或 bigint因为这样会丢失前导零。同理类似订单号这种虽然看起来像数字但不会做加减乘除的字段一律用字符串类型。第三步判断最大长度和增长趋势。字符串类型根据字节数选择 char/varchar 或者 nvarchar并把长度定义为业务可能的最大值再留一点余量不要直接设定为 varchar(max)。主键数值类型要结合业务增速预估比如高并发平台的核心表主键直接上 bigint因为你很难预测三年后的数据量会不会超过 21 亿。6.2 索引与存储交互的考虑类型选择直接影响索引结构。字符串列做主键或者索引键时过宽会导致非叶子节点变大、单页存储键值变少B 树的层级变高进而增加随机 IO。所以经常看到有些人建议用整数做主键字符串做业务唯一键核心就在这里。如果你必须在一个很长的字符串列上建索引而业务上只需要等值查询可以考虑引入一个计算列来散列该字符串存储为 binary(8) 或 bigint并在其上建索引。这个方法在解决长字符串前缀索引问题时非常有效。6.3 迁移和升级时的类型变更数据库重构中类型换型是最棘手的操作之一。比如把 varchar 改成 nvarcharSQL Server 会做全表扫描和重建同时产生大量事务日志。如果你在在线系统上直接 ALTER TABLE可能导致长时间锁表。正确的顺序是建新列、双写、分批迁移、校验、切换、删旧列。如果你在建表时就严格遵循选型规则这类问题是完全可以避免的。另外有一点想强调varchar 的默认替换行为在不同排序规则下表现不同。比如从 SQL_Latin1_General_CP1_CI_AS 数据库迁移到 Chinese_PRC_CI_AS 数据库如果原表里 varchar 列存的是中文你要验证这些列在迁移之后是否能正常显示。如果涉及跨库查询建议优先考虑 nvarchar 统一。7. 常见问题与排查技巧实录这一节我整理一些我在实际运行环境里经常遇到的数据类型相关问题和排查思路全都是踩过坑之后的真实记录。7.1 类型隐式转换导致的性能问题遇到一个查询很简单但执行计划显示 Index Scan看明细才发现 where 条件左侧是 varchar 列右侧传入一个 int 值。SQL Server 根据数据类型优先级把 varchar 列转成 int 来比较索引列被函数包裹索引失效。解决办法是把传入参数改成 varchar 类型。对应地nvarchar 与 varchar 比较时SQL Server 也会把 varchar 隐式转换为 nvarchar如果大表上有 JOIN这种隐式转换会在运行时逐行发生性能代价极高。所以我常跟同事说两张表关联字段的数据类型必须完全一致包括长度否则你就是在埋雷。7.2 溢出与截断错误Arithmetic overflow error converting expression to data type int这类报错我见过太多次。原因不外乎SUM 结果超过了目标类型的上限或者两个 smallint 相乘后插入到 smallint 列。排查思路很简单先通过查询找出有问题的值再根据业务确认是改类型还是改逻辑。字符串截断的报错是 String or binary data would be truncated。遇到这个别只想着把列加长先看看是不是固化了错误的前缀字符或者在 UPDATE 时传入了超长内容。7.3 快速排查方法当你面对一个不知道选什么类型的字段时最快速的方法是打开 SQL Server Management Studio 的表设计器把可能用到的候选类型逐一试一遍同时查看下面的长度和允许 NULL属性变化。这虽然笨但不失为一种可视化验证手段。还有一个实用技巧在生产环境做大型查询前把相关列的 metadata 查出来用 sys.columns 聚合一下看有没有类型的不整齐情况。比如两张经常 JOIN 的表键列一个用 int 一个用 bigint虽然查询在数据量小的时候没问题但数据量涨起来后JOIN 的性能会断崖式下降。最后再分享一个我常用的工具化操作在开发环境执行 SET STATISTICS IO ON 和 SET STATISTICS TIME ON对比前后两次查询的 logical reads 和 CPU time。当你修改了某个列的数据类型或者统一了两个表的关联字段类型后这个对比能直观地告诉你改动到底带来了多少收益。我从实操中体会最深的一点是SQL Server 数据类型选择本质上不是在正确与错误之间选择而是在可维护性与性能之间做平衡。真正的高手不是把所有类型都背下来而是能把每个类型背后的存储机制和查询行为摸透在业务变化的早期就做出调整。希望这篇整理能帮你省掉一些不必要的排查时间。