Microsoft SQL Server 作为微软公司推出的关系型数据库管理系统，凭借其与 Windows 生态系统的深度集成、强大的商业智能功能、易于使用的管理工具以及卓越的性能表现，在企业级应用中占据重要地位。本文将系统全面地梳理 SQL Server 的核心知识点，从基础概念到高级特性，帮助初学者建立完整的知识体系，也为有经验的 DBA 和开发者提供深入的技术参考。

一、SQL Server 基础

1.1 SQL Server 概述
SQL Server 是微软开发的关系型数据库管理系统，支持事务处理、商业智能、数据分析等多种应用场景。
主要版本：
企业版：最高性能，支持所有功能
标准版：基本功能，适合中小企业
开发者版：免费，用于开发和测试
Express版：免费入门级，数据库大小限制10GB

核心特性：
集成 Windows 身份验证
SQL Server Management Studio (SSMS) 管理工具
Transact-SQL (T-SQL) 编程语言
商业智能套件（SSIS、SSAS、SSRS）
高可用性解决方案（Always On）
列存储索引和内存优化表
1.2 安装与配置
Windows 安装：

# 1. 下载 SQL Server 安装程序
# 2. 运行 SQLServerSetup.exe
# 3. 选择安装类型（基本、自定义、下载媒体）
# 4. 选择功能：数据库引擎、SSMS、SSIS等
# 5. 配置实例：默认实例或命名实例
# 6. 配置服务账户和身份验证模式
# 7. 指定管理员账户

SQL Server 配置管理器：

-- 查看服务器配置
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure;

-- 修改配置（如最大内存）
EXEC sp_configure 'max server memory (MB)', 4096;
RECONFIGURE;

-- 查看服务器属性
SELECT 
    SERVERPROPERTY('ServerName') AS ServerName,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('EngineEdition') AS EngineEdition;

1.3 SQL Server 体系结构
数据库架构：

SQL Server 实例
    ├── master 数据库（系统配置）
    ├── model 数据库（模板）
    ├── msdb 数据库（作业、备份历史）
    ├── tempdb 数据库（临时对象）
    ├── resource 数据库（只读系统对象）
    └── 用户数据库

物理结构：

-- 查看数据库文件
SELECT 
    database_id,
    name AS LogicalName,
    physical_name AS PhysicalFile,
    type_desc AS FileType,
    size / 128 AS SizeMB,
    max_size / 128 AS MaxSizeMB,
    growth AS Growth
FROM sys.master_files
WHERE database_id = DB_ID('YourDatabase');

-- 创建数据库时指定文件
CREATE DATABASE SalesDB
ON PRIMARY
(
    NAME = SalesDB_Data,
    FILENAME = 'D:\Data\SalesDB_Data.mdf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
)
LOG ON
(
    NAME = SalesDB_Log,
    FILENAME = 'E:\Logs\SalesDB_Log.ldf',
    SIZE = 50MB,
    MAXSIZE = 2GB,
    FILEGROWTH = 5MB
);

内存结构：

Buffer Pool：缓存数据页

Plan Cache：缓存执行计划

Procedure Cache：缓存存储过程

Log Pool：缓存事务日志

进程架构：

SQLOS：管理资源调度

Scheduler：工作线程调度器

Worker Threads：处理用户请求

Checkpoint：将脏页写入磁盘

Lazy Writer：管理空闲缓冲区

Log Writer：写入事务日志

二、T-SQL 基础

2.1 数据类型

-- 表创建示例
CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    BirthDate DATE,
    HireDate DATE NOT NULL DEFAULT GETDATE(),
    Salary DECIMAL(10,2) CHECK (Salary > 0),
    Email VARCHAR(100) UNIQUE,
    Photo VARBINARY(MAX),
    Resume NVARCHAR(MAX),
    IsActive BIT DEFAULT 1,
    RowVersion ROWVERSION,  -- 自动更新时间戳
    ManagerID INT FOREIGN KEY REFERENCES Employees(EmployeeID)
);

2.2 数据库对象
表（TABLE）：

-- 创建表
CREATE TABLE Departments (
    DeptID INT IDENTITY(1,1) PRIMARY KEY,
    DeptName VARCHAR(100) NOT NULL,
    Location VARCHAR(100),
    CreatedDate DATETIME2 DEFAULT SYSDATETIME()
);

-- 临时表
-- 本地临时表（当前会话可见）
CREATE TABLE #TempEmployees (
    EmployeeID INT,
    FullName NVARCHAR(100)
);

-- 全局临时表（所有会话可见）
CREATE TABLE ##GlobalTemp (
    ID INT,
    Data VARCHAR(100)
);

-- 表变量（适合小数据量）
DECLARE @TableVar TABLE (
    ID INT,
    Name VARCHAR(50)
);

-- 修改表
ALTER TABLE Employees ADD MiddleName NVARCHAR(50);
ALTER TABLE Employees ALTER COLUMN Salary DECIMAL(12,2);
ALTER TABLE Employees DROP COLUMN MiddleName;

视图（VIEW）：

-- 创建标准视图
CREATE VIEW vw_EmployeeDetails AS
SELECT 
    e.EmployeeID,
    e.FirstName + ' ' + e.LastName AS FullName,
    d.DeptName,
    e.Salary
FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.DeptID
WHERE e.IsActive = 1;

-- 索引视图（需要SCHEMABINDING）
CREATE VIEW vw_SalesSummary
WITH SCHEMABINDING
AS
SELECT 
    ProductID,
    COUNT_BIG(*) AS SaleCount,
    SUM(Quantity) AS TotalQuantity,
    SUM(Amount) AS TotalAmount
FROM dbo.Sales
GROUP BY ProductID;

-- 创建唯一聚集索引使视图物化
CREATE UNIQUE CLUSTERED INDEX IX_SalesSummary ON vw_SalesSummary (ProductID);

-- 分区视图（跨多个表）
CREATE VIEW vw_AllSales AS
SELECT * FROM Sales_2022
UNION ALL
SELECT * FROM Sales_2023
UNION ALL
SELECT * FROM Sales_2024;

索引（INDEX）：

-- 聚集索引（物理排序）
CREATE CLUSTERED INDEX IX_Employees_EmployeeID 
ON Employees(EmployeeID);

-- 非聚集索引
CREATE INDEX IX_Employees_LastName 
ON Employees(LastName) 
INCLUDE (FirstName, Email);  -- 包含列

-- 唯一索引
CREATE UNIQUE INDEX IX_Employees_Email 
ON Employees(Email) 
WHERE Email IS NOT NULL;  -- 过滤索引

-- 覆盖索引
CREATE INDEX IX_Sales_ProductID_Date 
ON Sales(ProductID, SaleDate) 
INCLUDE (Amount, Quantity);

-- 列存储索引（数据仓库）
CREATE COLUMNSTORE INDEX IX_Sales_ColumnStore 
ON Sales(ProductID, SaleDate, Amount, Quantity);

-- 全文索引
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON Articles(Content)
KEY INDEX PK_Articles
WITH STOPLIST = SYSTEM;

序列（SEQUENCE，SQL Server 2012+）：

-- 创建序列
CREATE SEQUENCE Seq_EmployeeID
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999999
    CYCLE
    CACHE 20;

-- 使用序列
INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES (NEXT VALUE FOR Seq_EmployeeID, '张三', '李四');

-- 查看当前值
SELECT CURRENT_VALUE FROM sys.sequences WHERE name = 'Seq_EmployeeID';

同义词（SYNONYM）：

-- 创建同义词
CREATE SYNONYM Emp FOR SalesDB.dbo.Employees;

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

-- 删除同义词
DROP SYNONYM Emp;

2.3 T-SQL 查询
基础查询：

-- SELECT 查询
SELECT 
    TOP 10 *  -- 限制返回行数
FROM Employees
WHERE DeptID IN (SELECT DeptID FROM Departments WHERE Location = 'Beijing')
    AND Salary BETWEEN 5000 AND 10000
    AND LastName LIKE '张%'  -- 通配符：%、_、[]、[^]
    AND HireDate >= '2023-01-01'
ORDER BY Salary DESC, HireDate ASC;

-- 分页查询（SQL Server 2012+）
SELECT *
FROM Employees
ORDER BY EmployeeID
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- 分页查询（传统方式）
SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum
    FROM Employees
) AS t
WHERE RowNum BETWEEN 21 AND 30;

-- 聚合查询
SELECT 
    DeptID,
    COUNT(*) AS EmployeeCount,
    AVG(Salary) AS AvgSalary,
    SUM(Salary) AS TotalSalary,
    MAX(Salary) AS MaxSalary,
    MIN(Salary) AS MinSalary,
    COUNT(DISTINCT Title) AS DistinctTitles
FROM Employees
WHERE IsActive = 1
GROUP BY DeptID
HAVING COUNT(*) > 5;

-- 分组集
SELECT 
    DeptID,
    Title,
    COUNT(*) AS Count
FROM Employees
GROUP BY GROUPING SETS (
    (DeptID, Title),
    (DeptID),
    (Title),
    ()
);

-- CUBE 和 ROLLUP
SELECT 
    DeptID,
    Title,
    COUNT(*) AS Count
FROM Employees
GROUP BY CUBE(DeptID, Title);

连接查询：

-- INNER JOIN
SELECT e.EmployeeID, e.FirstName, d.DeptName
FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.DeptID;

-- LEFT JOIN
SELECT e.EmployeeID, e.FirstName, d.DeptName
FROM Employees e
LEFT JOIN Departments d ON e.DeptID = d.DeptID;

-- RIGHT JOIN
SELECT e.EmployeeID, e.FirstName, d.DeptName
FROM Employees e
RIGHT JOIN Departments d ON e.DeptID = d.DeptID;

-- FULL OUTER JOIN
SELECT e.EmployeeID, e.FirstName, d.DeptName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DeptID = d.DeptID;

-- CROSS JOIN（笛卡尔积）
SELECT e.FirstName, d.DeptName
FROM Employees e
CROSS JOIN Departments d;

-- 自连接
SELECT 
    e.FirstName AS Employee,
    m.FirstName AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

-- APPLY（表值函数）
SELECT e.FirstName, t.OrderCount
FROM Employees e
OUTER APPLY (
    SELECT COUNT(*) AS OrderCount
    FROM Orders o
    WHERE o.EmployeeID = e.EmployeeID
) t;

子查询：

-- 标量子查询
SELECT 
    FirstName,
    Salary,
    (SELECT AVG(Salary) FROM Employees) AS AvgSalary,
    Salary - (SELECT AVG(Salary) FROM Employees) AS DiffFromAvg
FROM Employees;

-- IN 子查询
SELECT FirstName, LastName
FROM Employees
WHERE DeptID IN (
    SELECT DeptID 
    FROM Departments 
    WHERE Location = 'Beijing'
);

-- EXISTS 子查询
SELECT FirstName, LastName
FROM Employees e
WHERE EXISTS (
    SELECT 1 
    FROM Orders o 
    WHERE o.EmployeeID = e.EmployeeID 
        AND o.OrderDate > '2023-01-01'
);

-- 派生表（FROM子句中的子查询）
SELECT DeptID, AVG(Salary) AS AvgSalary
FROM (
    SELECT DeptID, Salary
    FROM Employees
    WHERE IsActive = 1
) AS ActiveEmployees
GROUP BY DeptID;

-- CTE（公用表表达式）
WITH DeptStats AS (
    SELECT 
        DeptID,
        AVG(Salary) AS AvgSalary,
        COUNT(*) AS EmpCount
    FROM Employees
    GROUP BY DeptID
)
SELECT d.DeptName, ds.AvgSalary, ds.EmpCount
FROM Departments d
INNER JOIN DeptStats ds ON d.DeptID = ds.DeptID;

-- 递归CTE
WITH OrgChart AS (
    -- 锚点成员
    SELECT EmployeeID, FirstName, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- 递归成员
    SELECT e.EmployeeID, e.FirstName, e.ManagerID, o.Level + 1
    FROM Employees e
    INNER JOIN OrgChart o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM OrgChart;

三、T-SQL 高级编程

3.1 变量与控制流

-- 变量声明和使用
DECLARE @EmployeeID INT = 1001;
DECLARE @FullName NVARCHAR(100);
DECLARE @Salary DECIMAL(10,2);
DECLARE @Date DATETIME = GETDATE();

SET @FullName = (SELECT FirstName + ' ' + LastName 
                 FROM Employees 
                 WHERE EmployeeID = @EmployeeID);

SELECT @Salary = Salary 
FROM Employees 
WHERE EmployeeID = @EmployeeID;

PRINT @FullName;
PRINT @Salary;

-- IF-ELSE
IF @Salary > 10000
BEGIN
    PRINT '高收入员工';
    UPDATE Employees SET Bonus = @Salary * 0.2 
    WHERE EmployeeID = @EmployeeID;
END
ELSE IF @Salary > 5000
BEGIN
    PRINT '中等收入员工';
    UPDATE Employees SET Bonus = @Salary * 0.1 
    WHERE EmployeeID = @EmployeeID;
END
ELSE
BEGIN
    PRINT '普通员工';
    UPDATE Employees SET Bonus = @Salary * 0.05 
    WHERE EmployeeID = @EmployeeID;
END

-- CASE 表达式
SELECT 
    EmployeeID,
    FirstName,
    Salary,
    CASE 
        WHEN Salary > 10000 THEN '高级'
        WHEN Salary > 5000 THEN '中级'
        ELSE '初级'
    END AS Level
FROM Employees;

-- WHILE 循环
DECLARE @Counter INT = 1;
DECLARE @MaxID INT;

SELECT @MaxID = MAX(EmployeeID) FROM Employees;

WHILE @Counter <= @MaxID
BEGIN
    IF EXISTS (SELECT 1 FROM Employees WHERE EmployeeID = @Counter)
    BEGIN
        UPDATE Employees 
        SET Processed = 1 
        WHERE EmployeeID = @Counter;

        IF @@ROWCOUNT = 0
            BREAK;  -- 跳出循环
    END

    SET @Counter = @Counter + 1;

    IF @Counter > 1000
        CONTINUE;  -- 跳过后续代码
END

-- TRY-CATCH
BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO Employees (FirstName, LastName) 
    VALUES ('测试', '用户');

    -- 可能出错的代码
    DELETE FROM Departments WHERE DeptID = 999;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;

    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH

3.2 存储过程

-- 创建存储过程
CREATE OR ALTER PROCEDURE sp_GetEmployeeDetails
    @EmployeeID INT,
    @IncludeOrders BIT = 0,
    @EmployeeName NVARCHAR(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- 获取员工信息
    SELECT 
        @EmployeeName = FirstName + ' ' + LastName,
        Salary,
        HireDate
    FROM Employees
    WHERE EmployeeID = @EmployeeID;

    -- 返回结果集
    SELECT 
        EmployeeID,
        FirstName,
        LastName,
        Salary,
        DeptID
    FROM Employees
    WHERE EmployeeID = @EmployeeID;

    -- 条件返回额外信息
    IF @IncludeOrders = 1
    BEGIN
        SELECT 
            OrderID,
            OrderDate,
            Amount
        FROM Orders
        WHERE EmployeeID = @EmployeeID;
    END

    RETURN 0;  -- 返回状态码
END;

-- 执行存储过程
DECLARE @Name NVARCHAR(100);
DECLARE @Result INT;

EXEC @Result = sp_GetEmployeeDetails 
    @EmployeeID = 1001,
    @IncludeOrders = 1,
    @EmployeeName = @Name OUTPUT;

SELECT @Result AS ReturnCode, @Name AS EmployeeName;

-- 动态SQL
CREATE PROCEDURE sp_DynamicSearch
    @TableName NVARCHAR(128),
    @ColumnName NVARCHAR(128),
    @SearchValue NVARCHAR(255)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX);

    SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName) + 
               N' WHERE ' + QUOTENAME(@ColumnName) + 
               N' LIKE @Value';

    EXEC sp_executesql @SQL, 
        N'@Value NVARCHAR(255)',
        @Value = '%' + @SearchValue + '%';
END;

-- 查看存储过程定义
EXEC sp_helptext 'sp_GetEmployeeDetails';

-- 重新编译存储过程
EXEC sp_recompile 'sp_GetEmployeeDetails';

3.3 函数

-- 标量函数
CREATE FUNCTION fn_GetFullName
(
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50)
)
RETURNS NVARCHAR(101)
AS
BEGIN
    RETURN TRIM(@FirstName) + ' ' + TRIM(@LastName);
END;

-- 使用函数
SELECT 
    EmployeeID,
    dbo.fn_GetFullName(FirstName, LastName) AS FullName
FROM Employees;

-- 内联表值函数（性能更好）
CREATE FUNCTION fn_GetEmployeesByDept
(
    @DeptID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        EmployeeID,
        FirstName,
        LastName,
        Salary
    FROM Employees
    WHERE DeptID = @DeptID AND IsActive = 1
);

-- 使用表值函数
SELECT * FROM fn_GetEmployeesByDept(10);

-- 多语句表值函数
CREATE FUNCTION fn_GetEmployeeStats()
RETURNS @Stats TABLE
(
    DeptID INT,
    DeptName NVARCHAR(100),
    EmployeeCount INT,
    AvgSalary DECIMAL(10,2)
)
AS
BEGIN
    INSERT INTO @Stats
    SELECT 
        d.DeptID,
        d.DeptName,
        COUNT(e.EmployeeID),
        AVG(e.Salary)
    FROM Departments d
    LEFT JOIN Employees e ON d.DeptID = e.DeptID
    GROUP BY d.DeptID, d.DeptName;

    RETURN;
END;

-- 系统函数示例
SELECT 
    GETDATE() AS CurrentDateTime,
    SYSDATETIME() AS HighPrecisionTime,
    DB_NAME() AS CurrentDB,
    @@VERSION AS Version,
    @@ROWCOUNT AS LastRowsAffected,
    @@IDENTITY AS LastIdentity,
    NEWID() AS GUID,
    NEWSEQUENTIALID() AS SequentialGUID,
    ISNULL(NULL, 'Default') AS NullHandling,
    COALESCE(NULL, NULL, 'FirstNonNull') AS CoalesceResult,
    CAST('2024-01-01' AS DATETIME) AS Casting,
    CONVERT(VARCHAR, GETDATE(), 120) AS Converting;

3.4 触发器

-- DML 触发器
CREATE TRIGGER trg_Employees_Audit
ON Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- 审计表
    INSERT INTO AuditLog (TableName, Action, OldData, NewData, ActionDate, UserName)
    SELECT 
        'Employees',
        CASE 
            WHEN EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) THEN 'UPDATE'
            WHEN EXISTS (SELECT * FROM inserted) THEN 'INSERT'
            WHEN EXISTS (SELECT * FROM deleted) THEN 'DELETE'
        END,
        (SELECT * FROM deleted FOR JSON AUTO) AS OldData,
        (SELECT * FROM inserted FOR JSON AUTO) AS NewData,
        GETDATE(),
        SUSER_NAME();
END;

-- INSTEAD OF 触发器（用于视图）
CREATE TRIGGER trg_EmployeesView_Insert
ON vw_EmployeeDetails
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Employees (FirstName, LastName, DeptID)
    SELECT FirstName, LastName, DeptID
    FROM inserted;
END;

-- DDL 触发器
CREATE TRIGGER trg_DDL_Audit
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO DDL_AuditLog (EventType, ObjectName, SQLText, LoginName, EventDate)
    VALUES (
        EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
        EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        SUSER_NAME(),
        GETDATE()
    );
END;

-- 服务器级触发器
CREATE TRIGGER trg_Server_Logon
ON ALL SERVER
FOR LOGON
AS
BEGIN
    IF (ORIGINAL_LOGIN() = 'test_user' AND DATEPART(HOUR, GETDATE()) NOT BETWEEN 8 AND 18)
    BEGIN
        ROLLBACK;
        RAISERROR('只能在8:00-18:00之间登录', 16, 1);
    END
END;

-- 禁用/启用触发器
DISABLE TRIGGER trg_Employees_Audit ON Employees;
ENABLE TRIGGER trg_Employees_Audit ON Employees;

来源：
https://app-aes4wxahovsx.appmiaoda.com/

SQL Server数据库学习知识点大全（一）

一、SQL Server 基础

二、T-SQL 基础

三、T-SQL 高级编程

热门文章

最新文章

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

SQL Server数据库学习知识点大全（一）

一、SQL Server 基础

二、T-SQL 基础

三、T-SQL 高级编程

热门文章

最新文章

相关电子书