sqlserver

SQL Server 核心知识

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

数据类型

数值类型

类型	说明	范围
BIT	布尔值（0/1）	0、1、NULL
TINYINT	1 字节整数	0 到 255
SMALLINT	2 字节整数	-32,768 到 32,767
INT	4 字节整数	-2^31 到 2^31-1
BIGINT	8 字节整数	-2^63 到 2^63-1
DECIMAL(p,s)	固定精度	p：1-38 位数字
NUMERIC(p,s)	与 DECIMAL 相同	p：1-38 位数字
FLOAT	8 字节浮点数	约 15 位精度
REAL	4 字节浮点数	约 7 位精度
MONEY	8 字节货币类型	±922 万亿
SMALLMONEY	4 字节货币类型	±214,748

字符串类型

类型	说明	最大大小
CHAR(n)	固定长度	8,000 字节
VARCHAR(n)	可变长度	8,000 字节
VARCHAR(MAX)	大型可变长度	2 GB
NCHAR(n)	Unicode 固定长度	4,000 个字符
NVARCHAR(n)	Unicode 可变长度	4,000 个字符
NVARCHAR(MAX)	大型 Unicode	1 GB
TEXT	旧版大型类型（已弃用）	2 GB
NTEXT	旧版 Unicode（已弃用）	1 GB

日期/时间类型

类型	说明	范围
DATE	仅日期	0001-01-01 到 9999-12-31
TIME(n)	仅时间（n=0-7 精度）	00:00:00 到 23:59:59
DATETIME	日期 + 时间	1753-01-01 到 9999-12-31
DATETIME2(n)	扩展日期时间	0001-01-01 到 9999-12-31
DATETIMEOFFSET(n)	带时区	同上 + 时区
SMALLDATETIME	精度较低	1900-01-01 到 2079-06-06

其他类型

类型	说明
UNIQUEIDENTIFIER	16 字节 GUID
BINARY(n)	固定长度二进制
VARBINARY(n)	可变长度二进制
VARBINARY(MAX)	大型二进制（2 GB）
XML	XML 数据
JSON	JSON（以 NVARCHAR 存储）
GEOGRAPHY	空间数据
GEOMETRY	几何数据
HIERARCHYID	层次结构位置

CREATE TABLE example (
    id INT IDENTITY(1,1) PRIMARY KEY,
    uuid UNIQUEIDENTIFIER DEFAULT NEWID(),
    name NVARCHAR(100) NOT NULL,
    price DECIMAL(10,2),
    quantity INT DEFAULT 0,
    is_active BIT DEFAULT 1,
    created_at DATETIME2 DEFAULT SYSDATETIME(),
    metadata NVARCHAR(MAX),  -- For JSON
    document VARBINARY(MAX)  -- For files
);

标识列

-- Auto-increment
CREATE TABLE employees (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100)
);

-- Get last identity
SELECT SCOPE_IDENTITY();      -- Current scope
SELECT @@IDENTITY;            -- Any scope (avoid)
SELECT IDENT_CURRENT('employees');  -- Specific table

-- Insert with identity off
SET IDENTITY_INSERT employees ON;
INSERT INTO employees (id, name) VALUES (100, 'Admin');
SET IDENTITY_INSERT employees OFF;

-- Reseed identity
DBCC CHECKIDENT ('employees', RESEED, 0);

序列（2012+）

-- Create sequence
CREATE SEQUENCE dbo.OrderSeq
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NO MAXVALUE
    NO CYCLE
    CACHE 20;

-- Use sequence
INSERT INTO orders (id, customer_id)
VALUES (NEXT VALUE FOR dbo.OrderSeq, 1);

-- In DEFAULT
ALTER TABLE orders
ADD CONSTRAINT df_order_id DEFAULT (NEXT VALUE FOR dbo.OrderSeq) FOR id;

-- Get current without incrementing
SELECT current_value FROM sys.sequences WHERE name = 'OrderSeq';

-- Reset sequence
ALTER SEQUENCE dbo.OrderSeq RESTART WITH 1;

日期/时间函数

-- Current date/time
SELECT GETDATE();           -- DATETIME
SELECT SYSDATETIME();       -- DATETIME2 (more precise)
SELECT GETUTCDATE();        -- UTC datetime
SELECT SYSUTCDATETIME();    -- UTC datetime2
SELECT SYSDATETIMEOFFSET(); -- With timezone

-- Date parts
SELECT YEAR(GETDATE());
SELECT MONTH(GETDATE());
SELECT DAY(GETDATE());
SELECT DATEPART(QUARTER, GETDATE());
SELECT DATENAME(WEEKDAY, GETDATE());  -- 'Monday'

-- Date arithmetic
SELECT DATEADD(DAY, 7, GETDATE());     -- Add 7 days
SELECT DATEADD(MONTH, -1, GETDATE());  -- Subtract 1 month
SELECT DATEDIFF(DAY, '2024-01-01', GETDATE());  -- Days between

-- Format (2012+)
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss');

-- Parse
SELECT CAST('2024-01-15' AS DATE);
SELECT CONVERT(DATETIME, '01/15/2024', 101);  -- US format
SELECT PARSE('15 January 2024' AS DATE);

-- First/last of month
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);  -- First of month
SELECT EOMONTH(GETDATE());  -- Last of month (2012+)

字符串函数

-- Concatenation
SELECT 'Hello' + ' ' + 'World';
SELECT CONCAT('Hello', ' ', 'World');
SELECT CONCAT_WS(' ', 'Hello', 'World', NULL);  -- With separator, ignores NULL

-- Substring
SELECT SUBSTRING('Hello World', 1, 5);  -- 'Hello'
SELECT LEFT('Hello World', 5);          -- 'Hello'
SELECT RIGHT('Hello World', 5);         -- 'World'

-- Length
SELECT LEN('Hello');     -- 5 (chars, trims trailing spaces)
SELECT DATALENGTH('Hello');  -- 5 (bytes)

-- Case
SELECT UPPER('hello');
SELECT LOWER('HELLO');

-- Trim
SELECT TRIM('  hello  ');
SELECT LTRIM('  hello');
SELECT RTRIM('hello  ');
SELECT TRIM('x' FROM 'xxxhelloxxx');  -- 2017+

-- Replace
SELECT REPLACE('hello', 'l', 'L');
SELECT STUFF('Hello World', 7, 5, 'SQL');  -- 'Hello SQL'

-- Position
SELECT CHARINDEX('o', 'Hello World');      -- 5
SELECT CHARINDEX('o', 'Hello World', 6);   -- 8 (start from 6)

-- Padding
SELECT RIGHT('0000000000' + '123', 10);    -- '0000000123'
SELECT FORMAT(123, '0000000000');          -- 2012+

-- Split (2016+)
SELECT value FROM STRING_SPLIT('a,b,c', ',');

-- Aggregate strings (2017+)
SELECT STRING_AGG(name, ', ') FROM employees;
SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) FROM employees;

JSON 支持

-- JSON functions (2016+)
DECLARE @json NVARCHAR(MAX) = '{"name":"John","age":30,"address":{"city":"NYC"}}';

-- Extract values
SELECT JSON_VALUE(@json, '$.name');           -- 'John'
SELECT JSON_VALUE(@json, '$.address.city');   -- 'NYC'
SELECT JSON_QUERY(@json, '$.address');        -- '{"city":"NYC"}'

-- Check if valid JSON
SELECT ISJSON(@json);  -- 1

-- Modify JSON
SELECT JSON_MODIFY(@json, '$.age', 31);
SELECT JSON_MODIFY(@json, '$.email', '[email protected]');

-- Parse JSON array
DECLARE @arr NVARCHAR(MAX) = '[{"id":1,"name":"A"},{"id":2,"name":"B"}]';
SELECT * FROM OPENJSON(@arr) WITH (id INT, name NVARCHAR(50));

-- Generate JSON
SELECT id, name, email
FROM employees
FOR JSON AUTO;  -- Auto-structure

SELECT id, name, email
FROM employees
FOR JSON PATH, ROOT('employees');  -- Custom structure

索引类型

-- Clustered index (one per table, defines physical order)
CREATE CLUSTERED INDEX IX_emp_id ON employees(id);

-- Non-clustered index
CREATE NONCLUSTERED INDEX IX_emp_email ON employees(email);

-- Unique index
CREATE UNIQUE INDEX IX_emp_email ON employees(email);

-- Composite index
CREATE INDEX IX_emp_dept_name ON employees(department_id, last_name);

-- Included columns (covering index)
CREATE INDEX IX_emp_email ON employees(email)
INCLUDE (first_name, last_name, phone);

-- Filtered index
CREATE INDEX IX_active_emp ON employees(email)
WHERE is_active = 1;

-- Columnstore index (analytics)
CREATE COLUMNSTORE INDEX IX_sales_cs ON sales(product_id, amount, sale_date);
-- Or clustered columnstore
CREATE CLUSTERED COLUMNSTORE INDEX IX_sales_ccs ON sales;

-- Full-text index
CREATE FULLTEXT CATALOG ft_catalog;
CREATE FULLTEXT INDEX ON documents(content) KEY INDEX PK_documents;

表分区

-- 1. Create partition function
CREATE PARTITION FUNCTION pf_sales_date (DATE)
AS RANGE RIGHT FOR VALUES ('2022-01-01', '2023-01-01', '2024-01-01');

-- 2. Create partition scheme
CREATE PARTITION SCHEME ps_sales_date
AS PARTITION pf_sales_date
TO (fg_2021, fg_2022, fg_2023, fg_2024);

-- 3. Create partitioned table
CREATE TABLE sales (
    id INT IDENTITY(1,1),
    sale_date DATE,
    amount DECIMAL(10,2),
    CONSTRAINT PK_sales PRIMARY KEY (id, sale_date)
) ON ps_sales_date(sale_date);

-- View partitions
SELECT
    $PARTITION.pf_sales_date(sale_date) AS partition_number,
    COUNT(*) AS row_count
FROM sales
GROUP BY $PARTITION.pf_sales_date(sale_date);

-- Split partition (add new)
ALTER PARTITION SCHEME ps_sales_date NEXT USED fg_2025;
ALTER PARTITION FUNCTION pf_sales_date() SPLIT RANGE ('2025-01-01');

-- Merge partitions
ALTER PARTITION FUNCTION pf_sales_date() MERGE RANGE ('2022-01-01');

-- Switch partition (instant move)
ALTER TABLE sales SWITCH PARTITION 1 TO sales_archive;

时态表（2016+）

-- Create temporal table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name NVARCHAR(100),
    salary DECIMAL(10,2),
    -- System time columns
    valid_from DATETIME2 GENERATED ALWAYS AS ROW START,
    valid_to DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.employees_history));

-- Query historical data
SELECT * FROM employees FOR SYSTEM_TIME AS OF '2024-01-01';
SELECT * FROM employees FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-06-01';
SELECT * FROM employees FOR SYSTEM_TIME ALL;

-- Disable versioning for maintenance
ALTER TABLE employees SET (SYSTEM_VERSIONING = OFF);
-- ... maintenance ...
ALTER TABLE employees SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.employees_history));

公用表表达式

-- Basic CTE
;WITH emp_stats AS (
    SELECT department_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.name, es.emp_count, es.avg_salary
FROM departments d
JOIN emp_stats es ON d.id = es.department_id;

-- Recursive CTE
;WITH hierarchy AS (
    -- Anchor
    SELECT id, name, manager_id, 0 AS level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    -- Recursive
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * FROM hierarchy OPTION (MAXRECURSION 100);

分页

-- OFFSET-FETCH (2012+)
SELECT id, name, email
FROM employees
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

-- With variable
DECLARE @PageNum INT = 3, @PageSize INT = 10;
SELECT id, name, email
FROM employees
ORDER BY id
OFFSET (@PageNum - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

-- ROW_NUMBER (older method)
;WITH numbered AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM employees
)
SELECT * FROM numbered WHERE rn BETWEEN 21 AND 30;

不应使用此技能的情况

• T-SQL 编程 - 使用 tsql 技能编写存储过程、函数和触发器
• PostgreSQL - 使用 postgresql 技能了解 PostgreSQL 特有功能
• Oracle - 使用 oracle 技能了解 Oracle 功能
• 基础 SQL - 使用 sql-fundamentals 学习 ANSI SQL 基础知识

反模式

反模式	问题	解决方案
不使用 SET NOCOUNT ON	网络开销	在所有存储过程中添加
使用游标	性能缓慢	使用基于集合的操作
缺少聚集索引	堆扫描	添加聚集索引
未在索引中使用 INCLUDE	键查找	使用覆盖索引
使用已弃用的 TEXT/NTEXT	兼容性问题	使用 VARCHAR(MAX)/NVARCHAR(MAX)
使用 @@IDENTITY 而非 SCOPE_IDENTITY	标识值错误	使用 SCOPE_IDENTITY()

快速故障排除

问题	诊断	修复
查询缓慢	SET STATISTICS IO ON	添加索引，检查执行计划
阻塞	sp_who2 或活动监视器	终止阻塞进程，优化查询
死锁	扩展事件、跟踪标志 1222	保持访问顺序一致，缩短事务
标识问题	DBCC CHECKIDENT	重新设定种子，或使用 SCOPE_IDENTITY
TempDB 争用	检查等待统计信息	添加 TempDB 文件，优化查询

参考文档

• 数据类型
• 索引
• 分区