印刻数据库

外观

SQLServer 数据库操作命令 ​

数据库管理命令 ​

查看数据库 ​

sql
-- 查看所有数据库及状态
SELECT 
    name AS DatabaseName,
    database_id,
    create_date,
    state_desc AS Status,
    recovery_model_desc AS RecoveryModel,
    compatibility_level
FROM sys.databases;

-- 查看当前数据库
SELECT DB_NAME() AS CurrentDatabase;

-- 查看数据库详细信息（包括文件组和文件）
EXEC sp_helpdb 'DatabaseName';

生产场景：在进行数据库迁移或升级前，使用上述命令查看所有数据库的兼容性级别和恢复模式，确保目标环境支持。例如，某金融机构在升级SQL Server 2012到2016前，通过查询sys.databases确认所有数据库的兼容性级别均为110，满足升级要求。

版本差异：

  • SQL Server 2016+：新增 sys.dm_db_log_stats DMV 可查看日志文件统计信息
  • SQL Server 2019+：兼容性级别150，支持更现代的性能特性，如智能查询处理

创建数据库 ​

sql
-- 创建基本数据库（适用于测试环境）
CREATE DATABASE DatabaseName;

-- 创建生产级数据库（带多个文件组和文件）
CREATE DATABASE DatabaseName
ON PRIMARY
(
    NAME = DatabaseName_Primary,
    FILENAME = 'D:\MSSQL\Data\DatabaseName_Primary.mdf',
    SIZE = 1024MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 256MB
),
FILEGROUP FG_UserData
(
    NAME = DatabaseName_UserData1,
    FILENAME = 'D:\MSSQL\Data\DatabaseName_UserData1.ndf',
    SIZE = 512MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 128MB
),
FILEGROUP FG_Index
(
    NAME = DatabaseName_Index1,
    FILENAME = 'E:\MSSQL\Index\DatabaseName_Index1.ndf',
    SIZE = 256MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 64MB
)
LOG ON
(
    NAME = DatabaseName_Log,
    FILENAME = 'F:\MSSQL\Log\DatabaseName_Log.ldf',
    SIZE = 256MB,
    MAXSIZE = 2048MB,
    FILEGROWTH = 64MB
);

生产场景：为大型数据库创建分离的数据和索引文件组，将数据文件放在不同物理磁盘上，提高I/O性能。例如，某电商平台的订单数据库采用了分离的数据、索引和日志文件组，分布在不同的SSD磁盘上，IOPS提升了300%。

版本差异：

  • SQL Server 2012+：支持包含数据库（Contained Databases），降低数据库依赖
  • SQL Server 2016+：支持弹性数据库（Stretch Database），将冷数据自动迁移到Azure
  • SQL Server 2019+：支持UTF-8字符集，减少存储空间需求，提高跨平台兼容性

修改数据库 ​

sql
-- 修改数据库名称
ALTER DATABASE OldDatabaseName MODIFY NAME = NewDatabaseName;

-- 增加数据文件到现有文件组
ALTER DATABASE DatabaseName
ADD FILE
(
    NAME = DatabaseName_UserData2,
    FILENAME = 'D:\MSSQL\Data\DatabaseName_UserData2.ndf',
    SIZE = 512MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 128MB
) TO FILEGROUP FG_UserData;

-- 修改文件大小和增长方式
ALTER DATABASE DatabaseName
MODIFY FILE
(
    NAME = DatabaseName_Primary,
    SIZE = 2048MB,
    FILEGROWTH = 512MB
);

生产场景：当数据库文件增长过快导致性能下降时，调整文件增长方式，避免频繁的自动增长事件。例如，某报表数据库将文件增长方式从默认的10%改为固定的512MB，减少了80%的自动增长事件，提高了报表生成速度。

版本差异：

  • SQL Server 2016+：支持在线添加数据文件到内存优化文件组

删除数据库 ​

sql
-- 删除数据库（如果存在）
IF DB_ID('DatabaseName') IS NOT NULL
BEGIN
    -- 设置为单用户模式并立即回滚所有连接
    ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DatabaseName;
END;

生产场景：在自动化脚本中使用条件删除，避免因数据库不存在导致脚本失败。例如，某DevOps团队在CI/CD管道中使用上述脚本确保测试数据库被正确清理。

数据库状态管理 ​

sql
-- 设置数据库为单用户模式（用于维护）
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK AFTER 30 SECONDS;

-- 设置数据库为多用户模式
ALTER DATABASE DatabaseName SET MULTI_USER;

-- 设置数据库为只读模式
ALTER DATABASE DatabaseName SET READ_ONLY WITH ROLLBACK IMMEDIATE;

-- 设置数据库为读写模式
ALTER DATABASE DatabaseName SET READ_WRITE;

-- 分离数据库（适用于移动数据库文件）
EXEC sp_detach_db 'DatabaseName';

-- 附加数据库（推荐使用 CREATE DATABASE FOR ATTACH）
CREATE DATABASE DatabaseName
ON PRIMARY (FILENAME = 'D:\MSSQL\Data\DatabaseName.mdf')
LOG ON (FILENAME = 'D:\MSSQL\Log\DatabaseName.ldf')
FOR ATTACH;

生产场景：在进行数据库硬件迁移时，使用分离/附加操作将数据库文件移动到新磁盘，减少停机时间。例如，某医院在迁移数据库到新存储时，通过分离/附加操作将停机时间从4小时缩短到30分钟。

版本差异：

  • SQL Server 2016+：引入 ALTER DATABASE SCOPED CONFIGURATION 进行更细粒度的数据库配置

表操作命令 ​

查看表 ​

sql
-- 查看当前数据库所有表
SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    p.rows AS RowCount
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1); -- 只计算堆表或聚集索引的行数

-- 查看表结构和约束
EXEC sp_help 'SchemaName.TableName';

-- 查看表的列信息（详细）
SELECT 
    c.name AS ColumnName,
    t.name AS DataType,
    c.max_length AS MaxLength,
    c.precision AS Precision,
    c.scale AS Scale,
    c.is_nullable AS IsNullable,
    c.is_identity AS IsIdentity,
    c.is_computed AS IsComputed,
    d.definition AS ComputedDefinition
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT JOIN sys.computed_columns d ON c.object_id = d.object_id AND c.column_id = d.column_id
WHERE c.object_id = OBJECT_ID('SchemaName.TableName');

生产场景：在进行数据迁移或ETL开发时，使用上述命令了解表结构、数据类型和约束，确保数据兼容性。例如，某数据仓库团队在构建ETL流程前，使用这些命令详细了解源系统的表结构，确保数据映射正确。

创建表 ​

sql
-- 创建基本表
CREATE TABLE dbo.Customers
(
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    CustomerName NVARCHAR(100) NOT NULL,
    Email NVARCHAR(255) UNIQUE,
    Phone NVARCHAR(20),
    CreatedDate DATETIME2(3) DEFAULT SYSUTCDATETIME(),
    LastModifiedDate DATETIME2(3) DEFAULT SYSUTCDATETIME(),
    IsActive BIT DEFAULT 1
);

-- 创建带有外键约束的表
CREATE TABLE dbo.Orders
(
    OrderID INT PRIMARY KEY IDENTITY(1,1),
    CustomerID INT NOT NULL,
    OrderDate DATETIME2(3) DEFAULT SYSUTCDATETIME(),
    TotalAmount DECIMAL(18,4) NOT NULL CHECK (TotalAmount >= 0),
    OrderStatus TINYINT DEFAULT 1,
    CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) 
        REFERENCES dbo.Customers(CustomerID) ON DELETE CASCADE
);

-- 创建临时表（会话级）
CREATE TABLE #TempOrders
(
    OrderID INT,
    OrderDate DATETIME
);

-- 创建全局临时表
CREATE TABLE ##GlobalTempTable
(
    ID INT,
    Name NVARCHAR(50)
);

生产场景：创建表时使用 DATETIME2(3) 替代 DATETIME 以获得更高的精度和更广泛的日期范围，使用 SYSUTCDATETIME() 确保时区一致性。例如，某跨国公司的订单系统使用 DATETIME2(3) 和 SYSUTCDATETIME() 确保全球订单时间的一致性。

版本差异：

  • SQL Server 2012+：支持 SEQUENCE 对象替代标识列，提供更灵活的编号方式
  • SQL Server 2016+：支持 JSON 数据类型和 TEMPORAL TABLES（系统版本化临时表）
  • SQL Server 2019+：支持 UTF-8 字符集，减少存储需求，提高跨平台兼容性

修改表 ​

sql
-- 添加列
ALTER TABLE dbo.Customers ADD Gender CHAR(1) NULL;

-- 修改列数据类型（注意：可能需要先删除依赖对象）
ALTER TABLE dbo.Customers ALTER COLUMN Phone NVARCHAR(30) NULL;

-- 修改列名
EXEC sp_rename 'dbo.Customers.Gender', 'CustomerGender', 'COLUMN';

-- 添加默认约束
ALTER TABLE dbo.Customers 
ADD CONSTRAINT DF_Customers_CreatedBy DEFAULT 'System' FOR CreatedBy;

-- 删除列
ALTER TABLE dbo.Customers DROP COLUMN CreatedBy;

-- 删除约束
ALTER TABLE dbo.Customers DROP CONSTRAINT DF_Customers_CreatedBy;

生产场景：在生产环境中修改表结构时，建议在低峰期进行，并提前测试，尤其是修改列数据类型时，可能会导致长时间锁表。例如，某电商平台在凌晨2点进行表结构修改，使用 ALTER TABLE 语句添加新列，并提前进行了充分的测试，确保修改过程不会影响业务。

删除表 ​

sql
-- 删除表（如果存在）
IF OBJECT_ID('dbo.TableName', 'U') IS NOT NULL
    DROP TABLE dbo.TableName;

-- 删除多个表
IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL DROP TABLE dbo.Table1;
IF OBJECT_ID('dbo.Table2', 'U') IS NOT NULL DROP TABLE dbo.Table2;

生产场景：在部署脚本中使用条件删除，确保脚本的幂等性，可重复执行。例如，某DevOps团队在部署数据库变更时，使用上述脚本确保旧表被正确删除，新表被正确创建。

索引操作命令 ​

查看索引 ​

sql
-- 查看表的所有索引
EXEC sp_helpindex 'dbo.TableName';

-- 查看索引详细信息（包括包含列和键列）
SELECT 
    i.name AS IndexName,
    i.type_desc AS IndexType,
    COL_NAME(ic.object_id, ic.column_id) AS ColumnName,
    ic.key_ordinal AS KeyOrdinal,
    ic.is_included_column AS IsIncludedColumn
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.object_id = OBJECT_ID('dbo.TableName')
ORDER BY i.index_id, ic.key_ordinal;

-- 查看索引碎片
SELECT 
    OBJECT_NAME(ps.object_id) AS TableName,
    i.name AS IndexName,
    ps.index_type_desc AS IndexType,
    ps.avg_fragmentation_in_percent AS FragmentationPercent,
    ps.page_count AS PageCount
FROM sys.dm_db_index_physical_stats(
    DB_ID(), OBJECT_ID('dbo.TableName'), NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > 10;

生产场景：定期检查索引碎片，当碎片率超过10%时考虑重建或重组索引，优化查询性能。例如，某金融机构每周运行一次索引碎片检查，当碎片率超过10%时重组索引，超过30%时重建索引，确保查询性能稳定。

创建索引 ​

sql
-- 创建非聚集索引
CREATE NONCLUSTERED INDEX IX_Customers_Email ON dbo.Customers(Email);

-- 创建唯一索引
CREATE UNIQUE NONCLUSTERED INDEX IX_Customers_Phone ON dbo.Customers(Phone) WHERE Phone IS NOT NULL;

-- 创建复合索引
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_OrderDate 
ON dbo.Orders(CustomerID, OrderDate DESC);

-- 创建包含包含列的索引（覆盖查询）
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID 
ON dbo.Orders(CustomerID) INCLUDE (OrderDate, TotalAmount, OrderStatus);

-- 创建聚集索引（表必须没有聚集索引）
CREATE CLUSTERED INDEX CI_Orders_OrderID ON dbo.Orders(OrderID);

-- SQL Server 2014+：创建内存优化表的索引
CREATE TABLE dbo.MemoryOptimizedTable
(
    ID INT PRIMARY KEY NONCLUSTERED,
    Name NVARCHAR(50),
    INDEX IX_MemoryOptimizedTable_Name NONCLUSTERED (Name)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

生产场景：为频繁用于过滤、排序和连接的列创建索引，使用包含列减少书签查找，提高查询性能。例如，某电商平台为订单表创建了覆盖索引，包含了订单查询中常用的所有列，使查询性能提升了5倍。

版本差异：

  • SQL Server 2012+：支持列存储索引，用于数据仓库和分析场景
  • SQL Server 2014+：支持非聚集列存储索引
  • SQL Server 2016+：支持更新的非聚集列存储索引
  • SQL Server 2019+：支持批处理模式自适应连接，自动选择最优连接算法

修改索引 ​

sql
-- 重命名索引
EXEC sp_rename 'dbo.TableName.OldIndexName', 'NewIndexName', 'INDEX';

-- 禁用索引
ALTER INDEX IX_TableName_Column ON dbo.TableName DISABLE;

-- 启用索引（重建）
ALTER INDEX IX_TableName_Column ON dbo.TableName REBUILD;

-- 更新索引统计信息
UPDATE STATISTICS dbo.TableName IX_TableName_Column WITH FULLSCAN;

-- 更新表的所有统计信息
UPDATE STATISTICS dbo.TableName WITH FULLSCAN;

生产场景：在数据大量变更后，更新索引统计信息，确保查询优化器使用准确的统计信息生成高效的执行计划。例如，某零售企业在每日数据加载完成后，运行统计信息更新脚本，确保第二天的报表查询性能良好。

删除索引 ​

sql
-- 删除索引
DROP INDEX IX_TableName_Column ON dbo.TableName;

-- 删除表的所有非聚集索引
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + '.' + QUOTENAME(OBJECT_NAME(i.object_id)) + ';' 
FROM sys.indexes i 
WHERE i.object_id = OBJECT_ID('dbo.TableName') AND i.type_desc = 'NONCLUSTERED';
EXEC sp_executesql @sql;

生产场景：当索引不再被查询使用或维护成本过高时，删除无用索引，减少写操作的开销。例如，某IT服务公司通过分析索引使用情况，删除了30%的无用索引，降低了数据库维护成本，提高了写入性能。

索引维护 ​

sql
-- 重组索引（适用于低碎片率 5%-30%）
ALTER INDEX ALL ON dbo.TableName REORGANIZE WITH (LOB_COMPACTION = ON);

-- 重建索引（适用于高碎片率 >30%）
ALTER INDEX ALL ON dbo.TableName REBUILD WITH (
    ONLINE = ON, -- 在线重建，不阻塞查询（Enterprise 版本）
    SORT_IN_TEMPDB = ON, -- 在 TempDB 中排序，减少对用户数据库的影响
    MAXDOP = 4, -- 限制并行度
    DATA_COMPRESSION = PAGE -- 启用页压缩
);

-- 查看索引使用情况
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks + s.user_scans + s.user_lookups AS TotalUsage,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates AS Updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID() AND OBJECT_NAME(s.object_id) = 'TableName';

生产场景：定期运行索引维护作业，根据碎片率选择重组或重建索引，优化查询性能。例如，某电信公司使用Ola Hallengren的索引维护脚本，每周日凌晨自动运行，确保所有索引的碎片率保持在合理范围内。

数据操作命令 ​

插入数据 ​

sql
-- 插入单行数据
INSERT INTO dbo.Customers (CustomerName, Email, Phone)
VALUES ('张三', 'zhangsan@example.com', '13800138000');

-- 插入多行数据（SQL Server 2008+）
INSERT INTO dbo.Customers (CustomerName, Email, Phone)
VALUES 
    ('李四', 'lisi@example.com', '13900139000'),
    ('王五', 'wangwu@example.com', '13700137000'),
    ('赵六', 'zhaoliu@example.com', '13600136000');

-- 从另一个表插入数据
INSERT INTO dbo.OrdersArchive (OrderID, CustomerID, OrderDate, TotalAmount)
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM dbo.Orders
WHERE OrderDate < DATEADD(YEAR, -1, GETDATE());

-- 使用 OUTPUT 子句返回插入的行
INSERT INTO dbo.Customers (CustomerName, Email)
OUTPUT INSERTED.CustomerID, INSERTED.CustomerName, INSERTED.Email
VALUES ('孙七', 'sunqi@example.com');

生产场景：在ETL过程中使用批量插入，减少日志生成和提高插入速度。例如，某数据仓库团队使用SSIS进行数据加载，将数据批量插入到目标表，提高了加载速度。

版本差异：

  • SQL Server 2008+：支持表值构造函数，一次插入多行
  • SQL Server 2012+：支持 SEQUENCE 对象生成唯一标识符

查询数据 ​

sql
-- 查询所有列（生产环境中应避免使用 *）
SELECT * FROM dbo.Customers;

-- 查询指定列
SELECT CustomerID, CustomerName, Email FROM dbo.Customers;

-- 带条件查询
SELECT * FROM dbo.Customers WHERE IsActive = 1;

-- 带排序查询
SELECT * FROM dbo.Orders ORDER BY OrderDate DESC;

-- 带分组和聚合查询
SELECT 
    CustomerID,
    COUNT(*) AS OrderCount,
    SUM(TotalAmount) AS TotalSpent
FROM dbo.Orders
WHERE OrderDate >= DATEADD(MONTH, -1, GETDATE())
GROUP BY CustomerID
HAVING COUNT(*) >= 5;

-- 带连接查询
SELECT 
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    o.TotalAmount
FROM dbo.Customers c
JOIN dbo.Orders o ON c.CustomerID = o.CustomerID;

-- 子查询
SELECT * FROM dbo.Customers 
WHERE CustomerID IN (SELECT CustomerID FROM dbo.Orders WHERE TotalAmount > 1000);

-- 分页查询（SQL Server 2012+）
SELECT * FROM dbo.Orders 
ORDER BY OrderID 
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- 分页查询（兼容旧版本）
SELECT TOP 10 * FROM dbo.Orders 
WHERE OrderID NOT IN (SELECT TOP 10 OrderID FROM dbo.Orders ORDER BY OrderID) 
ORDER BY OrderID;

生产场景：在生产环境中，避免使用 SELECT *，只查询需要的列，减少网络传输和I/O开销。例如，某社交媒体平台的用户查询API只返回必要的用户信息，减少了网络带宽消耗，提高了API响应速度。

更新数据 ​

sql
-- 更新单行数据
UPDATE dbo.Customers 
SET Email = 'newemail@example.com', LastModifiedDate = SYSUTCDATETIME()
WHERE CustomerID = 1;

-- 更新多行数据
UPDATE dbo.Orders 
SET OrderStatus = 2 
WHERE OrderDate < DATEADD(DAY, -30, GETDATE()) AND OrderStatus = 1;

-- 使用 CASE 语句更新
UPDATE dbo.Customers 
SET CustomerType = CASE 
    WHEN TotalSpent > 10000 THEN 'VIP'
    WHEN TotalSpent > 5000 THEN 'Gold'
    ELSE 'Silver'
END;

-- 从另一个表更新数据
UPDATE c
SET c.TotalSpent = o.TotalSpent
FROM dbo.Customers c
JOIN (
    SELECT CustomerID, SUM(TotalAmount) AS TotalSpent
    FROM dbo.Orders
    GROUP BY CustomerID
) o ON c.CustomerID = o.CustomerID;

-- 使用 OUTPUT 子句返回更新前后的值
UPDATE dbo.Customers 
SET Email = 'updated@example.com'
OUTPUT INSERTED.CustomerID, DELETED.Email AS OldEmail, INSERTED.Email AS NewEmail
WHERE CustomerID = 1;

生产场景：在更新大量数据时，使用批量更新（如每次更新1000行），避免长时间锁表。例如，某电商平台在更新用户积分时，使用批量更新的方式，每次更新1000行，避免了对用户表的长时间锁定。

删除数据 ​

sql
-- 删除单行数据
DELETE FROM dbo.Customers WHERE CustomerID = 1;

-- 删除多行数据
DELETE FROM dbo.Orders WHERE OrderDate < DATEADD(YEAR, -2, GETDATE());

-- 使用 TOP 删除部分数据
DELETE TOP (1000) FROM dbo.LargeTable WHERE Status = 0;

-- 截断表（更快，不记录日志，不可回滚）
TRUNCATE TABLE dbo.StagingTable;

-- 使用 OUTPUT 子句返回删除的数据
DELETE FROM dbo.Customers 
OUTPUT DELETED.*
WHERE IsActive = 0;

生产场景：对于大型表，使用 DELETE TOP (n) 分批删除数据，避免日志空间不足和长时间锁表。例如，某金融机构在清理过期交易数据时，使用分批删除的方式，每次删除10000行，避免了对系统性能的影响。

视图操作命令 ​

查看视图 ​

sql
-- 查看所有视图
SELECT 
    s.name AS SchemaName,
    v.name AS ViewName
FROM sys.views v
JOIN sys.schemas s ON v.schema_id = s.schema_id;

-- 查看视图定义
EXEC sp_helptext 'dbo.ViewName';

-- 查看视图列信息
EXEC sp_columns 'dbo.ViewName';

生产场景：在审查数据库对象时，查看所有视图及其定义，了解数据访问方式。例如，某审计团队在审查数据库权限时，查看所有视图的定义，确保敏感数据未被不当访问。

创建视图 ​

sql
-- 创建基本视图
CREATE VIEW dbo.ActiveCustomers
AS
SELECT CustomerID, CustomerName, Email, Phone
FROM dbo.Customers
WHERE IsActive = 1;

-- 创建带架构绑定的视图（可创建索引）
CREATE VIEW dbo.OrderSummary WITH SCHEMABINDING
AS
SELECT 
    CustomerID,
    COUNT_BIG(*) AS OrderCount,
    SUM(TotalAmount) AS TotalSpent
FROM dbo.Orders
GROUP BY CustomerID;

-- 创建索引视图（SQL Server 2008+）
CREATE UNIQUE CLUSTERED INDEX IX_OrderSummary_CustomerID 
ON dbo.OrderSummary(CustomerID);

-- 创建加密视图
CREATE VIEW dbo.EncryptedView WITH ENCRYPTION
AS
SELECT CustomerID, CustomerName FROM dbo.Customers;

生产场景：使用索引视图加速复杂的聚合查询，减少计算开销。例如，某电商平台使用索引视图存储每日销售汇总数据，将报表查询时间从分钟级缩短到秒级。

版本差异：

  • SQL Server 2016+：支持内存优化表上的视图

修改视图 ​

sql
-- 修改视图
ALTER VIEW dbo.ActiveCustomers
AS
SELECT CustomerID, CustomerName, Email, Phone, CreatedDate
FROM dbo.Customers
WHERE IsActive = 1;

-- 重命名视图
EXEC sp_rename 'dbo.OldViewName', 'NewViewName';

生产场景：在修改视图时，使用 ALTER VIEW 替代 DROP VIEW 后 CREATE VIEW，保留视图的权限设置。例如，某医疗系统在修改患者信息视图时，使用 ALTER VIEW 语句，确保视图的权限设置不变，符合HIPAA合规要求。

删除视图 ​

sql
-- 删除视图（如果存在）
IF OBJECT_ID('dbo.ViewName', 'V') IS NOT NULL
    DROP VIEW dbo.ViewName;

存储过程操作命令 ​

查看存储过程 ​

sql
-- 查看所有存储过程
SELECT 
    s.name AS SchemaName,
    p.name AS ProcedureName
FROM sys.procedures p
JOIN sys.schemas s ON p.schema_id = s.schema_id;

-- 查看存储过程定义
EXEC sp_helptext 'dbo.ProcedureName';

-- 查看存储过程参数
EXEC sp_help 'dbo.ProcedureName';

-- 查看存储过程依赖关系
EXEC sp_depends 'dbo.ProcedureName';

生产场景：在修改表结构前，查看哪些存储过程依赖该表，评估影响范围。例如，某银行在修改账户表结构前，使用 sp_depends 查看所有依赖该表的存储过程，确保所有存储过程都被正确更新。

创建存储过程 ​

sql
-- 创建基本存储过程
CREATE PROCEDURE dbo.GetCustomers
    @IsActive BIT = 1,
    @PageSize INT = 10,
    @PageNumber INT = 1
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT CustomerID, CustomerName, Email, Phone
    FROM dbo.Customers
    WHERE IsActive = @IsActive
    ORDER BY CustomerID
    OFFSET (@PageNumber - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;
END;

-- 创建带输出参数的存储过程
CREATE PROCEDURE dbo.GetCustomerOrderCount
    @CustomerID INT,
    @OrderCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT @OrderCount = COUNT(*) 
    FROM dbo.Orders 
    WHERE CustomerID = @CustomerID;
END;

-- 创建带结果集的存储过程
CREATE PROCEDURE dbo.GetCustomerDetails
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 返回客户基本信息
    SELECT CustomerID, CustomerName, Email, Phone
    FROM dbo.Customers
    WHERE CustomerID = @CustomerID;
    
    -- 返回客户订单
    SELECT OrderID, OrderDate, TotalAmount, OrderStatus
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
    ORDER BY OrderDate DESC;
END;

生产场景：使用存储过程封装复杂的业务逻辑，提高代码重用性和安全性，减少网络传输。例如，某电商平台的订单处理逻辑全部封装在存储过程中，提高了代码的可维护性和安全性。

版本差异：

  • SQL Server 2016+：支持 natively compiled stored procedures（针对内存优化表）
  • SQL Server 2017+：支持图形处理存储过程

执行存储过程 ​

sql
-- 执行无参数存储过程
EXEC dbo.GetCustomers;

-- 执行带参数的存储过程
EXEC dbo.GetCustomers @IsActive = 1, @PageSize = 20, @PageNumber = 1;

-- 执行带输出参数的存储过程
DECLARE @Count INT;
EXEC dbo.GetCustomerOrderCount @CustomerID = 1, @OrderCount = @Count OUTPUT;
SELECT @Count AS OrderCount;

-- 使用 EXECUTE AS 执行存储过程
EXECUTE AS USER = 'dbo';
EXEC dbo.SensitiveProcedure;
REVERT;

生产场景：在应用程序中使用参数化存储过程，防止SQL注入攻击。例如，某金融应用使用参数化存储过程处理所有数据库访问，有效防止了SQL注入攻击。

修改存储过程 ​

sql
-- 修改存储过程
ALTER PROCEDURE dbo.GetCustomers
    @IsActive BIT = 1,
    @PageSize INT = 10,
    @PageNumber INT = 1,
    @SearchTerm NVARCHAR(100) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT CustomerID, CustomerName, Email, Phone
    FROM dbo.Customers
    WHERE IsActive = @IsActive
        AND (@SearchTerm IS NULL OR CustomerName LIKE '%' + @SearchTerm + '%')
    ORDER BY CustomerID
    OFFSET (@PageNumber - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;
END;

删除存储过程 ​

sql
-- 删除存储过程（如果存在）
IF OBJECT_ID('dbo.ProcedureName', 'P') IS NOT NULL
    DROP PROCEDURE dbo.ProcedureName;

触发器操作命令 ​

查看触发器 ​

sql
-- 查看所有触发器
SELECT 
    name AS TriggerName,
    parent_name AS TableName,
    type_desc AS TriggerType
FROM sys.triggers;

-- 查看触发器定义
EXEC sp_helptext 'dbo.TriggerName';

-- 查看表的触发器
EXEC sp_helptrigger 'dbo.TableName';

生产场景：在排查数据异常时，查看表上的触发器，了解数据修改的额外逻辑。例如，某公司在发现订单数据异常时，查看了订单表上的触发器，发现触发器逻辑存在bug，导致数据被错误修改。

创建触发器 ​

sql
-- 创建 AFTER INSERT 触发器（用于审计）
CREATE TRIGGER dbo.tr_Customers_AfterInsert
ON dbo.Customers
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO dbo.AuditLog (
        TableName,
        Operation,
        RecordID,
        ChangeDetails,
        OperationDate,
        OperationBy
    )
    SELECT 
        'Customers',
        'INSERT',
        inserted.CustomerID,
        'New customer created: ' + inserted.CustomerName,
        SYSUTCDATETIME(),
        ORIGINAL_LOGIN()
    FROM inserted;
END;

-- 创建 INSTEAD OF DELETE 触发器（防止直接删除）
CREATE TRIGGER dbo.tr_Customers_InsteadOfDelete
ON dbo.Customers
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 逻辑：不允许直接删除，而是标记为非活动
    UPDATE dbo.Customers 
    SET IsActive = 0, LastModifiedDate = SYSUTCDATETIME()
    WHERE CustomerID IN (SELECT CustomerID FROM deleted);
    
    -- 记录审计日志
    INSERT INTO dbo.AuditLog (
        TableName,
        Operation,
        RecordID,
        ChangeDetails,
        OperationDate,
        OperationBy
    )
    SELECT 
        'Customers',
        'SOFT DELETE',
        deleted.CustomerID,
        'Customer marked as inactive: ' + deleted.CustomerName,
        SYSUTCDATETIME(),
        ORIGINAL_LOGIN()
    FROM deleted;
END;

生产场景：使用触发器实现数据审计和软删除，保护数据完整性。例如，某医疗系统使用触发器记录所有患者数据的修改，确保符合HIPAA合规要求。

修改触发器 ​

sql
-- 修改触发器
ALTER TRIGGER dbo.tr_Customers_AfterInsert
ON dbo.Customers
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO dbo.AuditLog (
        TableName,
        Operation,
        RecordID,
        ChangeDetails,
        OperationDate,
        OperationBy
    )
    SELECT 
        'Customers',
        CASE WHEN deleted.CustomerID IS NULL THEN 'INSERT' ELSE 'UPDATE' END,
        ISNULL(inserted.CustomerID, deleted.CustomerID),
        CASE WHEN deleted.CustomerID IS NULL 
            THEN 'New customer created: ' + inserted.CustomerName
            ELSE 'Customer updated: ' + inserted.CustomerName END,
        SYSUTCDATETIME(),
        ORIGINAL_LOGIN()
    FROM inserted
    FULL OUTER JOIN deleted ON inserted.CustomerID = deleted.CustomerID;
END;

删除触发器 ​

sql
-- 删除触发器
DROP TRIGGER dbo.tr_Customers_AfterInsert;

-- 删除表的所有触发器
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'DROP TRIGGER ' + QUOTENAME(name) + ';' 
FROM sys.triggers 
WHERE parent_id = OBJECT_ID('dbo.TableName');
EXEC sp_executesql @sql;

生产场景：当触发器导致性能问题时，删除或重构触发器，考虑使用其他方式实现相同功能。例如，某电商平台发现订单表上的触发器导致订单处理延迟，将触发器逻辑重构为异步处理，提高了订单处理速度。

事务管理命令 ​

sql
-- 开始事务
BEGIN TRANSACTION;

-- 或使用 BEGIN TRAN
BEGIN TRAN;

-- 提交事务
COMMIT TRANSACTION;

-- 回滚事务
ROLLBACK TRANSACTION;

-- 使用保存点
BEGIN TRANSACTION;

INSERT INTO dbo.Table1 (Name) VALUES ('Test1');
SAVE TRANSACTION SavePoint1;

INSERT INTO dbo.Table2 (Name) VALUES ('Test2');

-- 发生错误时回滚到保存点
IF @@ERROR <> 0
    ROLLBACK TRANSACTION SavePoint1;

COMMIT TRANSACTION;

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 默认级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 脏读
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; -- 快照隔离

-- SQL Server 2014+：延迟事务持久化
ALTER DATABASE DatabaseName SET DELAYED_DURABILITY = FORCED;

生产场景：在进行多个相关的数据修改操作时，使用事务确保数据一致性，失败时可以回滚所有更改。例如，某银行在处理转账业务时，使用事务确保转出和转入操作要么同时成功，要么同时失败。

版本差异：

  • SQL Server 2014+：支持延迟事务持久化，提高事务处理性能
  • SQL Server 2016+：支持内存优化表的事务处理增强

常用系统存储过程 ​

数据库信息 ​

sql
-- 查看数据库文件信息
EXEC sp_helpfile;

-- 查看数据库文件组
EXEC sp_helpfilegroup;

-- 查看数据库用户
EXEC sp_helplogins;
EXEC sp_helpuser;

表和索引信息 ​

sql
-- 查看表信息
EXEC sp_help 'dbo.TableName';

-- 查看表索引
EXEC sp_helpindex 'dbo.TableName';

-- 查看表约束
EXEC sp_helpconstraint 'dbo.TableName';

性能相关 ​

sql
-- 查看当前活动进程
EXEC sp_who;
EXEC sp_who2; -- 包含更多信息
EXEC sp_whoisactive; -- 第三方存储过程，功能更强大

-- 查看当前执行的 SQL
EXEC sp_executesql N'SELECT * FROM sys.dm_exec_requests WHERE session_id > 50';

-- 查看缓存的查询计划
SELECT * FROM sys.dm_exec_cached_plans;

-- 重新编译存储过程
EXEC sp_recompile 'dbo.ProcedureName';
-- 重新编译表的所有相关存储过程
EXEC sp_recompile 'dbo.TableName';

生产场景：在排查性能问题时，使用 sp_who2 或 sp_whoisactive 查看当前活动进程，识别阻塞和长时间运行的查询。例如，某IT支持团队在收到性能问题投诉时，使用 sp_whoisactive 快速定位到阻塞进程，并将其杀死，恢复系统性能。

常用 DBCC 命令 ​

数据库维护 ​

sql
-- 检查数据库完整性（推荐定期运行）
DBCC CHECKDB ('DatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY;

-- 检查表完整性
DBCC CHECKTABLE ('dbo.TableName') WITH NO_INFOMSGS;

-- 检查索引完整性
DBCC CHECKINDEX ('dbo.TableName') WITH NO_INFOMSGS;

-- 更新统计信息（修复页计数不准确问题）
DBCC UPDATEUSAGE ('DatabaseName', 'dbo.TableName');

生产场景：在数据库备份前或升级后，运行 DBCC CHECKDB 确保数据库完整性。例如，某金融机构在每日全量备份前运行 DBCC CHECKDB，确保备份的数据是完整的。

性能相关 ​

sql
-- 查看事务日志空间使用情况
DBCC SQLPERF(LOGSPACE);

-- 查看缓冲池使用情况
DBCC MEMORYSTATUS;

-- 清除缓冲区缓存（仅用于测试）
DBCC DROPCLEANBUFFERS;

-- 清除计划缓存
DBCC FREEPROCCACHE;

-- 查看死锁信息（SQL Server 2005+）
SELECT * FROM sys.dm_tran_locks;
SELECT * FROM sys.dm_os_waiting_tasks WHERE wait_type = 'LCK_M_IX';

-- 启用死锁跟踪（重启后失效）
DBCC TRACEON(1222, -1);
DBCC TRACEON(1204, -1);

生产场景：在排查死锁问题时，启用死锁跟踪标志，查看详细的死锁信息。例如，某电商平台在高峰期出现死锁问题，启用死锁跟踪后，找到了导致死锁的查询，并对其进行了优化。

版本差异：

  • SQL Server 2014 SP2+：引入 DBCC CLONEDATABASE 用于创建数据库的只读副本，用于分析问题
  • SQL Server 2016+：DBCC CHECKDB 性能提升，支持并行检查

常见问题（FAQ） ​

如何快速复制表结构？ ​

问题：如何快速复制表结构而不复制数据？

解答：

sql
-- 方法 1：使用 SELECT INTO 但不返回数据
SELECT * INTO dbo.NewTable FROM dbo.OldTable WHERE 1 = 0;

-- 方法 2：使用生成脚本功能
-- 在 SSMS 中右键点击表 → 任务 → 生成脚本 → 选择只生成表结构

-- 方法 3：使用 sys.objects 和 sys.columns 生成创建脚本
-- 可编写动态 SQL 生成完整的表创建脚本

生产场景：在创建测试环境时，快速复制生产表结构用于测试。

如何复制表数据？ ​

问题：如何复制表数据到新表或现有表？

解答：

sql
-- 复制数据到新表（自动创建表结构）
SELECT * INTO dbo.NewTable FROM dbo.OldTable;

-- 复制数据到现有表
INSERT INTO dbo.ExistingTable (Column1, Column2)
SELECT Column1, Column2 FROM dbo.OldTable;

-- 复制大量数据（使用 BULK INSERT）
BULK INSERT dbo.LargeTable 
FROM 'D:\Data\LargeTableData.txt' 
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    BATCHSIZE = 10000,
    MAXERRORS = 10
);

生产场景：在数据迁移或ETL过程中，复制表数据到目标表。

如何查看表的大小？ ​

问题：如何查看表的大小（数据大小和索引大小）？

解答：

sql
-- 方法 1：使用 sp_spaceused
EXEC sp_spaceused 'dbo.TableName';

-- 方法 2：使用动态管理视图
SELECT 
    t.name AS TableName,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    SUM(CASE WHEN i.index_id IN (0, 1) THEN a.total_pages ELSE 0 END) * 8 AS DataSpaceKB,
    SUM(CASE WHEN i.index_id > 1 THEN a.total_pages ELSE 0 END) * 8 AS IndexSpaceKB
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.name = 'TableName'
GROUP BY t.name;

-- 方法 3：查看所有表的大小
EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?'';';

生产场景：在进行容量规划时，查看表的大小和增长趋势。

如何查看正在执行的 SQL 语句？ ​

问题：如何查看当前正在执行的 SQL 语句？

解答：

sql
-- 方法 1：使用 sp_who2 找到会话 ID，然后查看 SQL
EXEC sp_who2;
-- 然后使用以下命令查看特定会话的 SQL
DBCC INPUTBUFFER(session_id);

-- 方法 2：使用动态管理视图（推荐）
SELECT 
    r.session_id,
    s.host_name,
    s.program_name,
    s.login_name,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    t.text AS SqlText,
    qp.query_plan AS QueryPlan
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.session_id > 50;

生产场景：在排查性能问题时，查看正在执行的 SQL 语句，识别慢查询。

如何杀死长时间运行的查询？ ​

问题：如何杀死长时间运行的查询或阻塞的会话？

解答：

sql
-- 首先找到会话 ID
EXEC sp_who2 'active';

-- 然后杀死会话
KILL session_id;

-- 例如：
KILL 55;

-- 查找并杀死阻塞会话
WITH BlockingInfo AS (
    SELECT 
        r.session_id AS BlockedSessionId,
        r.blocking_session_id AS BlockingSessionId
    FROM sys.dm_exec_requests r
    WHERE r.blocking_session_id > 0
)
SELECT 
    bi.BlockedSessionId,
    bi.BlockingSessionId,
    s1.host_name AS BlockedHost,
    s1.program_name AS BlockedProgram,
    s2.host_name AS BlockingHost,
    s2.program_name AS BlockingProgram,
    t.text AS BlockingSql
FROM BlockingInfo bi
JOIN sys.dm_exec_sessions s1 ON bi.BlockedSessionId = s1.session_id
JOIN sys.dm_exec_sessions s2 ON bi.BlockingSessionId = s2.session_id
CROSS APPLY sys.dm_exec_sql_text(
    (SELECT sql_handle FROM sys.dm_exec_requests WHERE session_id = bi.BlockingSessionId)
) t;

生产场景：在紧急情况下，杀死阻塞会话或长时间运行的查询，恢复系统性能。

如何生成数据库的创建脚本？ ​

问题：如何生成数据库的完整创建脚本？

解答：

sql
-- 方法 1：使用 SSMS 生成脚本
-- 右键点击数据库 → 任务 → 生成脚本 → 选择要生成的对象和选项

-- 方法 2：使用 PowerShell 和 SqlServer 模块
Import-Module SqlServer
Export-DbaScript -SqlInstance ServerName -Database DatabaseName -FilePath C:\Scripts\DatabaseScript.sql

-- 方法 3：使用 SMO（SQL Server Management Objects）
-- 可编写 C# 或 PowerShell 脚本生成数据库脚本

生产场景：在备份数据库结构或进行数据库迁移时，生成数据库创建脚本。

如何查看数据库的恢复模式？ ​

问题：如何查看数据库的恢复模式？

解答：

sql
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'DatabaseName';

生产场景：在配置备份策略时，查看数据库的恢复模式。

如何修改数据库的恢复模式？ ​

问题：如何修改数据库的恢复模式？

解答：

sql
-- 修改为完整恢复模式（生产环境推荐）
ALTER DATABASE DatabaseName SET RECOVERY FULL;

-- 修改为简单恢复模式（开发/测试环境）
ALTER DATABASE DatabaseName SET RECOVERY SIMPLE;

-- 修改为大容量日志恢复模式（用于大容量操作）
ALTER DATABASE DatabaseName SET RECOVERY BULK_LOGGED;

生产场景：根据业务需求修改数据库的恢复模式，例如在进行大容量数据加载时，临时修改为大容量日志恢复模式。

如何查看数据库的最后备份时间？ ​

问题：如何查看数据库的最后备份时间？

解答：

sql
SELECT 
    d.name AS DatabaseName,
    MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFullBackup,
    MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDifferentialBackup,
    MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLogBackup,
    DATEDIFF(HOUR, MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END), GETDATE()) AS HoursSinceFullBackup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name
GROUP BY d.name
ORDER BY d.name;

生产场景：监控数据库备份状态，确保备份按时执行。

如何收缩数据库或日志文件？ ​

问题：如何收缩数据库或日志文件？

解答：

sql
-- 查看文件信息，获取逻辑文件名
EXEC sp_helpfile;

-- 收缩数据库
DBCC SHRINKDATABASE (DatabaseName, 10); -- 收缩到 10% 可用空间

-- 收缩数据文件
DBCC SHRINKFILE (DataFileName, 500); -- 收缩到 500MB

-- 收缩日志文件（先备份日志）
BATCH LOG DatabaseName TO DISK = 'D:\Backup\DatabaseName_Log.bak';
DBCC SHRINKFILE (LogFileName, 100); -- 收缩到 100MB

-- 注意：频繁收缩数据库会导致碎片增加，影响性能，应避免

生产场景：在必要时收缩数据库或日志文件，例如日志文件过大导致磁盘空间不足。

如何查看表的索引使用情况？ ​

问题：如何查看表的索引使用情况，识别无用索引？

解答：

sql
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    s.user_seeks + s.user_scans + s.user_lookups AS TotalReads,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates AS Writes,
    s.last_user_seek,
    s.last_user_scan,
    s.last_user_lookup,
    s.last_user_update
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID() AND OBJECT_NAME(s.object_id) = 'TableName'
ORDER BY TotalReads DESC;

-- 查找从未使用过的索引
SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON 
    i.object_id = s.object_id AND i.index_id = s.index_id AND s.database_id = DB_ID()
WHERE i.object_id = OBJECT_ID('TableName') 
    AND i.type_desc = 'NONCLUSTERED' 
    AND s.object_id IS NULL;

生产场景：优化数据库性能，删除无用索引，减少维护成本。

如何处理大量数据删除？ ​

问题：如何删除大量数据而不阻塞系统？

解答：

sql
-- 方法 1：分批删除
DECLARE @BatchSize INT = 10000;
DECLARE @RowCount INT = 1;

WHILE @RowCount > 0
BEGIN
    DELETE TOP (@BatchSize) FROM dbo.LargeTable 
    WHERE CreatedDate < DATEADD(YEAR, -1, GETDATE());
    
    SET @RowCount = @@ROWCOUNT;
    WAITFOR DELAY '00:00:01'; -- 等待 1 秒，减轻系统压力
END;

-- 方法 2：使用 TRUNCATE TABLE（如果可以清空表）
TRUNCATE TABLE dbo.StagingTable;

-- 方法 3：使用分区切换
-- 将旧数据切换到临时表，然后删除临时表
ALTER TABLE dbo.PartitionedTable SWITCH PARTITION 1 TO dbo.OldDataTable;
DROP TABLE dbo.OldDataTable;

生产场景：在清理过期数据时，避免长时间阻塞系统。

如何查看和优化慢查询？ ​

问题：如何查看和优化慢查询？

解答：

sql
-- 查看当前执行的慢查询
SELECT 
    r.session_id,
    s.host_name,
    s.program_name,
    t.text AS SqlText,
    r.wait_time,
    r.cpu_time,
    r.total_elapsed_time,
    r.reads,
    r.writes,
    qp.query_plan
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.total_elapsed_time > 5000 -- 执行时间超过 5 秒
ORDER BY r.total_elapsed_time DESC;

-- 查看历史慢查询（需要启用查询存储）
SELECT 
    qt.query_sql_text,
    rs.avg_duration,
    rs.max_duration,
    rs.avg_cpu_time,
    rs.max_cpu_time,
    rs.avg_logical_io_reads,
    rs.max_logical_io_reads
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.query_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.avg_duration > 5000000 -- 平均执行时间超过 5 秒
ORDER BY rs.avg_duration DESC;

优化建议：

  • 为查询添加合适的索引
  • 避免使用 SELECT *
  • 优化 JOIN 条件和 WHERE 子句
  • 考虑使用索引视图或内存优化表
  • 调整查询参数，避免参数嗅探

生产场景：在性能调优过程中，识别和优化慢查询。

如何监控数据库性能？ ​

问题：如何监控数据库性能，识别瓶颈？

解答：

sql
-- 监控 CPU 使用率
SELECT 
    TOP 10
    total_worker_time/execution_count AS AvgCPU,
    (SELECT SUBSTRING(text, statement_start_offset/2 + 1, 
        (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) 
        FROM sys.dm_exec_sql_text(sql_handle)) AS QueryText
FROM sys.dm_exec_query_stats
ORDER BY AvgCPU DESC;

-- 监控内存使用率
SELECT 
    (physical_memory_in_use_kb/1024) AS PhysicalMemoryMB,
    (locked_page_allocations_kb/1024) AS LockedPagesMB,
    (virtual_address_space_committed_kb/1024) AS VirtualMemoryMB,
    (available_physical_memory_kb/1024) AS AvailablePhysicalMemoryMB
FROM sys.dm_os_process_memory;

-- 监控 I/O 等待
SELECT 
    TOP 10
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    (wait_time_ms - signal_wait_time_ms) AS ResourceWaitTime
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%' AND wait_type NOT LIKE '%QUEUE%'
ORDER BY wait_time_ms DESC;

-- 监控 TempDB 使用情况
SELECT 
    session_id,
    database_id,
    user_objects_alloc_page_count,
    user_objects_dealloc_page_count,
    internal_objects_alloc_page_count,
    internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50;

推荐使用工具：

  • SQL Server Management Studio (SSMS) 活动监视器
  • SQL Server Profiler 或扩展事件
  • Performance Monitor (PerfMon)
  • 第三方工具：Redgate SQL Monitor, SolarWinds Database Performance Analyzer

生产场景：在日常运维中，监控数据库性能，识别并解决性能瓶颈。

如何实现数据库自动化运维？ ​

问题：如何实现数据库自动化运维，减少人工操作？

解答：

  1. 使用 SQL Server Agent 作业：

    • 创建定期执行的作业，如备份、索引维护、统计信息更新
    • 配置作业通知，出现故障时发送邮件

  2. 使用 PowerShell 脚本：

    powershell
    # 示例：自动备份数据库
Import-Module SqlServer
$InstanceName = "localhost"
$Databases = Get-DbaDatabase -SqlInstance $InstanceName -ExcludeSystem
$BackupPath = "D:\Backup\"

foreach ($DB in $Databases) {
    $BackupFile = $BackupPath + $DB.Name + "_" + (Get-Date -Format "yyyyMMddHHmmss") + ".bak"
    Backup-DbaDatabase -SqlInstance $InstanceName -Database $DB.Name -Path $BackupFile -CompressBackup
}

  3. 使用 T-SQL 脚本：

    sql
    -- 示例：自动重建碎片索引
DECLARE @TableName NVARCHAR(256);
DECLARE @IndexName NVARCHAR(256);
DECLARE @Fragmentation FLOAT;

DECLARE IndexCursor CURSOR FOR
SELECT 
    OBJECT_NAME(ps.object_id) AS TableName,
    i.name AS IndexName,
    ps.avg_fragmentation_in_percent AS Fragmentation
FROM sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > 30 AND i.type_desc <> 'HEAP';

OPEN IndexCursor;
FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @Fragmentation;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = 'ALTER INDEX ' + QUOTENAME(@IndexName) + 
        ' ON ' + QUOTENAME(@TableName) + ' REBUILD WITH (ONLINE = ON);';
    EXEC sp_executesql @SQL;
    FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @Fragmentation;
END;

CLOSE IndexCursor;
DEALLOCATE IndexCursor;

  4. 使用第三方工具：

    • Redgate SQL Backup Pro
    • Ola Hallengren 维护脚本（免费）
    • dbatools PowerShell 模块（免费）

  5. 使用 Azure DevOps 或 Jenkins：

    • 配置 CI/CD 管道，自动化数据库部署和测试

生产场景：结合使用 SQL Server Agent 作业和 PowerShell 脚本，实现数据库备份、索引维护、性能监控等自动化运维，减少人工干预，提高可靠性。

总结 ​

SQL Server 数据库操作命令是 DBA 日常工作的基础，掌握这些命令对于高效管理数据库至关重要。本文介绍了数据库管理、表操作、索引操作、数据操作、视图操作、存储过程操作、触发器操作和事务管理等常用命令，并提供了丰富的生产场景示例和版本差异说明。

在实际工作中，DBA 应根据具体业务需求和环境特点，选择合适的命令和工具，并结合自动化脚本和工具，实现高效、可靠的数据库运维。同时，应定期学习 SQL Server 的新特性和最佳实践，不断提升自己的技能水平，适应不断变化的技术环境。

通过掌握本文介绍的 SQL Server 数据库操作命令和最佳实践，DBA 可以更好地管理和维护数据库，确保数据库系统的性能、可用性和安全性，为业务提供可靠的数据支持。