1. SQL Server函数全景解析作为一名常年与SQL Server打交道的数据库工程师我深知函数库的掌握程度直接决定了开发效率。SQL Server提供了丰富多样的内置函数从基础的字符串处理到复杂的日期计算这些函数就像工具箱里的各种工具用对了事半功倍用错了可能引发灾难性后果。今天我要分享的是经过多年实战检验的SQL Server函数大全包含最新版本SQL Server 2019/2022新增的函数特性。不同于官方文档的冰冷罗列我会结合真实业务场景告诉你哪些函数最常用、哪些组合能解决棘手问题以及那些官方文档里不会写的性能陷阱。2. 日期时间函数深度应用2.1 基础转换与格式化日期处理是SQL中最容易出错的领域之一。先看最常用的CONVERT函数-- 基本日期格式转换 SELECT CONVERT(VARCHAR, GETDATE(), 120) AS ODBC规范格式, -- 2023-07-25 14:30:22 CONVERT(VARCHAR, GETDATE(), 112) AS 纯数字日期, -- 20230725 CONVERT(VARCHAR, GETDATE(), 113) AS 欧洲格式 -- 25 Jul 2023 14:30:22注意CONVERT的style参数有20多种预设值120是最常用的ODBC规范格式适合作为中间格式在不同系统间传递SQL Server 2012引入的FORMAT函数让日期格式化更直观-- 使用.NET风格格式化 SELECT FORMAT(GETDATE(), yyyy-MM-dd HH:mm:ss) AS 标准格式, FORMAT(GETDATE(), MMMM dd, yyyy, en-US) AS 英文月份, FORMAT(GETDATE(), dd/MM/yyyy, fr-FR) AS 法国格式2.2 日期计算与边界处理日期加减使用DATEADD函数但要注意月末的特殊情况-- 处理月末日期加减 DECLARE endOfMonth DATE EOMONTH(GETDATE()) SELECT DATEADD(DAY, 1, endOfMonth) AS 下个月第一天, DATEADD(MONTH, -1, endOfMonth) AS 上月最后一天计算日期差时DATEDIFF有个重要陷阱-- 计算年龄错误示范 SELECT DATEDIFF(YEAR, 2000-02-29, 2023-02-28) AS 错误年龄 -- 返回23实际应为22 -- 正确计算方法 SELECT DATEDIFF(YEAR, 2000-02-29, 2023-02-28) - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, 2000-02-29, 2023-02-28), 2000-02-29) 2023-02-28 THEN 1 ELSE 0 END AS 正确年龄2.3 业务场景实战处理财务周期时这个函数组合特别有用-- 获取当前财季的开始和结束日期 DECLARE today DATE GETDATE() SELECT DATEFROMPARTS(YEAR(today), (DATEPART(QUARTER, today)-1)*31, 1) AS 季初, EOMONTH(DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(today), (DATEPART(QUARTER, today)-1)*31, 1))) AS 季末3. 字符串处理艺术3.1 基础操作与性能对比字符串拼接的三种方式及其性能特点-- 1. 运算符SQL Server 2016前唯一选择 SELECT 用户 CAST(UserID AS VARCHAR) 登录时间: CONVERT(VARCHAR, LoginTime, 120) FROM Logins -- 2. CONCAT函数自动处理NULL值 SELECT CONCAT(用户, UserID, 登录时间:, LoginTime) FROM Logins -- 3. STRING_AGGSQL Server 2017 分组拼接 SELECT UserID, STRING_AGG(CONVERT(VARCHAR, LoginTime, 120), |) WITHIN GROUP (ORDER BY LoginTime) AS LoginTimes FROM Logins GROUP BY UserID性能提示大量字符串拼接时CONCAT比运算符快约30%因为减少了中间结果的类型转换3.2 高级文本处理处理JSON数据时STRING_ESCAPE非常有用-- 构建JSON安全字符串 SELECT STRING_ESCAPE(This text has special\ncharacters, json) AS JsonSafeString模式匹配的进阶用法-- 提取字符串中的数字 DECLARE text NVARCHAR(100) 订单号ABC20230725X SELECT SUBSTRING(text, PATINDEX(%[0-9]%, text), PATINDEX(%[0-9][^0-9]%, text x) - PATINDEX(%[0-9]%, text) 1) AS ExtractedNumber3.3 字符串分片技术拆分CSV字符串的高效方法SQL Server 2016-- 使用STRING_SPLIT函数 DECLARE csv VARCHAR(100) apple,orange,banana,grape SELECT value AS Fruit FROM STRING_SPLIT(csv, ,) -- 传统XML方法兼容旧版本 DECLARE xml XML CAST(i REPLACE(csv, ,, /ii) /i AS XML) SELECT i.value(., VARCHAR(50)) AS Fruit FROM xml.nodes(//i) AS x(i)4. 数学与统计函数精要4.1 数值处理陷阱ROUND函数的银行家舍入规则-- 四舍六入五成双 SELECT ROUND(12.5, 0) AS 结果为12, -- 0.5部分舍去 ROUND(13.5, 0) AS 结果为14 -- 0.5部分进位精确计算时应使用DECIMAL类型-- 浮点数精度问题 SELECT 0.1 0.2 AS FloatResult, -- 0.30000000000000004 CAST(0.1 AS DECIMAL(10,2)) CAST(0.2 AS DECIMAL(10,2)) AS DecimalResult -- 0.304.2 统计函数进阶窗口函数的强大应用-- 计算移动平均 SELECT TradeDate, ClosingPrice, AVG(ClosingPrice) OVER (ORDER BY TradeDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg3Day FROM StockPrices百分位计算SQL Server 2012-- 计算销售金额的90分位 SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY SaleAmount) OVER() AS Percentile90 FROM Sales5. 聚合函数与分组技巧5.1 高级分组操作GROUPING SETS的实用案例-- 多维度汇总 SELECT COALESCE(Region, 所有地区) AS Region, COALESCE(ProductCategory, 所有类别) AS Category, SUM(SalesAmount) AS TotalSales FROM SalesData GROUP BY GROUPING SETS ( (Region, ProductCategory), (Region), (ProductCategory), () )5.2 条件聚合的多种实现计算不同价格区间的商品数量-- 方法1使用多个COUNTCASE SELECT COUNT(CASE WHEN Price 100 THEN 1 END) AS 低于100, COUNT(CASE WHEN Price BETWEEN 100 AND 500 THEN 1 END) AS 100-500, COUNT(CASE WHEN Price 500 THEN 1 END) AS 高于500 FROM Products -- 方法2使用FILTER子句SQL Server 2019 SELECT COUNT(*) FILTER(WHERE Price 100) AS 低于100, COUNT(*) FILTER(WHERE Price BETWEEN 100 AND 500) AS 100-500, COUNT(*) FILTER(WHERE Price 500) AS 高于500 FROM Products6. 系统函数与元数据查询6.1 会话信息获取获取当前连接信息SELECT SESSION_USER AS 当前用户, ORIGINAL_LOGIN() AS 原始登录名, CONNECTIONPROPERTY(client_net_address) AS 客户端IP, APP_NAME() AS 应用程序名6.2 对象元数据查询查找包含特定列的所有表SELECT t.name AS TableName, s.name AS SchemaName FROM sys.tables t JOIN sys.schemas s ON t.schema_id s.schema_id JOIN sys.columns c ON t.object_id c.object_id WHERE c.name LIKE %Customer%7. 函数性能优化指南7.1 常见性能陷阱标量函数在WHERE子句中的性能问题-- 错误用法导致全表扫描 SELECT * FROM Orders WHERE dbo.GetOrderStatus(OrderID) Completed -- 正确做法使用JOIN或内联计算 SELECT o.* FROM Orders o JOIN OrderStatus os ON o.OrderID os.OrderID WHERE os.Status Completed7.2 函数替代方案用CROSS APPLY替代复杂的计算-- 解析JSON字符串的高效方法 SELECT o.OrderID, j.ProductID, j.Quantity FROM Orders o CROSS APPLY OPENJSON(o.OrderDetails) WITH ( ProductID INT $.ProductID, Quantity INT $.Quantity ) j8. 新版函数特性解析8.1 SQL Server 2022新函数GREATEST/LEAST函数的实用案例-- 计算多列最大值 SELECT OrderID, GREATEST(OrderDate, RequiredDate, ShippedDate) AS LatestDate, LEAST(OrderDate, RequiredDate, ShippedDate) AS EarliestDate FROM Orders8.2 位操作函数增强新的位操作函数SQL Server 2016-- 权限掩码检查 DECLARE permission INT 5 -- 二进制0101 SELECT BIT_COUNT(permission) AS TotalFlags, GET_BIT(permission, 2) AS HasWritePermission -- 返回1表示有写权限9. 实战函数组合案例9.1 数据清洗流水线清洗杂乱地址数据的完整方案UPDATE Customers SET Address TRIM(REPLACE(REPLACE(Address, CHAR(13), ), CHAR(10), )), PostalCode CASE WHEN PostalCode LIKE [0-9][0-9][0-9][0-9][0-9] THEN PostalCode WHEN PostalCode LIKE [0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9] THEN PostalCode ELSE NULL END WHERE Address IS NOT NULL9.2 动态SQL生成器根据参数生成动态查询DECLARE sql NVARCHAR(MAX), columns NVARCHAR(100) ProductID, ProductName, UnitPrice SET sql CONCAT( SELECT , columns, FROM Products WHERE CategoryID category, CASE WHEN minPrice IS NOT NULL THEN AND UnitPrice minPrice ELSE END, ORDER BY , ISNULL(sortColumn, ProductName) ) EXEC sp_executesql sql, Ncategory INT, minPrice MONEY, category 5, minPrice 10.0010. 函数调试与错误处理10.1 常见错误排查日期转换错误处理模式-- 安全日期转换函数 CREATE FUNCTION dbo.SafeConvertToDate(input VARCHAR(50)) RETURNS DATE AS BEGIN RETURN TRY_CONVERT(DATE, input, 120) -- 尝试ODBC格式 OR TRY_CONVERT(DATE, input, 101) -- 尝试美国格式 OR NULL END10.2 函数性能测试使用统计函数分析执行情况-- 比较不同字符串拼接方法的性能 DECLARE results TABLE (Method VARCHAR(50), DurationMS INT) INSERT INTO results EXEC( DECLARE start DATETIME2 GETDATE() -- 测试方法1 DECLARE i INT 0, s VARCHAR(MAX) WHILE i 10000 BEGIN SET s s CAST(i AS VARCHAR) SET i 1 END SELECT 运算符 AS Method, DATEDIFF(MILLISECOND, start, GETDATE()) AS DurationMS ) INSERT INTO results EXEC( DECLARE start DATETIME2 GETDATE() -- 测试方法2 DECLARE i INT 0, s VARCHAR(MAX) WHILE i 10000 BEGIN SET s CONCAT(s, i) SET i 1 END SELECT CONCAT函数 AS Method, DATEDIFF(MILLISECOND, start, GETDATE()) AS DurationMS ) SELECT * FROM results