简介《sql server函数大全精析》是一份聚焦SQL Server函数使用的技术文档适合数据库初学者、开发人员及备考者快速查阅与系统学习。压缩包内含1个doc文件共859KB以精炼篇幅系统讲解函数应用。文档首先按聚合、配置、转换、加密、游标、日期时间、数学、元数据、排名、行集、安全、字符串、系统统计、文本和图像等类别梳理函数体系并重点阐明确定性与非确定性函数的判定标准给出AVG、CAST、CONVERT、DATEADD等确定性函数与GETDATE、RAND等非确定性函数的典型示例。同时通过DECLARE声明变量、SET与SELECT赋值、SQRT函数返回值捕获等实例演示在函数调用中使用用户变量的完整流程帮助读者理解参数传递、返回值处理及变量赋值差异。当前已有1190人学习下载适合在编写查询、优化存储过程或准备数据库面试时作为随身参考。1. 函数大全不等于能打SQL Server 函数精析真正要解决的三件事很多开发者电脑里都收藏过一份SQL Server 函数大全式的速查表真到写查询时却还是翻半天同一个函数在测试库上飞快上生产库就慢到报表超时同一段写法在别人那里能跑在自己这里却报转换错误。这类问题的根源不在于函数本身而在于知道函数存在和知道怎么选型是两码事。SQL Server 函数是查询语句里的基础构件但每个函数都有参数边界、数据类型假设和版本限制脱离场景背清单基本没用。精析这个标题我理解要解决三件事第一在几十个常用函数里选出当前场景最合适的一个第二把函数放回查询上下文里考虑包括索引、数据类型和 NULL 语义第三出了问题时能快速定位是函数用法错还是数据本身不干净。后面几章就按这三件事展开最后的章节给你几个可以立刻抄走的组合写法。这篇文章适合正在写 SQL 的开发、做报表的数据分析师以及要排查慢查询的运维同学。2. 字符串处理是函数主战场高频函数、版本边界与两个必调参数几乎所有业务表里都躺着 code、remark、name 这类字符串列字符串函数因而是日常使用频率最高的一类。这里挑两组最容易被轻视的函数LEN / DATALENGTH 与 CHARINDEX / SUBSTRING前者把所有长度问题变成疑难杂症后者是解析编码类字段的万能组合。最后补上 STRING_SPLIT 与 STRING_AGG 这对拆拼搭档它们直接决定你能不能把数据处理留在 SQL 层完成。2.1 LEN 与 DATALENGTH一个中文字符的差距先看一个反差很大的例子SELECT LEN(NABC中文字符) AS char_count, DATALENGTH(NABC中文字符) AS byte_count;这段代码返回两个完全不同的数字LEN 按字符数计算返回 7DATALENGTH 按字节数计算返回 14。关键在字符串前面的 N 前缀它把常量声明为 NVARCHAR。在 NVARCHAR 下每个中英文字符都按 2 字节存储所以字节数是字符数的两倍。如果把 N 去掉字符串变成 VARCHARDATALENGTH 的结果又会因为排序规则和编码不同而变化更容易踩坑。实际用途很清楚判断用户输入的备注是否超过业务规定的字符数应该用 LEN因为它和界面上的字数统计一致判断数据文件占多大空间、做容量评估或分库前的对齐检查用 DATALENGTH 更直接。我遇到过不少同事在检查字段长度时用了 DATALENGTH导致长度限制形同虚设——比如规定 remark 最多 50 个字符结果 25 个汉字就报超限这就是按字节做了字符校验。另一个容易忽略的行为是LEN 会忽略字符串尾部的空格而 DATALENGTH 不会。LEN(NABC ) 返回 3DATALENGTH(NABC ) 返回 6。在做去重、拼接判断时这个差异会影响结果。建议在比较前先用 RTRIM 把尾随空格处理掉否则明明看起来一样的两条数据就是匹配不上。2.2 CHARINDEX 与 SUBSTRING实现取第 N 段的通用写法编码字段经常长这样A001-技术部-张三三段由分隔符连在一起。业务上要取第二段技术部最稳的写法不是猜测固定位置而是用 CHARINDEX 定位两个分隔符再交给 SUBSTRING 截取DECLARE data NVARCHAR(100) NA001-技术部-张三; SELECT SUBSTRING( data, CHARINDEX(N-, data) 1, CHARINDEX(N-, data, CHARINDEX(N-, data) 1) - CHARINDEX(N-, data) - 1 ) AS second_part;核心是 CHARINDEX 的第三个参数起始搜索位置。第一次调用从第 1 位开始找找到第一个分隔符的位置第二次调用把起始位置设为第一个分隔符的下一位找到第二个分隔符的位置。有了这两个位置SUBSTRING 的起点是第一个分隔符 1长度是第二个分隔符位置 - 第一个分隔符位置 - 1正好把中间那段完整拿出来。这个写法的优点是能适配分隔符位置变化的数据比如 A001-技术部-张三 和 A01-技术部-李四 都能正确取到第二段。缺点是漏掉第二个分隔符时CHARINDEX 返回 0长度变成负数SUBSTRING 直接报错。所以更稳妥的做法是先判断两个分隔符都存在再截取或者在外面包一层 CASE WHEN。如果截取目标是最后一段用 RIGHT 加 CHARINDEX 反向定位更直接SELECT RIGHT(data, CHARINDEX(N-, REVERSE(data)) - 1) AS last_part;先把字符串反转找到第一个分隔符的位置再用 RIGHT 从原字符串尾部截取这个套路在解析文件路径、编号末位时非常好用。不过用之前要确认分隔符在数据里是唯一的否则结果会偏移。2.3 STRING_SPLIT 与 STRING_AGG兼容性与排序陷阱拆分和拼接是一对逆操作。拆分用 STRING_SPLIT拼接用 STRING_AGGSELECT value FROM STRING_SPLIT(NA区,B区,C区, N,); SELECT STRING_AGG(city, N、) AS city_list FROM dbo.staff WHERE dept_no D01;STRING_SPLIT 返回的列名叫 value每一行是一个拆分出来的片段通常配合 JOIN 做标签匹配STRING_AGG 则把多行合并成一个字符串常用于生成下拉选项、打印清单。注意版本边界STRING_SPLIT 在 SQL Server 2016 及以后出现STRING_AGG 到 SQL Server 2017 才提供。如果你的实例兼容级别在 2014 或更早直接调用会报对象名无效。在旧版本上我一般用 FOR XML PATH 做拼接或者写一个表值函数自己拆字符串。举个例子把 STRING_AGG 的活换成 FOR XML PATHSELECT STUFF(( SELECT N、 city FROM dbo.staff WHERE dept_no D01 ORDER BY staff_no FOR XML PATH(), TYPE ).value(N., nvarchar(max)), 1, 1, N) AS city_list;这个写法在低版本 Sql Server 上也能运行TYPE 关键字让拼接结果保持 nvarchar 类型避免 XML 转义把、这类字符改坏。另一个坑是 STRING_AGG 的拼接顺序官方不保证它在并行计划下的稳定输出。需要顺序固定时先在一个子查询里排好序再往外层聚合。3. 日期时间函数三类取值指令的取舍与时间区间统计写法日期时间处理几乎是报表需求里绕不开的环节。SQL Server 里的相关函数可以分成三类取值类解决现在几点计算类解决上个月一号是哪天格式化类解决展示成什么样子。这一章把每类里最常用的函数挑出来重点说明精度、返回类型和性能差异。日期格式化在外行人眼里像玄学其实不过是三套语法体系DATEPART、DATENAME 和 FORMAT。3.1 GETDATE / SYSDATETIME / CURRENT_TIMESTAMP先分清精度再取值新建日志表时默认值到底该写什么很多人习惯用 GETDATE()但它的精度只有毫秒级的三分之一左右。下面这三个函数返回的结果不一样函数返回类型精度适用场景GETDATE()datetime约 3.33ms通用业务时间CURRENT_TIMESTAMPdatetime约 3.33ms标准 SQL 等价写法SYSDATETIME()datetime2100ns高精度审计、流水号SELECT GETDATE() AS dt_getdate, CURRENT_TIMESTAMP AS dt_standard, SYSDATETIME() AS dt_sysdatetime;从结果可以看到SYSDATETIME 的小数位明显更长。它底层返回 datetime2精度更高适合对时间敏感的场景GETDATE 和 CURRENT_TIMESTAMP 在功能上等价只是后者是 SQL 标准语法更容易在不同数据库间迁移。日志表我一般把默认值设为 SYSDATETIME()排序和追溯都更精细业务单据里用 GETDATE() 就够了没必要为展示多存几位小数。另一个容易忽略的是 UTC 时间。系统要跨时区使用时建议用 SYSUTCDATETIME() 存标准时间展示时再换算避免不同时区的服务器写入的时间互相矛盾。很多对账差 8 小时的问题根源就是有的服务写本地时间有的写 UTC。3.2 用 DATEADD 与 DATEDIFF 计算当天/当月起点统计报表往往需要从月初到今天这样的区间。常规做法是把年份和月份拼成字符串再 CAST 成日期这个写法能跑但每次都要转换而且容易在边界值上出错。我更习惯用 DATEADD 和 DATEDIFF 组合-- 返回当月第一天的日期 SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS month_first_day; -- 把一周起点固定为周一受 DATEFIRST 影响 SET DATEFIRST 1; SELECT DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) AS week_first_day;第一行代码的原理可以拆开看DATEDIFF(MONTH, 0, GETDATE()) 计算的是从 1900-01-01 到当前时间之间跨越的整月数DATEADD 把这个月数再加回 1900-01-01结果就是当前月份的第一天。因为 1900-01-01 本身就是某月的 1 号加回去后依然落在 1 号。这个写法最大的好处是返回结果仍是日期类型可以直接参与日期比较不产生字符串转换。第二行是求本周第一天。WEEKDAY 返回的值取决于 SET DATEFIRST如果把周一定为每周第一天周一的 WEEKDAY 就是 1用 1 减去它得到 0DATEADD 加 0 天周三的 WEEKDAY 是 31 - 3 -2往前推两天回到周一。这套逻辑把星期偏移量折算成天数差。注意 DATEFIRST 是会话级设置脚本里别的地方改了它周统计结果就会漂移。想要本月最后一天可以直接用 EOMONTHSELECT EOMONTH(GETDATE()) AS month_last_day;EOMONTH 是 SQL Server 2012 及之后提供的很多低版本库没有这个函数。低版本上可以用 DATEADD(DAY, -1, DATEADD(MONTH, 1, month_first_day)) 达到同样效果。我的习惯是能用这两个函数组合就尽量不用字符串拼接减少隐式转换的机会。3.3 DATEPART / DATENAME / FORMAT要本地化还是要性能格式化日期最常见的三个选择是 DATEPART、DATENAME 和 FORMATSELECT DATEPART(YEAR, order_date) AS year_no, DATEPART(MONTH, order_date) AS month_no, DATENAME(MONTH, order_date) AS month_name, FORMAT(order_date, Nyyyy-MM-dd) AS fmt_date FROM dbo.orders;DATEPART 返回整数适合做分组键和数值比较DATENAME 返回名称比如 June、星期一适合生成人类可读的字段FORMAT 用 .NET 风格格式字符串几乎什么格式都能表达。性能上三者差距明显DATEPART 和 DATENAME 走系统内置转换开销低FORMAT 要加载 .NET 格式引擎在百万行上做一次格式化查询耗时可能成倍上涨。我在实际使用有一条原则数据库层尽量只做 DATEPART 这类轻量拆解真正要展示成 2024-06-01 12:30:00 这样的格式交给报表工具或应用层处理。如果确实要在 SQL 里格式化小结果集FORMAT 的 yyyy/MM/dd 写法很好理解。但要注意它的格式串和 CONVERT 的样式号不通用CONVERT(varchar(8), getdate(), 112) 得到 20240601换成 FORMAT 要写 FORMAT(getdate(), NyyyyMMdd)。另外无论用哪套函数都要避免在 WHERE 条件里对日期列套函数。WHERE DATEPART(YEAR, order_date) 2024 这种写法会让索引失效正确做法是写范围条件order_date 20240101 AND order_date 20250101。这一条对任何日期函数都适用。4. 聚合函数与窗口函数报表快慢的分水岭与 OVER 写法聚合函数和窗口函数是报表查询里最容易影响性能的部分。聚合函数把多行折叠成一行窗口函数则在不折叠行的情况下计算聚合结果。理解它们的分工很多为什么这个报表这么慢的问题能直接看出答案。不要把 COUNT 这类函数当成黑匣子它的语义差异足以让报表数字对不上。4.1 COUNT(*) 与 COUNT(列)不是同一个统计口径看这条最简单也最容易写错的查询SELECT COUNT(*) AS all_rows, COUNT(remark) AS rows_with_remark FROM dbo.orders;COUNT() 统计的是记录数包含所有行COUNT(remark) 只统计 remark 不为 NULL 的行。如果订单表有 1000 行其中 200 行没有备注上面两个结果分别是 1000 和 800。这个差异不是性能问题而是语义问题。写订单总数用 COUNT()写已填写备注的订单数用 COUNT(remark)两者差的就是 NULL 行。COUNT(DISTINCT 列) 是另一个高频用法用于去重统计比如统计有下单的用户数。它的成本通常比普通 COUNT 高因为 SQL Server 需要先排序或哈希去重。在超大表上做 COUNT(DISTINCT) 时如果报表对精度要求不苛刻可以用近似函数替代速度能快一个量级但口径必须和业务确认过。4.2 SUM / AVG 的空值与精度问题SUM 和 AVG 表面简单实则对 NULL 的处理非常容易引起误解SELECT SUM(order_amount) AS total_amount, AVG(order_amount) AS avg_amount, ISNULL(SUM(order_amount), 0) AS safe_total, AVG(ISNULL(order_amount, 0)) AS avg_with_zero FROM dbo.orders;第一行和第二行的结果在数据完整时没有区别但如果 order_amount 整列都是 NULLSUM 和 AVG 返回的不是 0而是 NULL。报表层拿到 NULL 后会显示成空白业务方就会质疑为什么没有合计。第三行用 ISNULL 把 SUM 兜底成 0这是常规做法。真正需要和业务对齐的是第四行的口径。AVG(ISNULL(order_amount, 0)) 把所有 NULL 都当成 0 参与平均AVG(order_amount) 本身会忽略 NULL 行、只对非空值求平均。两者在数据有缺失时结果不同。这也是不少团队的月底对账血泪经验因为口径没确认对账差出十几万。处理原则是先和业务确认 NULL 代表没有发生还是数据未录入再决定用哪种聚合口径。4.3 窗口函数ROW_NUMBER / LAG 处理分组内取值窗口函数的典型场景是每个部门里金额最高的前三名每个员工上一次订单金额是多少。传统写法往往要自连接或子查询窗口函数可以一步完成SELECT staff_no, dept_no, order_amount, ROW_NUMBER() OVER (PARTITION BY dept_no ORDER BY order_amount DESC) AS row_no, LAG(order_amount) OVER (PARTITION BY staff_no ORDER BY order_date) AS prev_amount FROM dbo.orders;ROW_NUMBER() OVER 是窗口函数里最常用的一个。PARTITION BY 把它分成若干组ORDER BY 决定组内顺序ROW_NUMBER 从 1 开始给每行编号。想取每个部门前三名就在外层套一层查询过滤 row_no 3。它和 RANK 的区别是ROW_NUMBER 遇到相同金额也会分出先后结果唯一RANK 允许并列两个第一名并列时第二名不存在下一个直接跳到第三名。函数相同值处理典型用途ROW_NUMBER严格区分编号连续分页、Top NRANK并列占用后续编号竞赛排名DENSE_RANK并列不占用后续编号分组等级LAG 用来取当前行前面的某一行的值。这里用它计算同一员工按订单日期排序后上一次订单金额是多少拿到这个值以后做环比增长就很简单。窗口函数适合在 SELECT 阶段完成计算和普通聚合最大的差异是行数不折叠每一行都保留在结果集里。另外注意 OVER 里不能直接引用 SELECT 列表里的别名需要把整个查询放到外层再引用。5. SQL Server 函数实战避坑五个高频翻车现场与排查思路函数本身没问题问题往往出在用法和上下文。下面这五类场景是排查慢查询和数据异常时最容易撞见的按现象、原因、解决的顺序写清楚每条后面补一个排查思路。5.1 隐式转换让索引失效现象一条SELECT COUNT(*) FROM orders WHERE create_date 2024-06-01在千万级表上跑出十几秒执行计划显示全表扫描。原因create_date 是 datetime 类型右侧是字符串SQL Server 在比较时做了隐式转换导致 create_date 上的索引无法正常使用。执行计划里会出现 CONVERT_IMPLICIT 提示。解决把右侧常量写成日期类型或者用范围查询代替等值。推荐写法是create_date 20240601 AND create_date 20240602既保持索引可用又避免日期格式的地区歧义。排查时打开实际执行计划查找 CONVERT_IMPLICIT 关键字能快速定位是哪个列发生了隐式转换。5.2 在 WHERE 里用函数包列索引用了也白用现象WHERE ISNULL(status, ) A在 status 有索引的表上依然扫描。原因ISNULL(status, ) 对列做了函数运算索引里存的原始列值无法直接匹配函数结果。解决改写成status A OR status IS NULL或者让应用层在写入时把空字符串统一规整为 NULL / 固定值。如果这种查询非常多可以在表上建持久化计算列、对计算列建索引再按计算列查询。除了 ISNULLLEN(column)、UPPER(column) 这类写法同样会破坏索引查找。排查时看执行计划里的 Seek Predicate如果只有 Residual 而没有 Seek就说明索引没有真正派上用场。5.3 标量函数逐行调用拖慢查询现象WHERE LEN(remark) 200在大表上 CPU 飙升执行计划里出现大量 Compute Scalar 操作。原因LEN 是标量函数SQL Server 要对每一行调用一次。把函数用在 WHERE 的列上本质上是让优化器放弃索引查找只能全表扫描后逐行过滤。解决如果备注超长是固定的业务规则可以新增持久化计算列 remark_len建索引查询条件换成WHERE remark_len 200。在没有计算列的情况下把核心大表上的逐行运算挪到应用层先查主键集合再回表取详情。排查时看执行计划里 Compute Scalar 的代价占比如果超过一半基本就是标量函数在拖后腿。5.4 CONVERT 遇到脏数据直接中断现象导入外部编码表时日期列里有 2024-02-30 之类的非法日期CONVERT(date, col) 直接把整个批次事务回滚。原因CONVERT 在转换失败时抛错TRY_CONVERT 则返回 NULL。对批量导入来说一行的错误就能影响整批。解决把用 CONVERT 的地方换成 TRY_CONVERT先让它返回 NULL再在后续逻辑里过滤或修正。如果必须保留原始错误信息就用 CASE 判断 TRY_CONVERT 结果是否为 NULL单独把脏数据行挑出来。导入前先执行一遍SELECT TRY_CONVERT(date, raw_value) AS parsed, raw_value FROM staging把 NULL 的行导出成问题清单比直接在导入脚本里反复试错要快得多。5.5 STRING_AGG 拼接顺序随机现象同一份部门成员名单每次查询出来的顺序都不同应用端渲染出来的下拉框跟着跳。原因STRING_AGG 不承诺输出顺序并行执行计划下拼接顺序具有随机性。数据量小的时候看着稳定一旦执行计划选了并行顺序就变了。解决先对数据源做一次 ORDER BY把排序列传递到聚合里。标准写法是在子查询里先排序外层再用 STRING_AGG。如果实例兼容级别较旧没有 STRING_AGG用 FOR XML PATH 加 STUFF 拼接顺序可控只是代码会长一些。排查时把两次查询的实际执行计划并排对比重点看有没有并行运算符有的话基本就确定是计划变化导致顺序漂移。6. 把函数组合成查询工具箱三个高频场景的组装写法单个函数解决单点问题组合起来才能应对真实报表。这里给三个反复用到的函数拼装写法都可以直接抄走改参数。6.1 生成连续日期序列补全报表缺口报表最怕某天没有数据就不显示这一行。用递归 CTE 先生成日期序列再 LEFT JOIN 业务表缺口就补上了;WITH day_seq AS ( SELECT CAST(GETDATE() AS DATE) AS dt, 1 AS lvl UNION ALL SELECT DATEADD(DAY, -1, dt), lvl 1 FROM day_seq WHERE lvl 30 ) SELECT ds.dt, ISNULL(SUM(o.order_amount), 0) FROM day_seq ds LEFT JOIN dbo.orders o ON o.order_date ds.dt GROUP BY ds.dt ORDER BY ds.dt;lvl 控制生成天数30 就是最近 30 天。递归 CTE 在 100 层以内性能可控超过 100 层会碰到默认 MAXRECURSION 限制建议改用数字表或系统视图生成序列。6.2 安全转换后再兜底外部数据导入最容易出现非法日期和非法数值。TRY_CONVERT 返回 NULLCOALESCE 负责给默认值一个表达式搞定SELECT COALESCE(TRY_CONVERT(date, raw_value), 1900-01-01) AS safe_date FROM dbo.staging_import;转换失败变成 NULLCOALESCE 再把 NULL 替换成统一默认日期导入脚本不会因为一行脏数据中断。数值转换同理TRY_CAST 和 COALESCE 配合使用。6.3 空字符串与 NULL 一起处理业务录入接口经常把未填写存成空字符串另一些场景存成 NULL。查询时想把两者都归一化成无备注SELECT COALESCE(NULLIF(TRIM(remark), N), N无备注) AS remark_display FROM dbo.orders;NULLIF 把空字符串转成 NULLCOALESCE 再把 NULL 换成默认文案TRIM 去掉首尾空格三种脏数据形态一次性兜住。前阵子帮同事排查一张月报慢的根源就是 WHERE 里包了一层 ISNULL执行计划里扫了整张表。改掉写法以后跑进一秒同事自己也感慨这种错误最可惜查起来不算难但容易先入为主地觉得函数用上就完事了。我现在的习惯是写完一条 SQL先问三件事这个列被函数包住了吗转换是显式的吗NULL 的语义和业务对上了吗这套检查花不了一分钟但能省掉很多上线后的后悔药。希望帮到你。本文还有配套的精品资源点击获取