SQLServer-guide

default

markdown

# Microsoft SQL Server 参考手册

**SQL Server 版本**: 2019 (15.x)  
**镜像**: `mcr.microsoft.com/mssql/server:2019-latest`  
**指定中文字符集**: `COLLATE Chinese_PRC_CI_AS`

> **重要提示**: 创建数据库时务必指定字符集，否则中文会出现乱码：
>
> ```sql
> CREATE DATABASE snest_dev COLLATE Chinese_PRC_CI_AS;
> ```

---

## 目录

1. [安装与部署](#第一节-安装与部署)
2. [连接与配置](#第二节-连接与配置)
3. [核心特性与三数据库对比](#第三节-核心特性与三数据库对比)
4. [Java 适配开发](#第四节-java-适配开发)
5. [参考文档与附录](#第五节-参考文档与附录)

---

## 第一节. 安装与部署

### 1.1 前置准备

| 资源         | 要求                                                         |
| :----------- | :----------------------------------------------------------- |
| **操作系统** | Windows 10/11、Windows Server 2016+、Linux (Ubuntu 16.04+、RHEL 7+)、Docker 19.03+ |
| **安装包**   | `mcr.microsoft.com/mssql/server:2019-latest`                 |
| **端口**     | 宿主机 1433（映射容器 1433）                                 |
| **内存**     | 最低 2GB RAM（推荐 4GB+）                                    |
| **磁盘**     | 至少 6GB 可用空间                                            |
| **用户权限** | 具有 sudo 权限的普通用户                                     |

### 1.2 Docker 安装

#### 拉取镜像

```bash
# 拉取 SQL Server 2019 最新版
docker pull mcr.microsoft.com/mssql/server:2019-latest

# 或拉取特定 CU 版本
docker pull mcr.microsoft.com/mssql/server:2019-CU27-ubuntu-20.04
```

#### 目录与权限

```bash
# 创建数据目录
sudo mkdir -p /usr/sie/mssql/{data,log,backup}

# 设置权限（容器内 mssql 用户 UID 10001）
sudo chown -R 10001:10001 /usr/sie/mssql
```

#### 创建并启动容器

```bash
docker run -d --privileged=true \
  -e "ACCEPT_EULA=Y" \
  -e "MSSQL_SA_PASSWORD=SNEST_TEST#123" \
  -e "MSSQL_PID=Developer" \
  -v /usr/sie/mssql/data:/var/opt/mssql/data \
  -v /usr/sie/mssql/log:/var/opt/mssql/log \
  -v /usr/sie/mssql/backup:/var/opt/mssql/backup \
  --name mssql2019 \
  --hostname mssql2019 \
  -p 1433:1433 \
  mcr.microsoft.com/mssql/server:2019-latest
```

**环境变量说明**:

**一键启动脚本**:

```bash
docker run -d --privileged=true -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=SNEST_TEST#123" -e "MSSQL_PID=Developer" -v /usr/sie/mssql/data:/var/opt/mssql/data -v /usr/sie/mssql/log:/var/opt/mssql/log -v /usr/sie/mssql/backup:/var/opt/mssql/backup --name mssql2019 -p 1433:1433 mcr.microsoft.com/mssql/server:2019-latest
```

#### 验证启动

```bash
# 查看日志
docker logs -f --tail 100 mssql2019

# 查看容器状态
docker ps | grep mssql2019
```

成功标志：`SQL Server is now ready for client connections.`

#### 进入容器

```bash
docker exec -it mssql2019 bash

# 容器内连接
/opt/mssql-tools18/bin/sqlcmd -S localhost -U SA -P "SNEST_TEST#123"
```

### 1.3 数据库与用户初始化

#### 创建业务数据库及用户

```sql
-- 使用 SA 登录
USE master;
GO

-- 创建数据库（务必指定字符集）
CREATE DATABASE snest_dev COLLATE Chinese_PRC_CI_AS;
GO

-- 创建登录名（服务器级别）
CREATE LOGIN snest_dev 
WITH PASSWORD = 'SNEST_DEV#123', 
CHECK_POLICY = OFF;
GO

USE snest_dev;
GO

-- 创建用户（数据库级别）
CREATE USER snest_dev FOR LOGIN snest_dev;
GO

-- 创建 Schema（与用户不同名，推荐）
CREATE SCHEMA dev_sch AUTHORIZATION snest_dev;
GO

-- 设置默认 Schema
ALTER USER snest_dev WITH DEFAULT_SCHEMA = dev_sch;
GO

-- 授权
GRANT CONTROL ON SCHEMA::dev_sch TO snest_dev;
GO
```

#### 验证配置

```sql
-- 查看用户及默认 Schema
SELECT 
    name AS UserName,
    default_schema_name AS DefaultSchema
FROM sys.database_principals
WHERE name = 'snest_dev';

-- 查看 Schema
SELECT 
    name AS SchemaName
FROM sys.schemas
WHERE name = 'dev_sch';
```

### 1.4 常用运维命令

---

## 第二节. 连接与配置

### 2.1 客户端连接

#### DBeaver 配置

| 配置项 | 值                                   |
| :----- | :----------------------------------- |
| 主机   | `<宿主机IP>`                         |
| 端口   | `1433`                               |
| 数据库 | `snest_dev`                          |
| 用户名 | `snest_dev`                          |
| 驱动   | Microsoft JDBC Driver for SQL Server |

> 注意：DBeaver 默认使用 `mssql-jdbc` 驱动

### 2.2 JDBC 配置

#### 标准配置

```properties
######## SQL Server DBCP ##########
driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://ip:1433;databaseName=snest_dev;encrypt=true;trustServerCertificate=true
username=snest_dev
password=******
```

#### 集成环境配置

```properties
######## 集成环境 DBCP ##########
driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://ip:1433;databaseName=snest_dev;encrypt=true;trustServerCertificate=true;sendStringParametersAsUnicode=false
username=snest_dev
password=******
```

#### 常用 URL 参数

---

## 第三节. 核心特性与三数据库对比

### 3.1 标识符与引号

#### SQL Server 标识符规则

| 场景           | 行为                                       |
| :------------- | :----------------------------------------- |
| 大小写敏感     | 默认**不敏感**（取决于排序规则 `CI`/`CS`） |
| 方括号 `[]`    | SQL Server 特有，推荐用于关键字/特殊字符   |
| 双引号 `""`    | 仅当 `QUOTED_IDENTIFIER ON` 时作为标识符   |
| 单引号 `''`    | 字符串字面量                               |
| Unicode 字符串 | `N'中文'` 前缀                             |

```sql
-- 大小写不敏感
SELECT * FROM MyTable;  -- 等同于 MYTABLE、mytable

-- 方括号包裹关键字
SELECT [SELECT], [FROM] FROM [My Table];  -- 表名含空格

-- QUOTED_IDENTIFIER ON 时使用双引号
SET QUOTED_IDENTIFIER ON;
SELECT * FROM "MyTable";
```

#### QUOTED_IDENTIFIER 详解

| 设置         | 双引号行为          | 使用场景                         |
| :----------- | :------------------ | :------------------------------- |
| `ON`（默认） | 标识符（表名/列名） | 索引视图、计算列索引、保留关键字 |
| `OFF`        | 字符串字面量        | 不推荐，仅兼容性场景             |

**重要限制**：

- 创建索引视图、计算列索引、XML索引时**必须**为 `ON`
- 存储过程创建时捕获当前设置，后续调用沿用该设置

```sql
-- 标准存储过程模板
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE usp_GetCustomers
AS
BEGIN
    SELECT [name], [address] FROM [customers];
END
GO
```

#### 三数据库引号对比

| 特性          | SQL Server                              | PostgreSQL | MySQL          |
| :------------ | :-------------------------------------- | :--------- | :------------- |
| 标识符大小写  | 不敏感（CI排序规则）                    | 转小写存储 | 取决于OS       |
| 标识符引号    | `[]` / `""`（需`QUOTED_IDENTIFIER ON`） | `""`       | `` ` `` / `""` |
| 字符串引号    | `''`                                    | `''`       | `''` / `""`    |
| Unicode字符串 | `N'...'`                                | 直接支持   | 直接支持       |

---

### 3.2 数据类型

#### 日期时间类型（重点）

**⚠️ 关键陷阱**：SQL Server 的 `TIMESTAMP` **不是时间类型**，而是二进制自增戳（`ROWVERSION`），与 MySQL/PostgreSQL 的 `TIMESTAMP` 完全不同！

```sql
-- SQL Server 日期时间
SELECT GETDATE();           -- DATETIME，精度3.33ms
SELECT SYSDATETIME();       -- DATETIME2，精度100ns（推荐）
SELECT GETUTCDATE();        -- UTC时间

-- MySQL
SELECT NOW();               -- 当前时间
SELECT UTC_TIMESTAMP();     -- UTC时间

-- PostgreSQL
SELECT CURRENT_TIMESTAMP;   -- 带时区
SELECT NOW();               -- 同 CURRENT_TIMESTAMP
```

**JDBC 跨数据库兼容写法**：

```java
// 所有数据库通用
ps.setObject(parameterIndex, timestamp, java.sql.Types.TIMESTAMP);
```

#### 字符串类型

```sql
-- SQL Server Unicode（必须加N前缀）
INSERT INTO users (name) VALUES (N'张三');

-- MySQL/PostgreSQL（直接插入）
INSERT INTO users (name) VALUES ('张三');
```

#### 布尔类型

| 数据库         | 类型                          | 值                        |
| :------------- | :---------------------------- | :------------------------ |
| **SQL Server** | `BIT`                         | `0` / `1` / `NULL`        |
| **MySQL**      | `BOOLEAN`（实际`TINYINT(1)`） | `0` / `1`                 |
| **PostgreSQL** | `BOOLEAN`                     | `true` / `false` / `NULL` |

---

### 3.3 分页语法（重点）

| 数据库         | 语法                                   | ORDER BY 要求 |
| :------------- | :------------------------------------- | :------------ |
| **SQL Server** | `OFFSET x ROWS FETCH NEXT y ROWS ONLY` | **必须**      |
| **MySQL**      | `LIMIT offset, count`                  | 可选          |
| **PostgreSQL** | `LIMIT count OFFSET offset`            | 可选          |

```sql
-- SQL Server（2012+，必须带ORDER BY）
SELECT * FROM users 
WHERE status = 1 
ORDER BY id 
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

-- MySQL
SELECT * FROM users WHERE status = 1 ORDER BY id LIMIT 0, 10;

-- PostgreSQL
SELECT * FROM users WHERE status = 1 ORDER BY id LIMIT 10 OFFSET 0;
```

**Provider 实现**：

```java
// SQL Server
public String getPageSQL(String sql, int pageNo, int pageSize) {
    int offset = (pageNo - 1) * pageSize;
    return sql + " OFFSET " + offset + " ROWS FETCH NEXT " + pageSize + " ROWS ONLY";
}

// MySQL
public String getPageSQL(String sql, int pageNo, int pageSize) {
    int offset = (pageNo - 1) * pageSize;
    return sql + " LIMIT " + offset + ", " + pageSize;
}

// PostgreSQL
public String getPageSQL(String sql, int pageNo, int pageSize) {
    int offset = (pageNo - 1) * pageSize;
    return sql + " LIMIT " + pageSize + " OFFSET " + offset;
}
```

---

### 3.4 UPDATE 语句别名（重点）

```sql
-- ❌ SQL Server 不支持（MySQL/PostgreSQL风格）
UPDATE [t] SET [update_user]=?, [update_date]=? 
FROM [tenant_user_instance] AS [t] 
WHERE [t].[id] IN (?);

-- ✅ SQL Server 正确写法
UPDATE t
SET update_user = ?, update_date = SYSDATETIME()
FROM tenant_user_instance t
WHERE t.id = ?;

-- MySQL/PostgreSQL 风格
UPDATE tenant_user_instance t
SET t.update_user = ?, t.update_date = NOW()
WHERE t.id = ?;
```

---

### 3.5 自增列

```sql
-- SQL Server 自增
CREATE TABLE users (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(50));
INSERT INTO users (name) VALUES (N'张三');
SELECT SCOPE_IDENTITY();  -- 获取刚插入的ID

-- 显式插入（数据迁移）
SET IDENTITY_INSERT users ON;
INSERT INTO users (id, name) VALUES (100, N'迁移数据');
SET IDENTITY_INSERT users OFF;
```

**限制**：`SET IDENTITY_INSERT` 一次只能对一个表设置为 `ON`。

---

### 3.6 Schema 管理

```sql
-- SQL Server Schema 管理
CREATE SCHEMA dev_sch AUTHORIZATION snest_dev;
ALTER USER snest_dev WITH DEFAULT_SCHEMA = dev_sch;
GRANT CONTROL ON SCHEMA::dev_sch TO snest_dev;

-- 查询时
SELECT * FROM dev_sch.mytable;  -- 完整限定名
SELECT * FROM mytable;            -- 使用默认Schema（如果设置了）
```

**Schema 不可见问题排查**：

```sql
-- 查看当前用户默认Schema
SELECT default_schema_name FROM sys.database_principals WHERE name = USER;

-- 查看所有Schema
SELECT name FROM sys.schemas;

-- 查看Schema下的表
SELECT * FROM sys.tables WHERE schema_id = SCHEMA_ID('dev_sch');
```

---

### 3.7 事务与 DDL

```sql
-- SQL Server（部分支持）
BEGIN TRANSACTION;
CREATE TABLE test (id INT);  -- 可回滚
-- CREATE DATABASE test2;     -- 隐式提交，无法回滚
ROLLBACK;

-- PostgreSQL（完全支持）
BEGIN;
CREATE TABLE test (id INT);  -- 在事务中
ROLLBACK;  -- 完全回滚

-- MySQL（不支持）
START TRANSACTION;
CREATE TABLE test (id INT);  -- 隐式提交！
ROLLBACK;  -- 无效，表已创建
```

**SQL Server 隐式提交的 DDL**：

- `CREATE DATABASE` / `ALTER DATABASE` / `DROP DATABASE`
- `CREATE/DROP FULLTEXT INDEX`

---

### 3.8 索引

| 特性         | SQL Server               | MySQL              | PostgreSQL           |
| :----------- | :----------------------- | :----------------- | :------------------- |
| 聚集索引     | 支持（一个表只能有一个） | 仅InnoDB主键聚集   | 不支持               |
| 索引长度限制 | 1700字节                 | 3072字节（InnoDB） | 理论上无限制         |
| 全文索引     | 内置                     | `FULLTEXT`         | `tsvector`/`tsquery` |
| 部分索引     | 不支持                   | 不支持             | 支持（`WHERE`条件）  |
| 表达式索引   | 不支持（可用计算列）     | 不支持             | 支持                 |

```sql
-- SQL Server 索引
CREATE INDEX IX_users_email ON users(email);
CREATE UNIQUE INDEX UQ_users_phone ON users(phone);
CREATE CLUSTERED INDEX IX_users_created ON users(created_date);  -- 聚集索引
CREATE INDEX IX_users_name ON users(name) INCLUDE (email, phone);   -- 包含列
```

---

### 3.9 JSON 支持（2016+）

```sql
-- SQL Server JSON
DECLARE @json NVARCHAR(MAX) = N'{"name": "张三", "age": 30}';
SELECT JSON_VALUE(@json, '$.name');        -- 标量值
SELECT JSON_QUERY(@json, '$.skills');      -- 对象/数组
SELECT * FROM users FOR JSON AUTO;        -- 表转JSON

-- JSON索引
ALTER TABLE users ADD json_name AS JSON_VALUE(info, '$.name');
CREATE INDEX IX_users_json_name ON users(json_name);
```

---

### 3.10 类型转换

```sql
-- SQL Server 样式代码
SELECT CONVERT(VARCHAR, GETDATE(), 120);  -- yyyy-mm-dd hh:mi:ss(24h)
SELECT CONVERT(VARCHAR, GETDATE(), 121);  -- yyyy-mm-dd hh:mi:ss.mmm（含毫秒）
SELECT CONVERT(VARCHAR, GETDATE(), 112);  -- yyyymmdd
```

---

### 3.11 字符串连接

| 数据库         | 连接符                                     | NULL处理                               |
| :------------- | :----------------------------------------- | :------------------------------------- |
| **SQL Server** | `+` / `CONCAT()`                           | `+`传播NULL，`CONCAT()`视NULL为空串    |
| **MySQL**      | `CONCAT()` / `\|\|`（需`PIPES_AS_CONCAT`） | `CONCAT()`视NULL为空串                 |
| **PostgreSQL** | `\|\|` / `CONCAT()`                        | `\|\|`传播NULL，`CONCAT()`视NULL为空串 |

```sql
-- SQL Server
SELECT 'Hello' + ' ' + 'World';           -- Hello World
SELECT 'Hello' + NULL;                    -- NULL（传播）
SELECT CONCAT('Hello', NULL, 'World');    -- HelloWorld（NULL视为空串）

-- 分组聚合（2017+）
SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) FROM users;
```

---

### 3.12 日期函数

---

### 3.13 系统视图

**对比**：

- `sys` 视图：SQL Server特有，性能更好，信息更详细
- `INFORMATION_SCHEMA`：ANSI标准，跨数据库兼容

```sql
-- SQL Server 推荐（sys视图）
SELECT * FROM sys.tables WHERE name = 'users';
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('users');

-- 跨数据库兼容（INFORMATION_SCHEMA）
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users';
```

---

### 3.14 错误处理

```sql
-- SQL Server TRY...CATCH
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO users (id) VALUES (1);
    INSERT INTO users (id) VALUES (1);  -- 主键冲突
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ERROR_LINE() AS ErrorLine;
END CATCH;
```

**对比**：

- SQL Server：`TRY...CATCH`，可在CATCH块中决定回滚或继续
- PostgreSQL：事务进入中止状态，必须回滚
- MySQL：存储过程中使用`DECLARE CONTINUE/EXIT HANDLER`

---

## 第四节. Java 适配开发

### 4.1 SQLServerProvider 实现

```java
public class SQLServerProvider extends AbstractSQLProvider {
    
    @Override
    public String getDBType() {
        return DBType.SQLServer.getName();
    }
  
    @Override
    public String getPageSQL(String sql, int pageNo, int pageSize) {
        int offset = (pageNo - 1) * pageSize;
        return sql + " OFFSET " + offset + " ROWS FETCH NEXT " + pageSize + " ROWS ONLY";
    }
    
    @Override
    public String getCurrentTimeSQL() {
        return "SELECT SYSDATETIME()";
    }
}
```

### 4.2 关键适配点

---

## 第五节. 参考文档与附录

### 5.1 官方文档

### 5.2 兼容性速查表

**建议**：SQL Server 2019 是长期支持版本（LTSC），功能稳定，适合生产环境。

## 六、快速迁移检查清单

从 MySQL/PostgreSQL 迁移到 SQL Server 时，请逐项检查：

- [ ] **标识符**：反引号 `` ` `` → 中括号 `[]`
- [ ] **分页**：`LIMIT offset, count` → `OFFSET ... FETCH`
- [ ] **日期函数**：`NOW()` → `GETDATE()`，`DATE_ADD` → `DATEADD`
- [ ] **字符串连接**：`CONCAT()` / `||` → `+` / `CONCAT()`
- [ ] **自增列**：`AUTO_INCREMENT` / `SERIAL` → `IDENTITY(1,1)`
- [ ] **布尔值**：`BOOLEAN` / `TINYINT(1)` → `BIT`
- [ ] **Unicode**：中文场景使用 `NVARCHAR` 替代 `VARCHAR`
- [ ] **系统表**：优先使用 `sys.*` 视图而非 `INFORMATION_SCHEMA`
- [ ] **扩展属性**：使用 `sp_addextendedproperty` 存储元数据
- [ ] **UPDATE 别名**：使用 `FROM` 子句语法

---

**文档版本**: v2.0  
**最后更新**: 2026-03-24  
**适用版本**: SQL Server 2019 (15.x) / MySQL 8.0+ / PostgreSQL 14+

Uploading file...

Edit message:

Cancel

Editing SQLServer-guide

Sidebar