前言

入职半年了，学到不少东西，尤其是SQL Server的应用功力增长了不少，开始了解到SQL脚本的使用场景与编写技巧。

数据库版本为SQL Server 2008。

内容

在公司的开发中，用到了三种脚本，存储过程、函数、触发器，以下是我对SQL Server脚本应用的记录，以方便后期查找使用。

同义词

同义词（Synonym）是数据库对象的一个别名，它可以简化对数据库对象的引用。通过使用同义词，我们可以隐藏对象的实际名称和位置，从而提高代码的可读性和可维护性。

1
2
3
4
5
-- 创建同义词
CREATE SYNONYM syn_employee FOR dbo.Employee;

-- 使用同义词
SELECT * FROM syn_employee;

存储过程

存储过程（Stored Procedure）是一组预编译的SQL语句，它可以接受参数、执行复杂的逻辑操作，并返回结果。存储过程可以提高代码的重用性和执行效率。

1
2
3
4
5
6
7
8
9
10
-- 创建存储过程
CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employee WHERE EmployeeID = @EmployeeID;
END;

-- 执行存储过程
EXEC GetEmployeeByID @EmployeeID = 1;

创建可重复执行的存储过程脚本，其他类型的脚本类似

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 检查存储过程是否存在，如果存在则删除
IF OBJECT_ID('dbo.GetEmployeeByID', 'P') IS NOT NULL
    DROP PROCEDURE dbo.GetEmployeeByID;
GO

-- 创建存储过程
CREATE PROCEDURE dbo.GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    -- 设置 NOCOUNT ON 以禁止返回受影响的行数的消息
    SET NOCOUNT ON;

    -- 查询员工信息
    SELECT * 
    FROM Employee 
    WHERE EmployeeID = @EmployeeID;
END;
GO

函数

函数（Function）是一段可以返回值的SQL代码。SQL Server中有两种类型的函数：标量函数和表值函数。

标量函数

标量函数返回单个值。

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建标量函数
CREATE FUNCTION GetEmployeeName(@EmployeeID INT)
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @EmployeeName NVARCHAR(100);
    SELECT @EmployeeName = Name FROM Employee WHERE EmployeeID = @EmployeeID;
    RETURN @EmployeeName;
END;

-- 使用标量函数
SELECT dbo.GetEmployeeName(1) AS EmployeeName;

表值函数

表值函数返回一个表。

1
2
3
4
5
6
7
8
9
10
11
-- 创建表值函数
CREATE FUNCTION GetEmployeesByDepartment(@DepartmentID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM Employee WHERE DepartmentID = @DepartmentID
);

-- 使用表值函数
SELECT * FROM dbo.GetEmployeesByDepartment(1);

触发器

触发器（Trigger）是一种特殊的存储过程，它在特定的事件（如INSERT、UPDATE、DELETE）发生时自动执行。触发器通常用于强制业务规则、维护数据完整性等。

1
2
3
4
5
6
7
8
9
10
11
-- 创建触发器
CREATE TRIGGER trg_AfterInsertEmployee
ON Employee
AFTER INSERT
AS
BEGIN
    PRINT 'A new employee has been added.';
END;

-- 插入数据触发触发器
INSERT INTO Employee (Name, DepartmentID) VALUES ('John Doe', 1);

语法

动态SQL

动态SQL允许我们在运行时构建和执行SQL语句。这在需要根据不同的条件生成不同的SQL语句时非常有用。

1
2
3
4
5
DECLARE @sql NVARCHAR(MAX);
DECLARE @EmployeeID INT = 1;

SET @sql = 'SELECT * FROM Employee WHERE EmployeeID = ' + CAST(@EmployeeID AS NVARCHAR);
EXEC sp_executesql @sql;

级联

级联（Cascade）用于在删除或更新主表记录时，自动删除或更新从表的相关记录。

1
2
3
4
5
6
-- 创建带级联删除的外键
ALTER TABLE Orders
ADD CONSTRAINT FK_EmployeeOrder
FOREIGN KEY (EmployeeID)
REFERENCES Employee(EmployeeID)
ON DELETE CASCADE;

优化

查询

  1. 子查询时，能用exists的不要使用in，如查询一个表中的某个字段是否在另一个表中存在

    1
    2
    3
    4
    5
    6
    -- 低效率，会进行全表搜索
    select * from t_item
    where main_id in (select main_id from t_main where xxxxx)
    -- 高效，查到一条则返回结果
    select * from t_item
    where exists (select 1 from t_main m where t_item.main_id = m.main_id and xxxxx)

  2. 使用索引来加速查询。确保在经常用于搜索和排序的列上创建索引。

    1
    CREATE INDEX idx_employee_name ON Employee(Name);

  3. 避免在WHERE子句中使用函数，这会导致索引失效。

    1
    2
    3
    4
    -- 不推荐
    SELECT * FROM Employee WHERE YEAR(JoinDate) = 2023;
    -- 推荐
    SELECT * FROM Employee WHERE JoinDate >= '2023-01-01' AND JoinDate < '2024-01-01';

踩坑

遇到的问题

  1. declare @sql nvarchar(max)超长，最长只能有4000字符。

    在SQL Server 2008中，nvarchar(max)理论上可以存储2GB的数据，但在某些情况下，特别是在动态SQL中，可能会遇到4000字符的限制。解决方法是分段构建SQL语句**（未经验证）**。

    1
    2
    3
    4
    5
    6
    7
    DECLARE @sql1 NVARCHAR(4000);
    DECLARE @sql2 NVARCHAR(4000);

    SET @sql1 = 'SELECT * FROM Employee WHERE Name LIKE ''%John%''';
    SET @sql2 = ' AND DepartmentID = 1';

    EXEC (@sql1 + @sql2);

  2. 触发器中的事务处理不当可能导致死锁。在触发器中应尽量避免长时间的操作，并确保事务的完整性。