MS-SQL Server开发里那些实用又容易忽略的小技巧分享和总结

说到MS-SQL Server开发，大家肯定都知道怎么写基本的增删改查，也会用一些存储过程，但有些小技巧，用好了真的能事半功倍，而且特别容易被忽略，这些都是我从日常工作和一些技术博客（比如博客园、CSDN上的经验分享）里慢慢积累的。

用 “SELECT … INTO” 快速建表备份

我们经常需要备份一张表的数据，或者根据查询结果创建一张新表，很多人会先CREATE TABLE把结构建好，然后再INSERT INTO … SELECT …，其实有更简单的一步到位的方法，就是SELECT … INTO。

你想把订单表里今天的订单快速备份到一张新表Orders_Backup_20240530里,直接写：

SELECT * INTO Orders_Backup_20240530 FROM Orders WHERE OrderDate = CAST(GETDATE() AS DATE)

数据库会自动根据SELECT出来的结果集创建一张新表Orders_Backup_20240530，并且把数据也插进去，这在做数据抽样、临时分析时特别方便，不过要注意，新表的结构（如索引、约束）是不会从原表带过来的,它就是一个纯粹的堆表。

字符串拼接的“现代”方法：STRING_AGG

以前要把一列的多行数据合并成一个用逗号分隔的字符串，非常麻烦，要么用FOR XML PATH(‘’)这种写法，看起来很复杂，不容易理解，从SQL Server 2017开始，有了一个超级好用的函数STRING_AGG。

你想查询每个部门的所有员工姓名,用分号隔开显示在一列里：

SELECT DepartmentID, STRING_AGG(EmployeeName, '; ') AS AllEmployees
FROM Employees
GROUP BY DepartmentID

这样出来的结果就是一行为一个部门，后面一列就是这个部门所有员工的名字字符串，清晰又简洁,这个函数大大简化了行列转换的操作。

使用 “MERGE” 语句实现“有则更新，无则插入”

这是一个非常经典的需求：根据一个条件判断数据是否存在，存在就更新，不存在就插入，以前我们要写一堆IF EXISTS … UPDATE … ELSE INSERT …,代码冗长还容易出竞态条件。

MERGE语句（也叫UPSERT）可以原子性地完成这个操作,根据员工ID同步员工信息表：

MERGE INTO Employees AS target
USING (VALUES (123, '张三', '技术部')) AS source (EmployeeID, Name, Department)
ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET Name = source.Name, Department = source.Department
WHEN NOT MATCHED THEN
    INSERT (EmployeeID, Name, Department) VALUES (source.EmployeeID, source.Name, source.Department);

一句搞定，而且保证了操作的原子性,避免了在判断和写入之间数据被其他人修改的问题。

临时表名前加“#”与“##”的区别

我们都知道用临时表,但有时候会搞不清一个和两个的区别。

• 局部临时表（一个，如#TempTable）：只在当前数据库连接（会话）中可见，你创建了只有你自己这个连接能看见和使用，连接一关闭，这个表就自动删除了,非常适合存储过程内部存放中间结果。
• 全局临时表（两个，如##GlobalTemp）：对所有数据库连接都可见，当你创建后，其他任何连接都能访问它，它会在创建它的连接断开、并且所有其他连接停止引用它的时候才被自动删除，用起来要非常小心，容易造成冲突,一般很少用。

查看SQL真实执行计划的简单方法

优化查询性能时，看执行计划是关键，除了在SSMS里点击“显示估计的执行计划”（那个是估计的，不真正运行），你还可以轻松看到SQL语句真正执行后的计划。

在查询窗口里，勾选“包括实际执行计划”（快捷键是Ctrl + M），然后再运行你的SQL，在结果栏旁边会多一个“执行计划”的标签页，里面就是这次运行的真实执行计划，这对于分析那些复杂查询的性能瓶颈至关重要，因为你能看到实际返回的行数、实际的消耗时间等,比估计的计划更准确。

使用 “APPLY” 运算符简化复杂查询

APPLY运算符（特别是CROSS APPLY和OUTER APPLY）非常强大，但容易被忽略,它可以让你在查询中为左表的每一行调用一次表值函数或子查询。

举个简单例子，你有一个订单表（Orders）和一个根据订单ID获取订单详情的表值函数（fn_GetOrderDetails），你想列出所有订单及其详情，用APPLY可以这样写：

SELECT o.OrderID, d.ProductName, d.Quantity
FROM Orders o
CROSS APPLY fn_GetOrderDetails(o.OrderID) d

这就相当于为Orders表的每一行，都去执行一次函数fn_GetOrderDetails，并把结果关联起来，用传统的JOIN很难写出这么灵活的查询，它在处理JSON数据、XML数据或者需要逐行计算的场景下特别好用。

条件判断函数：IIF 和 CHOOSE

虽然CASE WHEN是标准的条件判断语句，但SQL Server也提供了更简洁的函数。

• IIF：就像Excel里的IF函数。IIF(条件, 真的时候返回值, 假的时候返回值)。SELECT IIF(Salary > 5000, ‘高’, ‘低’) AS Level FROM Employees，写起来比CASE WHEN Salary > 5000 THEN ‘高’ ELSE ‘低’ END快多了。
• CHOOSE：根据索引返回值。CHOOSE(索引, 值1, 值2, …)。SELECT CHOOSE(Weekday, ‘Sun’, ‘Mon’, ‘Tue’)，如果Weekday是2，就返回’Mon’,适合这种简单的映射关系。

这些小函数能让代码更简短易读，虽然底层还是转换成CASE WHEN,但写起来顺手。

这些技巧虽然小，但都是实战中提炼出来的，能有效提高编写SQL的效率和代码质量,希望对你有所帮助。