SQL Server视图 #

视图特点：
├── 虚拟表，不存储数据
├── 简化复杂查询
├── 提供数据安全
├── 逻辑数据独立性
└── 可以像表一样查询

SQL Server视图类型
├── 标准视图
│   └── 基本的SELECT查询
├── 索引视图（物化视图）
│   └── 创建索引，存储数据
├── 分区视图
│   └── 跨多个表的联合视图
└── 系统视图
    └── 系统信息视图

CREATE VIEW [schema_name.]view_name [(column_name [, ...n])]
[WITH <view_attribute> [, ...n]]
AS
select_statement
[WITH CHECK OPTION]
[;]

<view_attribute> ::=
{
    ENCRYPTION
    | SCHEMABINDING
    | VIEW_METADATA
}

-- 创建简单视图
CREATE VIEW vw_active_users AS
SELECT id, name, email, status
FROM users
WHERE status = 1;

-- 使用视图
SELECT * FROM vw_active_users;

-- 创建带列名的视图
CREATE VIEW vw_user_summary (
    user_id,
    user_name,
    department,
    total_orders
) AS
SELECT 
    u.id,
    u.name,
    d.name,
    COUNT(o.id)
FROM users u
LEFT JOIN departments d ON u.department_id = d.id
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, d.name;

-- ENCRYPTION：加密视图定义
CREATE VIEW vw_sensitive_data
WITH ENCRYPTION
AS
SELECT id, name FROM users;

-- SCHEMABINDING：绑定架构
CREATE VIEW vw_users_bound
WITH SCHEMABINDING
AS
SELECT id, name, email
FROM dbo.users;

-- VIEW_METADATA：返回元数据
CREATE VIEW vw_users_meta
WITH VIEW_METADATA
AS
SELECT * FROM users;

-- 组合选项
CREATE VIEW vw_users_secure
WITH ENCRYPTION, SCHEMABINDING, VIEW_METADATA
AS
SELECT id, name FROM dbo.users;

-- 确保通过视图修改的数据仍可通过视图看到
CREATE VIEW vw_active_users_check AS
SELECT id, name, email, status
FROM users
WHERE status = 1
WITH CHECK OPTION;

-- 通过视图插入
INSERT INTO vw_active_users_check (name, email, status)
VALUES ('Test', 'test@example.com', 1);  -- 成功

INSERT INTO vw_active_users_check (name, email, status)
VALUES ('Test2', 'test2@example.com', 0);  -- 失败，违反CHECK OPTION

-- 修改视图
ALTER VIEW vw_active_users AS
SELECT id, name, email, status, created_at
FROM users
WHERE status = 1;

-- 删除视图
DROP VIEW vw_active_users;

-- 安全删除
IF OBJECT_ID('vw_active_users', 'V') IS NOT NULL
    DROP VIEW vw_active_users;

可更新视图条件：
├── SELECT列表没有聚合函数
├── 没有DISTINCT、GROUP BY、HAVING
├── 没有UNION、EXCEPT、INTERSECT
├── 没有派生列
├── 至少影响一个基表
└── 不违反约束

-- 创建可更新视图
CREATE VIEW vw_users_simple AS
SELECT id, name, email, status
FROM users;

-- 通过视图插入
INSERT INTO vw_users_simple (name, email, status)
VALUES ('NewUser', 'new@example.com', 1);

-- 通过视图更新
UPDATE vw_users_simple
SET name = 'UpdatedUser'
WHERE id = 1;

-- 通过视图删除
DELETE FROM vw_users_simple WHERE id = 1;

-- 创建复杂视图
CREATE VIEW vw_user_orders AS
SELECT 
    u.id AS user_id,
    u.name,
    o.id AS order_id,
    o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 创建INSTEAD OF触发器
CREATE TRIGGER tr_user_orders_insert
ON vw_user_orders
INSTEAD OF INSERT
AS
BEGIN
    -- 插入用户
    INSERT INTO users (name)
    SELECT name FROM inserted;
    
    -- 插入订单
    INSERT INTO orders (user_id, amount)
    SELECT 
        (SELECT id FROM users WHERE name = i.name),
        i.amount
    FROM inserted i;
END

-- 创建带SCHEMABINDING的视图
CREATE VIEW vw_order_summary
WITH SCHEMABINDING
AS
SELECT 
    user_id,
    COUNT_BIG(*) AS order_count,
    SUM(ISNULL(amount, 0)) AS total_amount
FROM dbo.orders
GROUP BY user_id;
GO

-- 创建唯一聚集索引
CREATE UNIQUE CLUSTERED INDEX ix_vw_order_summary
ON vw_order_summary(user_id);

-- 索引视图会实际存储数据

索引视图要求：
├── 必须使用SCHEMABINDING
├── SELECT列表必须包含COUNT_BIG(*)
├── 表名必须使用两段式命名（dbo.table）
├── 聚合函数必须使用ISNULL处理NULL
├── 不能使用OUTER JOIN
├── 不能使用UNION、子查询
└── 必须创建唯一聚集索引

-- 查询会自动使用索引视图
SELECT user_id, COUNT(*) AS cnt
FROM orders
GROUP BY user_id;

-- 强制使用索引视图
SELECT * FROM vw_order_summary WITH (NOEXPAND);

-- 不使用索引视图
SELECT * FROM vw_order_summary WITH (EXPAND VIEWS);

-- 创建分区表
CREATE TABLE orders_2023 (
    id INT PRIMARY KEY CHECK (id BETWEEN 1 AND 1000000),
    order_date DATE,
    amount DECIMAL(10,2)
);

CREATE TABLE orders_2024 (
    id INT PRIMARY KEY CHECK (id BETWEEN 1000001 AND 2000000),
    order_date DATE,
    amount DECIMAL(10,2)
);

-- 创建分区视图
CREATE VIEW vw_orders_all AS
SELECT * FROM orders_2023
UNION ALL
SELECT * FROM orders_2024;

-- 分区视图可更新条件：
-- 1. 每个表有CHECK约束
-- 2. 约束列相同
-- 3. UNION ALL连接

-- 通过分区视图插入
INSERT INTO vw_orders_all (id, order_date, amount)
VALUES (500000, '2023-06-01', 100.00);  -- 插入到orders_2023

INSERT INTO vw_orders_all (id, order_date, amount)
VALUES (1500000, '2024-06-01', 200.00);  -- 插入到orders_2024

-- 使用sp_helptext
EXEC sp_helptext 'vw_active_users';

-- 使用sys.sql_modules
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('vw_active_users');

-- 使用OBJECT_DEFINITION
SELECT OBJECT_DEFINITION(OBJECT_ID('vw_active_users'));

-- 查看视图依赖的对象
SELECT 
    OBJECT_NAME(referenced_id) AS referenced_object,
    referenced_class_desc
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('vw_active_users');

-- 查看依赖视图的对象
SELECT 
    OBJECT_NAME(referencing_id) AS dependent_object
FROM sys.sql_expression_dependencies
WHERE referenced_id = OBJECT_ID('vw_active_users');

视图设计原则：
├── 简化复杂查询
├── 提供数据安全层
├── 使用SCHEMABINDING保护依赖
├── 避免嵌套视图
├── 避免在视图中使用ORDER BY
├── 为视图添加注释
└── 定期审查视图使用情况

-- 视图不存储数据，每次查询都执行
-- 对于复杂查询，考虑使用索引视图

-- 查看视图执行计划
SELECT * FROM vw_active_users;

-- 使用索引视图提高性能
CREATE VIEW vw_stats
WITH SCHEMABINDING
AS
SELECT 
    department_id,
    COUNT_BIG(*) AS employee_count,
    SUM(ISNULL(salary, 0)) AS total_salary
FROM dbo.users
GROUP BY department_id;
GO

CREATE UNIQUE CLUSTERED INDEX ix_vw_stats
ON vw_stats(department_id);