简介这份SQL Server函数大全精析文档面向数据库开发、运维人员及SQL初学者系统梳理T-SQL函数体系帮助读者解决函数分类不清、确定性与非确定性混淆、变量传参不熟等常见问题。资源包为1个doc文档约859KB内容紧凑便于离线查阅与随查随用。文档按聚合、配置、转换、加密、游标、日期时间、数学、元数据、排名、行集、安全、字符串、系统、系统统计、文本图像等类别逐一说明函数作用并重点讲解确定性函数如AVG、CAST、CONVERT、DATEADD、DATEDIFF、ASCII、CHAR、SUBSTRING与非确定性函数如GETDATE、RAND、ERROR、CURSORSTATUS的差异以及用户变量在函数中的输入输出用法配合SET与SELECT赋值示例演示SQRT等函数的调用与返回值捕获。目前已有1190人学习适合需要系统掌握SQL Server函数、优化查询与编写高效数据库代码的读者参考。1. SQL Server 函数大全精析从“能跑”到“跑得稳”的分水岭很多人第一次接触 SQL Server 函数是在写报表或者做数据清洗的时候。SELECT 里嵌套几个 GETDATE、ISNULL、CAST查询能跑通结果也对于是就觉得“函数嘛会用就行”。但真正在生产环境里翻过车的人都知道函数用得好不好直接决定一条 SQL 是毫秒级返回还是把 CPU 打满。我见过一个日增百万行的订单表因为 WHERE 里对日期列套了一层 CONVERT索引直接失效查询从 200ms 变成 40 秒。SQL Server 函数大全精析这件事核心不是背函数名而是搞清楚三类函数——标量函数、聚合函数、窗口函数——各自在什么场景下用、对执行计划有什么影响、参数怎么传才不会踩坑。这篇内容适合已经会写基本 T-SQL、但想把手上的查询从“能跑”推到“跑得稳”的开发者也适合正在做慢查询治理的 DBA。接下来我会按字符串、日期、转换、聚合、窗口、排名这几个最常用的类别把语法、参数、典型坑和替代方案一次讲透。2. 字符串与日期函数最常用也最容易埋雷字符串和日期函数是日常写 SQL 绕不开的两类。它们看起来简单但参数里的隐式转换、排序规则、语言设置每一个都能让结果和预期不一样。这一章先把这两类的核心函数拆开再落到具体写法上。2.1 字符串函数的核心参数与返回值陷阱先看一组最常用的字符串函数。LEN 返回字符数但会忽略尾部空格DATALENGTH 返回字节数不忽略空格。这两个函数在判断“字段是否为空”时经常被混用结果就是明明存了空格LEN 却返回 0。-- 演示 LEN 与 DATALENGTH 的差异 DECLARE s NVARCHAR(50) Nabc ; -- 尾部三个空格 SELECT LEN(s) AS LenResult, -- 返回 3忽略尾部空格 DATALENGTH(s) AS DataLenResult; -- 返回 12NVARCHAR 每字符 2 字节含空格逻辑说明LEN 在计算长度时会去掉尾部空格DATALENGTH 不会。参数说明s 定义为 NVARCHAR所以 DATALENGTH 返回的是字节数如果是 VARCHAR 则每个字符 1 字节。实际排查“字段看起来为空但条件不成立”时优先用 DATALENGTH 确认是否存了不可见字符。SUBSTRING 的第三个参数是长度不是结束位置。很多人从其他语言转过来习惯写 SUBSTRING(s, 3, 5) 以为是取第 3 到第 5 个字符实际是取从第 3 位开始往后 5 个字符。这个差异在截取身份证号、手机号时特别容易出错。CHARINDEX 和 PATINDEX 的区别也值得记一笔。CHARINDEX 找的是精确子串PATINDEX 支持通配符。如果要在文本里找“第几个逗号出现”用 CHARINDEX如果要找“第一个数字出现的位置”用 PATINDEX(%[0-9]%, s)。还有一个高频坑是字符串拼接。加号拼接时只要有一个操作数是 NULL整个结果就是 NULL。做报表拼接地址、姓名时必须用 ISNULL 或 CONCAT。CONCAT 会自动把 NULL 当空字符串处理这是它比加号更安全的地方。-- 拼接时处理 NULL 的两种写法 DECLARE a VARCHAR(20) 北京; DECLARE b VARCHAR(20) NULL; SELECT a - b AS PlusResult; -- 返回 NULL SELECT CONCAT(a, -, b) AS ConcatResult; -- 返回 北京-参数说明CONCAT 接受 2 到 254 个参数自动将 NULL 转为空字符串。如果需要对结果做进一步格式化可以在外层再套 FORMAT 或 CAST。2.2 日期函数的精度、边界与语言依赖日期函数里第一个要分清的是 GETDATE、SYSDATETIME、SYSUTCDATETIME。GETDATE 返回 DATETIME精度约 3.33 毫秒SYSDATETIME 返回 DATETIME2精度 100 纳秒。做高频交易或日志记录时用 SYSDATETIME 才能避免同一毫秒内多条记录时间戳重复。DATEADD 和 DATEDIFF 是计算日期差和偏移的主力。DATEDIFF 的边界计算经常被误解它统计的是“跨越了多少个边界”不是“完整间隔”。比如 DATEDIFF(YEAR, 2023-12-31, 2024-01-01) 返回 1虽然只差一天但跨了一个年边界。-- DATEDIFF 边界计算演示 SELECT DATEDIFF(YEAR, 2023-12-31, 2024-01-01) AS YearDiff, -- 返回 1 DATEDIFF(DAY, 2023-12-31, 2024-01-01) AS DayDiff, -- 返回 1 DATEDIFF(MONTH, 2023-01-31, 2023-02-01) AS MonthDiff; -- 返回 1逻辑说明做账期、工龄、账龄计算时如果按“满一年才算一年”的口径不能直接用 DATEDIFF(YEAR)需要结合月份和日期做二次判断。参数说明DATEDIFF 的第一个参数是日期部分支持 year、quarter、month、dayofyear、day、week、hour、minute、second、millisecond、microsecond、nanosecond。FORMAT 函数在展示层很好用但它依赖 CLR性能比 CONVERT 差一个数量级。在 WHERE 条件或 JOIN 条件里用 FORMAT基本等于主动放弃索引。常见做法是存储和计算用 CONVERT只在最终输出给前端时用 FORMAT。日期语言依赖也是个大坑。SET LANGUAGE 会影响 DATENAME 和 FORMAT 的月份、星期输出。如果服务器默认语言是英文DATENAME(MONTH, GETDATE()) 返回 “January”而业务代码可能期望“一月”。跨库、跨服务器迁移时这类问题会集中爆发。注意在 WHERE 条件中对日期列使用 CONVERT、FORMAT、DATEPART 等函数会导致该列上的索引无法被有效使用。正确做法是把函数作用在常量侧或者用日期范围条件替代。3. 转换与聚合函数类型对齐和 NULL 处理决定成败转换函数和聚合函数是 SQL Server 里“看起来简单、用起来事多”的典型。转换决定了数据能不能正确比较和计算聚合决定了分组结果是否符合业务口径。这一章把 CAST、CONVERT、TRY_CAST、TRY_CONVERT 的选型讲清楚再把聚合函数里 NULL 和重复计数的坑挖出来。3.1 CAST、CONVERT、TRY_CAST 的选型与隐式转换风险CAST 是 ANSI 标准写法CONVERT 是 SQL Server 特有写法多了一个 style 参数用于日期和货币格式化。两者在功能上大部分重叠但 CONVERT 在日期格式化上更灵活。-- CAST 与 CONVERT 的典型用法 SELECT CAST(2024-01-15 AS DATE) AS CastDate, CONVERT(VARCHAR(10), GETDATE(), 120) AS ConvertDate, -- 120 样式yyyy-mm-dd CONVERT(VARCHAR(20), 1234.5678, 1) AS ConvertMoney; -- 1 样式千分位逗号参数说明CONVERT 的 style 参数对日期类型影响很大120 是 yyyy-mm-dd hh:mi:ss112 是 yyyymmdd126 是 ISO8601。做接口对接时建议统一用 126 或 120避免区域设置干扰。TRY_CAST 和 TRY_CONVERT 是 SQL Server 2012 之后引入的安全转换函数。转换失败时返回 NULL而不是直接抛错。在清洗外部导入数据时这两个函数能避免整批作业因为一行脏数据而中断。-- 安全转换失败返回 NULL SELECT TRY_CAST(abc AS INT) AS TryCastResult, -- 返回 NULL TRY_CONVERT(DATE, 2024-13-01) AS TryConvertResult; -- 返回 NULL逻辑说明TRY_CAST 适合在 SELECT 列表里做容错展示TRY_CONVERT 适合在 WHERE 或 JOIN 里做条件过滤。注意TRY_CAST 返回 NULL 后后续计算仍然可能因为 NULL 传播而得到非预期结果需要配合 ISNULL 使用。隐式转换是性能杀手里最隐蔽的一种。当 VARCHAR 列和 NVARCHAR 常量比较时SQL Server 会把 VARCHAR 列隐式转成 NVARCHAR导致索引扫描。常见于用英文版客户端连中文库、或者 ORM 框架参数类型不匹配的场景。-- 隐式转换导致索引失效的典型写法 -- 假设 OrderNo 是 VARCHAR(50) 且有索引 SELECT * FROM Orders WHERE OrderNo NSO20240115; -- 常量带 N触发隐式转换 -- 修正写法常量类型与列类型一致 SELECT * FROM Orders WHERE OrderNo SO20240115;参数说明判断是否存在隐式转换可以看执行计划里的 CONVERT_IMPLICIT 警告。在 SSMS 里开启“包含实际执行计划”如果看到某列上有 CONVERT_IMPLICIT基本就是类型不匹配。3.2 聚合函数中 NULL 与重复计数的处理COUNT(*) 统计所有行COUNT(列名) 忽略该列为 NULL 的行COUNT(DISTINCT 列名) 统计去重后的非 NULL 值。这三个在报表里经常被混用导致口径对不上。-- COUNT 三种写法的差异 DECLARE t TABLE (id INT, category VARCHAR(10)); INSERT INTO t VALUES (1, A), (2, A), (3, NULL), (4, B), (5, NULL); SELECT COUNT(*) AS AllRows, -- 返回 5 COUNT(category) AS NonNullRows, -- 返回 3 COUNT(DISTINCT category) AS DistinctRows; -- 返回 2逻辑说明做“有效订单数”时如果订单状态列可能为 NULL用 COUNT(状态列) 会漏掉 NULL 行如果业务上 NULL 代表“未处理”那它应该被计入总数此时用 COUNT()。参数说明COUNT 的参数可以是列名、常量或表达式COUNT(1) 和 COUNT() 在 SQL Server 里执行计划相同性能没有差异。SUM 和 AVG 同样忽略 NULL。AVG 的分母是非 NULL 行数不是总行数。如果业务口径要求“按总行数平均”需要显式写 SUM(列)/COUNT(*)。-- AVG 忽略 NULL 的演示 DECLARE scores TABLE (student VARCHAR(10), score INT); INSERT INTO scores VALUES (A, 90), (B, NULL), (C, 80); SELECT AVG(score) AS AvgIgnoreNull, -- 返回 85分母是 2 SUM(score) / COUNT(*) AS AvgAllRows; -- 返回 56分母是 3参数说明AVG 的返回值类型取决于输入列类型INT 列的 AVG 会返回 INT小数部分被截断。需要精确平均时先把列 CAST 成 DECIMAL。GROUP BY 里对 NULL 的处理也值得注意。GROUP BY 会把所有 NULL 归为一组但 NULL 不等于 NULL所以在 JOIN 或去重时不能用 比较。需要把 NULL 当独立分组时GROUP BY 是安全的需要把 NULL 当空字符串处理时先用 ISNULL 转换。注意在聚合查询中如果 SELECT 列表里的非聚合列没有全部出现在 GROUP BY 中SQL Server 会直接报错。这和 MySQL 的宽松模式不同迁移 SQL 时需要特别检查。4. 窗口与排名函数分组计算不丢明细的正确姿势窗口函数是 SQL Server 里最能体现“会不会写 SQL”的一类。它能在不折叠明细行的前提下做分组计算适合做累计、排名、同比环比。这一章把 OVER 子句的三个组成部分拆开再讲 ROW_NUMBER、RANK、DENSE_RANK 的选型差异。4.1 OVER 子句的 PARTITION BY、ORDER BY 与框架窗口函数的语法核心是 OVER 子句它由三部分组成PARTITION BY 定义分组ORDER BY 定义排序ROWS/RANGE 定义窗口框架。PARTITION BY 类似 GROUP BY但不折叠行。-- 按部门计算工资排名和部门平均工资 SELECT EmployeeName, Department, Salary, AVG(Salary) OVER (PARTITION BY Department) AS DeptAvgSalary, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum FROM Employees;逻辑说明AVG(Salary) OVER (PARTITION BY Department) 会在每一行后面附加该部门的平均工资明细行不丢失。ROW_NUMBER 按部门内工资降序编号。参数说明PARTITION BY 可以省略省略时整个结果集作为一个窗口ORDER BY 在排名函数里必须写在聚合窗口函数里可选。窗口框架 ROWS BETWEEN ... AND ... 是进阶用法。做累计求和时默认框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW但 RANGE 在遇到重复排序值时会合并计算ROWS 则严格按行号。-- 累计求和ROWS 与 RANGE 的差异 SELECT OrderDate, Amount, SUM(Amount) OVER (ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotalRows, SUM(Amount) OVER (ORDER BY OrderDate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotalRange FROM Orders;参数说明ROWS 按物理行逐行累计RANGE 按排序值分组累计。如果同一天有多笔订单ROWS 会逐笔累加RANGE 会把同一天的所有订单一次性累加。做财务报表时通常用 ROWS 更符合“逐笔累计”的直觉。4.2 ROW_NUMBER、RANK、DENSE_RANK 的选型与去重实战这三个排名函数的差异在面试里被问烂了但实际用错的人依然不少。ROW_NUMBER 不管并列1、2、3、4RANK 并列后跳号1、2、2、4DENSE_RANK 并列后不跳号1、2、2、3。-- 三种排名函数的对比 DECLARE t TABLE (name VARCHAR(10), score INT); INSERT INTO t VALUES (A, 100), (B, 90), (C, 90), (D, 80); SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS RowNum, RANK() OVER (ORDER BY score DESC) AS RankNum, DENSE_RANK() OVER (ORDER BY score DESC) AS DenseRankNum FROM t;逻辑说明ROW_NUMBER 适合做分页和去重因为每行编号唯一RANK 适合做“并列第几”的展示DENSE_RANK 适合做“有多少个不同档次”的统计。参数说明这三个函数都要求 OVER 子句里必须有 ORDER BY。用 ROW_NUMBER 做去重是高频操作。比如同一客户有多条重复记录只保留最新一条。-- 用 ROW_NUMBER 去重保留每个客户的最新记录 WITH RankedData AS ( SELECT CustomerID, OrderDate, Amount, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn FROM Orders ) SELECT CustomerID, OrderDate, Amount FROM RankedData WHERE rn 1;参数说明PARTITION BY CustomerID 按客户分组ORDER BY OrderDate DESC 让最新记录排第一外层筛选 rn 1 即保留最新。注意CTE 里的 ROW_NUMBER 是在筛选之前计算的所以不能直接在 WHERE 里用 rn 1必须套一层 CTE 或子查询。NTILE 函数用于把数据分成 N 个桶做百分位分析时很有用。NTILE(4) 会把结果集分成四等份返回 1 到 4 的桶号。数据量不能整除时前面的桶会多一行。LAG 和 LEAD 用于访问当前行之前或之后的行做同比环比时不用自连接。-- 用 LAG 计算环比 SELECT Month, Sales, LAG(Sales, 1, 0) OVER (ORDER BY Month) AS PrevMonthSales, Sales - LAG(Sales, 1, 0) OVER (ORDER BY Month) AS MoMChange FROM MonthlySales;参数说明LAG 的第二个参数是偏移量第三个参数是默认值当没有前一行时返回。LEAD 方向相反。这两个函数在 SQL Server 2012 及以上版本可用。注意窗口函数不能出现在 WHERE 子句里因为 WHERE 的执行顺序在 SELECT 之前。需要过滤窗口函数结果时必须用 CTE 或子查询包一层。5. 函数性能避坑与常见问题排查函数用错轻则结果不对重则查询跑不动。这一章把最常见的五类问题按“现象 → 原因 → 解决”整理出来都是实际排查中反复遇到的。5.1 标量函数导致索引失效与 CPU 飙升现象一条原本走索引查找的查询在 WHERE 里对索引列加了 ISNULL 或 CONVERT 之后变成全表扫描逻辑读从几十涨到几万。原因SQL Server 优化器无法对“列被函数包裹”的表达式使用索引查找。即使函数是确定性的优化器也不会自动展开。解决把函数作用在常量侧或者用计算列加索引。比如 WHERE ISNULL(Status, ) Active 改成 WHERE (Status Active OR Status IS NULL)。如果业务上必须用函数考虑建持久化计算列并在上面建索引。-- 不推荐函数包裹索引列 SELECT * FROM Orders WHERE YEAR(OrderDate) 2024; -- 推荐范围条件替代函数 SELECT * FROM Orders WHERE OrderDate 2024-01-01 AND OrderDate 2025-01-01;参数说明范围条件写法能命中 OrderDate 上的索引且结果等价。注意边界要用 和 避免 BETWEEN 在日期时间类型上漏掉当天最后一秒的数据。5.2 隐式转换让索引悄悄失效现象查询条件看起来没问题但执行计划里出现 CONVERT_IMPLICIT索引查找变成索引扫描。原因列类型和参数类型不一致SQL Server 按数据类型优先级把低优先级列转成高优先级类型。VARCHAR 列遇到 NVARCHAR 常量时列会被转成 NVARCHAR。解决统一参数类型。在应用层确保传参类型和数据库列类型一致。用 ORM 时检查映射配置避免框架默认用 Unicode 字符串。-- 排查隐式转换查看执行计划 XML SET SHOWPLAN_XML ON; GO SELECT * FROM Orders WHERE OrderNo NSO20240115; GO SET SHOWPLAN_XML OFF;参数说明在返回的 XML 里搜索 CONVERT_IMPLICIT如果出现在索引列上就是隐式转换。解决方式是去掉常量前的 N或者把列改成 NVARCHAR。5.3 聚合查询中 NULL 导致的计数偏差现象报表里“有效记录数”和“总记录数”对不上差异行数正好等于某列 NULL 的行数。原因COUNT(列名) 忽略 NULLCOUNT(*) 不忽略。开发时随手写了 COUNT(列名)但业务口径要求统计所有行。解决明确业务口径。如果 NULL 代表“未填写”但记录仍然有效用 COUNT(*)如果 NULL 代表“无效”用 COUNT(列名)。在报表 SQL 里加注释说明口径。-- 明确计数口径 SELECT COUNT(*) AS TotalRecords, COUNT(ValidField) AS ValidRecords, COUNT(*) - COUNT(ValidField) AS NullFieldRecords FROM SomeTable;参数说明第三个表达式能快速定位 NULL 行数便于和业务方核对。5.4 窗口函数排序不稳定导致分页重复现象分页查询时同一行数据在第 1 页和第 2 页都出现或者某些行完全没出现。原因ROW_NUMBER 的 ORDER BY 列有重复值排序不稳定每次执行的行号可能不同。解决在 ORDER BY 里加上唯一列如主键作为决胜列保证排序确定。-- 不稳定的分页排序 ROW_NUMBER() OVER (ORDER BY CreateTime DESC) AS rn -- 稳定的分页排序 ROW_NUMBER() OVER (ORDER BY CreateTime DESC, Id DESC) AS rn参数说明Id 是主键或唯一列加上之后排序结果确定分页不会重复或遗漏。5.5 日期函数受语言和区域设置影响现象同一段 SQL 在开发机返回“一月”在服务器返回“January”或者日期解析在跨区域环境报错。原因SET LANGUAGE 和服务器区域设置影响 DATENAME、FORMAT 和隐式日期解析。解决展示层用 FORMAT 时显式指定 culture 参数或者统一用 CONVERT 的 style 编号。日期常量统一用 yyyy-mm-dd 或 yyyymmdd 格式避免用 “01/02/2024” 这种有歧义的写法。-- 显式指定 culture 的 FORMAT 写法 SELECT FORMAT(GETDATE(), yyyy-MM-dd, zh-CN) AS FormattedDate; -- 无歧义的日期常量 SELECT * FROM Orders WHERE OrderDate 20240101;参数说明FORMAT 的第三个参数是 culture指定后不受服务器语言影响。日期常量用 yyyymmdd 格式在任何语言设置下都能正确解析。注意在跨库、跨服务器、跨区域迁移时优先检查 SET LANGUAGE、DATEFORMAT 和默认语言设置。这三项不一致是日期类问题的主要来源。6. 把函数用对从执行计划反推写法写了这么多年 SQL我最大的习惯是任何一条涉及函数的生产查询上线前必须看一次实际执行计划。函数用得对不对执行计划不会骗人。这一章讲几个我常用的验证手法以及怎么从计划里反推函数写法。第一个手法是用 SET STATISTICS IO 和 SET STATISTICS TIME 对比改写前后的逻辑读和 CPU 时间。逻辑读是判断索引是否被有效使用的硬指标。如果改写后逻辑读从几万降到几十说明索引用上了。-- 对比两种写法的 IO 开销 SET STATISTICS IO ON; SET STATISTICS TIME ON; -- 写法一函数包裹列 SELECT COUNT(*) FROM Orders WHERE YEAR(OrderDate) 2024; -- 写法二范围条件 SELECT COUNT(*) FROM Orders WHERE OrderDate 2024-01-01 AND OrderDate 2025-01-01; SET STATISTICS IO OFF; SET STATISTICS TIME OFF;参数说明在“消息”选项卡里看“逻辑读取”次数。写法一通常是全表扫描逻辑读等于表页数写法二如果是索引查找逻辑读会少很多。第二个手法是用 DMV 查历史上哪些查询因为函数导致高开销。sys.dm_exec_query_stats 里记录了每个查询计划的执行次数、总 CPU 时间和总逻辑读。结合 sys.dm_exec_sql_text 可以拿到原始 SQL。-- 查找 CPU 时间最高的查询 SELECT TOP 10 qs.total_worker_time / qs.execution_count AS AvgCPU, qs.total_logical_reads / qs.execution_count AS AvgReads, qs.execution_count, SUBSTRING(st.text, (qs.statement_start_offset / 2) 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) 1) AS QueryText FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st ORDER BY AvgCPU DESC;参数说明AvgCPU 是平均 CPU 时间微秒AvgReads 是平均逻辑读。如果某个查询的 AvgReads 远高于表页数且 QueryText 里有函数包裹列基本可以锁定问题。第三个手法是用查询存储Query Store对比同一查询在不同写法下的计划变化。查询存储会保留历史计划能直观看到函数改写后计划从扫描变成查找。-- 开启查询存储如果尚未开启 ALTER DATABASE YourDatabase SET QUERY_STORE ON; ALTER DATABASE YourDatabase SET QUERY_STORE (OPERATION_MODE READ_WRITE); -- 查看某查询的计划变化 SELECT q.query_id, p.plan_id, p.query_plan, rs.avg_logical_io_reads, rs.count_executions FROM sys.query_store_query q JOIN sys.query_store_plan p ON q.query_id p.query_id JOIN sys.query_store_runtime_stats rs ON p.plan_id rs.plan_id WHERE q.query_id 12345 ORDER BY rs.count_executions DESC;参数说明query_plan 是图形化执行计划的 XMLavg_logical_io_reads 是平均逻辑读。对比不同 plan_id 的 IO 差异能直接看出哪种写法更优。最后一个习惯是任何在 WHERE、JOIN、ORDER BY 里出现的函数都问自己一句“能不能把函数移到常量侧”。能移就移不能移就评估是否值得建计算列索引。这个习惯帮我避免了很多次上线后的性能告警。希望帮到你。本文还有配套的精品资源点击获取