tsql

T-SQL 核心知识

完整参考：有关多语句 TVF、自定义错误消息、INSTEAD OF 触发器、事务隔离级别、游标、动态 SQL 和递归 CTE，请参阅 advanced.md。

深入了解：使用 mcp__documentation__fetch_docs，并将技术设为：sqlserver，以获取全面的文档。

基本结构

-- Anonymous block
BEGIN
    DECLARE @count INT = 0;
    SET @count = @count + 1;
    PRINT 'Count: ' + CAST(@count AS VARCHAR);
END;
GO

变量

DECLARE @name VARCHAR(100) = 'John';
DECLARE @age INT;
DECLARE @salary DECIMAL(10,2), @bonus DECIMAL(10,2);

SET @age = 25;
SELECT @salary = salary FROM employees WHERE id = 1;

-- Multiple assignments
SELECT @salary = salary, @bonus = bonus
FROM employees WHERE id = 1;

-- Table variable
DECLARE @employees TABLE (
    id INT,
    name VARCHAR(100),
    salary DECIMAL(10,2)
);

INSERT INTO @employees SELECT id, name, salary FROM employees;

存储过程

基本过程

CREATE OR ALTER PROCEDURE usp_GetEmployee
    @EmployeeId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE employee_id = @EmployeeId;
END;
GO

-- Execute
EXEC usp_GetEmployee @EmployeeId = 100;
-- or
EXEC usp_GetEmployee 100;

带 OUTPUT 参数的过程

CREATE OR ALTER PROCEDURE usp_GetEmployeeStats
    @DeptId INT,
    @EmployeeCount INT OUTPUT,
    @TotalSalary DECIMAL(15,2) OUTPUT,
    @AvgSalary DECIMAL(15,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        @EmployeeCount = COUNT(*),
        @TotalSalary = SUM(salary),
        @AvgSalary = AVG(salary)
    FROM employees
    WHERE department_id = @DeptId;
END;
GO

-- Call with OUTPUT
DECLARE @Count INT, @Total DECIMAL(15,2), @Avg DECIMAL(15,2);
EXEC usp_GetEmployeeStats
    @DeptId = 10,
    @EmployeeCount = @Count OUTPUT,
    @TotalSalary = @Total OUTPUT,
    @AvgSalary = @Avg OUTPUT;

PRINT 'Count: ' + CAST(@Count AS VARCHAR);

带返回值的过程

CREATE OR ALTER PROCEDURE usp_ValidateEmployee
    @EmployeeId INT
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM employees WHERE employee_id = @EmployeeId)
        RETURN -1;  -- Not found

    IF EXISTS (SELECT 1 FROM employees WHERE employee_id = @EmployeeId AND status = 'INACTIVE')
        RETURN -2;  -- Inactive

    RETURN 0;  -- Success
END;
GO

-- Check return value
DECLARE @result INT;
EXEC @result = usp_ValidateEmployee @EmployeeId = 100;

IF @result = 0
    PRINT 'Valid';
ELSE IF @result = -1
    PRINT 'Employee not found';

函数

标量函数

CREATE OR ALTER FUNCTION dbo.fn_CalculateBonus(
    @Salary DECIMAL(10,2),
    @YearsOfService INT
)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @Bonus DECIMAL(10,2);

    SET @Bonus = CASE
        WHEN @YearsOfService >= 10 THEN @Salary * 0.15
        WHEN @YearsOfService >= 5 THEN @Salary * 0.10
        ELSE @Salary * 0.05
    END;

    RETURN @Bonus;
END;
GO

-- Usage
SELECT employee_id, salary,
    dbo.fn_CalculateBonus(salary, years_of_service) AS bonus
FROM employees;

内联表值函数

CREATE OR ALTER FUNCTION dbo.fn_GetDeptEmployees(
    @DeptId INT
)
RETURNS TABLE
AS
RETURN (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = @DeptId
);
GO

-- Usage
SELECT * FROM dbo.fn_GetDeptEmployees(10);

控制流

IF...ELSE

DECLARE @status VARCHAR(20);

IF @status = 'ACTIVE'
BEGIN
    PRINT 'User is active';
    -- Multiple statements in BEGIN...END
END
ELSE IF @status = 'PENDING'
    PRINT 'User is pending';  -- Single statement, no BEGIN needed
ELSE
BEGIN
    PRINT 'User is inactive';
END;

CASE 表达式

SELECT
    employee_id,
    salary,
    CASE
        WHEN salary >= 100000 THEN 'Executive'
        WHEN salary >= 50000 THEN 'Senior'
        WHEN salary >= 30000 THEN 'Mid'
        ELSE 'Junior'
    END AS level
FROM employees;

-- Simple CASE
SELECT
    employee_id,
    CASE status
        WHEN 'A' THEN 'Active'
        WHEN 'I' THEN 'Inactive'
        ELSE 'Unknown'
    END AS status_name
FROM employees;

WHILE 循环

DECLARE @counter INT = 1;

WHILE @counter <= 10
BEGIN
    PRINT 'Counter: ' + CAST(@counter AS VARCHAR);
    SET @counter = @counter + 1;

    IF @counter = 5
        CONTINUE;  -- Skip to next iteration

    IF @counter = 8
        BREAK;  -- Exit loop
END;

错误处理

TRY...CATCH

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Error information
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    -- Re-throw or log
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

错误函数

函数	说明
ERROR_NUMBER()	错误编号
ERROR_MESSAGE()	错误消息
ERROR_SEVERITY()	错误严重级别（0-25）
ERROR_STATE()	错误状态
ERROR_LINE()	错误发生的行号
ERROR_PROCEDURE()	存储过程名称

THROW 与 RAISERROR

-- THROW (SQL Server 2012+, preferred)
THROW 50001, 'Custom error message', 1;

-- THROW without parameters re-throws current error
BEGIN CATCH
    INSERT INTO error_log (message, error_time)
    VALUES (ERROR_MESSAGE(), GETDATE());

    THROW;  -- Re-throw original error
END CATCH;

-- RAISERROR (legacy)
RAISERROR('Error: %s', 16, 1, @ErrorMessage);

触发器

DML 触发器

CREATE OR ALTER TRIGGER tr_employees_audit
ON employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- Handle INSERT
    IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    BEGIN
        INSERT INTO employees_audit (action, employee_id, new_salary, changed_by, changed_at)
        SELECT 'INSERT', employee_id, salary, SYSTEM_USER, GETDATE()
        FROM inserted;
    END

    -- Handle UPDATE
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        INSERT INTO employees_audit (action, employee_id, old_salary, new_salary, changed_by, changed_at)
        SELECT 'UPDATE', i.employee_id, d.salary, i.salary, SYSTEM_USER, GETDATE()
        FROM inserted i
        INNER JOIN deleted d ON i.employee_id = d.employee_id;
    END

    -- Handle DELETE
    IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        INSERT INTO employees_audit (action, employee_id, old_salary, changed_by, changed_at)
        SELECT 'DELETE', employee_id, salary, SYSTEM_USER, GETDATE()
        FROM deleted;
    END
END;
GO

事务

BEGIN TRANSACTION;
-- or
BEGIN TRAN;

SAVE TRANSACTION SavePoint1;

-- Rollback to savepoint
ROLLBACK TRANSACTION SavePoint1;

COMMIT TRANSACTION;
-- or
COMMIT;

-- Check transaction count
SELECT @@TRANCOUNT;

-- Named transaction
BEGIN TRANSACTION MyTransaction;
COMMIT TRANSACTION MyTransaction;

公用表表达式（CTE）

;WITH dept_stats AS (
    SELECT
        department_id,
        COUNT(*) AS emp_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name, ds.emp_count, ds.avg_salary
FROM departments d
INNER JOIN dept_stats ds ON d.department_id = ds.department_id;

最佳实践

应当

• 在存储过程中使用 SET NOCOUNT ON
• 使用 TRY...CATCH 处理错误
• 使用 sp_executesql 执行动态 SQL
• 尽可能使用 FAST_FORWARD 游标
• 在批量操作中使用表值参数
• 始终使用架构限定对象名称

不应

• 在生产代码中使用 SELECT *
• 通过字符串拼接构建动态 SQL
• 在基于集合的操作可行时使用游标
• 忽略错误处理
• 使用已弃用的功能（GROUP BY ALL 等）

不适用此技能的场景

• SQL Server 基础 SQL - 使用 sqlserver 技能了解数据类型、索引和时态表
• PL/pgSQL（PostgreSQL） - 使用 plpgsql 技能编写 PostgreSQL 存储过程
• PL/SQL（Oracle） - 使用 plsql 技能编写 Oracle 存储过程
• 基础 SQL - 使用 sql-fundamentals 了解 ANSI SQL 基础知识

反模式

反模式	问题	解决方案
不使用 TRY...CATCH	错误被静默忽略	添加错误处理代码块
使用 RAISERROR	已弃用的模式	使用 THROW（SQL 2012+）
未使用 SET NOCOUNT ON	性能开销	在所有存储过程中添加
使用字符串拼接构建动态 SQL	SQL 注入	使用带参数的 sp_executesql
对 TOP 使用不带 ORDER BY 的 SELECT	结果不确定	始终指定 ORDER BY
在基于集合的操作可行时使用游标	性能较差	改写为集合操作

快速故障排除

问题	诊断	修复
事务未提交	SELECT @@TRANCOUNT	添加 COMMIT 或 ROLLBACK
存储过程运行缓慢	在 SSMS 中查看执行计划	添加索引，改写查询
错误被吞掉	检查 CATCH 代码块	确保执行 THROW 或记录日志
成为死锁受害者	扩展事件	保持访问顺序一致
参数嗅探	比较执行计划	使用 OPTION (RECOMPILE) 或局部变量

参考文档

• 存储过程
• 函数
• 错误处理