53、SQL Server 2005 数据类型及相关知识全解析

wind

于 2025-07-20 13:01:07 发布

阅读量50

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL Server数据库设计与优化精华文章标签： SQL Server 2005 数据类型主键

本文链接：https://blog.youkuaiyun.com/wind/article/details/149562865

SQL Server数据库设计与优化精华专栏收录该内容

56 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

SQL Server 2005 数据类型及相关知识全解析

1. 主键与代理键

使用 GUID 作为主键来构建代理键，与使用标识列效果相当，特别是对于带有聚集索引的情况。新值会添加到索引末尾，而非随机分布在索引中。不过， uniqueidentifier 类型的列大小是整数列的四倍，需要四倍的存储空间。

2. 游标（Cursor）

游标是一种允许按行操作的机制，而非常规的按集合操作。 cursor 数据类型用于保存对 SQL Server T - SQL 游标的引用，不能作为表中的列，仅用于 T - SQL 代码中保存游标引用。

3. 表数据类型（Table）

特点： table 数据类型与 cursor 有一些共同点，用于保存结果集的引用。它实际上是将结果集存储为临时表，但无法获取该表的统计信息，除了在表声明中应用 PRIMARY KEY 和 UNIQUE 约束外，不能对其进行索引。与本地临时表不同，使用 table 数据类型变量的存储过程不会重新编译，因为它没有统计信息来改变执行计划。通常仅用于存储少量数据（一般为数百行，而非数千行），例如数据能存放在单个数据页时。
示例代码

-- 声明表变量
DECLARE @tableVar TABLE
(
    id int IDENTITY PRIMARY KEY,
    value varchar(100)
)
-- 插入数据
INSERT INTO @tableVar (value)
VALUES ('This is a cool test')
-- 查询数据
SELECT id, value
FROM @tableVar

执行结果如下：
| id | value |
| ---- | -------------------------------------- |
| 1 | This is a cool test |

用于用户定义函数返回表

-- 创建函数
CREATE FUNCTION table$testFunction
(
    @returnValue varchar(100)
) 
RETURNS @tableVar table
(
    value varchar(100)
)
AS
BEGIN
    INSERT INTO @tableVar (value)
    VALUES (@returnValue)
    RETURN
END
-- 调用函数
SELECT *
FROM dbo.table$testFunction('testValue')

执行结果如下：
| value |
| ------------- |
| testValue |

不受事务影响

DECLARE @tableVar TABLE
(
    id int IDENTITY,
    value varchar(100)
)
BEGIN TRANSACTION
INSERT INTO @tableVar (value)
VALUES ('This will still be there')  
ROLLBACK TRANSACTION
SELECT id, value
FROM @tableVar

执行结果如下：
| id | value |
| ----------- | ---------------------------------------- |
| 1 | This will still be there |

由于表数据类型不受事务影响，所以可用于错误日志记录，即使回滚事务，数据仍然可用。

4. XML 数据类型

XML 数据类型是 SQL Server 的新特性，可将 XML 文档存储在表的列中，具备搜索和索引功能。与之前版本使用 text 或 varchar(max) 存储 XML 不同，XML 数据类型提供了更丰富的功能，如检查值、设置默认值、根据架构验证 XML 等，还实现了 XQuery 用于查询 XML 数据，以及特殊的 XML DML 语句用于插入、删除和替换 XML 数据。不过，应仅在关系型解决方案无法满足需求时使用 XML 数据类型，而不是作为设计的替代品。

5. sql_variant 数据类型

特点： sql_variant 是一种通用数据类型，可存储多种 SQL Server 支持的数据类型，但不包括 varchar(max) 、 varbinary(max) 、 xml 、 text 、 ntext 、 rowversion/timestamp 和 sql_variant 。虽然 rowversion 不能直接存储在 sql_variant 中，但可先存储在 binary(8) 中，再将 binary(8) 存储在 sql_variant 中。
应用场景 ：可创建用户自定义的“属性包”类型表，避免创建包含大量可空列的长表。例如，创建 vehicle 表和 vehicleProperty 表：

-- 创建 vehicle 表
CREATE TABLE vehicle
(
    vehicleId   int constraint PKvehicle Primary Key,
    name        varchar(60) constraint AKvehicle UNIQUE
)
-- 插入数据
INSERT INTO vehicle
SELECT 1, 'Main Car'
UNION ALL
SELECT 2, 'Backup Truck'

-- 创建 vehicleProperty 表
CREATE TABLE vehicleProperty
(
    vehicleId int,
    propertyName varchar(30),
    propertyValue sql_variant,
    constraint PKproperty primary key (vehicleId, propertyName),
    constraint property$suppliespropertyvaluesfor$vehicle 
    foreign key (vehicleId) references vehicle (vehicleId)
)
-- 插入数据
INSERT INTO vehicleProperty
SELECT 1,'main driver','Joe'
UNION ALL
SELECT 1, 'interior color','beige'
UNION ALL
SELECT 2,'trailer hitch style','small'
UNION ALL
SELECT 2,'interior color','tan'

INSERT INTO vehicleProperty
SELECT 2,'tow capacity (lbs)',2000

-- 查询数据
SELECT *
FROM vehicleProperty

执行结果如下：
| vehicleId | propertyName | propertyValue |
| ----------- | ------------------------------ | ---------------------------------- |
| 1 | interior color | beige |
| 1 | main driver | Joe |
| 2 | interior color | tan |
| 2 | tow capacity (lbs) | 2000 |
| 2 | trailer hitch style | small |

数据旋转 ：使用 PIVOT 关键字可将数据旋转为水平方向：

SELECT  vehicleName, vehicleId, [main driver], [interior color],
[trailer hitch style], [tow capacity (lbs)]
FROM
(SELECT vehicle.name as vehicleName, vehicleProperty.vehicleId,
vehicleProperty.propertyName, vehicleProperty.propertyValue
FROM vehicle
join vehicleProperty
on vehicle.vehicleId = vehicleProperty.vehicleId) as properties
PIVOT
(
    max (propertyValue)
    FOR PropertyName IN
    ( [main driver], [interior color], [trailer hitch style], [tow capacity (lbs)])
) AS pvt
ORDER BY VehicleName;

执行结果如下：
| vehicleName | main driver | interior color | trailerHitchStyle | tow capacity (lbs) |
| ------------ | ----------- | --------------- | ----------------- | ------------------- |
| Backup Truck | NULL | tan | small | 2000 |
| Main Car | Joe | beige | NULL | NULL |

6. 存储变体数据的缺点

数据操作困难 ：将 sql_variant 列的数据分配给强类型数据类型时需谨慎，因为数据类型转换规则复杂，可能导致转换错误。
处理 NULL 值不同 ： sql_variant 的 NULL 值被认为没有数据类型，需与其他数据类型区别处理。
比较易出错 ： sql_variant 与其他数据类型的比较可能导致难以发现的编程错误，因为编译器可能无法检测到数据类型不兼容的问题。

7. 数据类型检测函数

-- 检测 varchar 类型的 sql_variant
DECLARE @varcharVariant sql_variant
SET @varcharVariant = '1234567890'
SELECT @varcharVariant AS varcharVariant,
SQL_VARIANT_PROPERTY(@varcharVariant,'BaseType') as baseType,
SQL_VARIANT_PROPERTY(@varcharVariant,'MaxLength') as maxLength,
SQL_VARIANT_PROPERTY(@varcharVariant,'Collation') as collation

-- 检测 numeric 类型的 sql_variant
DECLARE @numericVariant sql_variant
SET @numericVariant = 123456.789
SELECT @numericVariant AS numericVariant,
SQL_VARIANT_PROPERTY(@numericVariant,'BaseType') as baseType,
SQL_VARIANT_PROPERTY(@numericVariant,'Precision') as precision,
SQL_VARIANT_PROPERTY(@numericVariant,'Scale') as scale

8. 总结

SQL Server 2005 与早期版本相比有两大变化： image 和 text 数据类型分别被 varbinary(max) 和 varchar(max) 取代。虽然可以为每列使用 sql_variant 数据类型，但会带来更多问题。SQL Server 2005 提供了多种特定数据类型，选择合适的数据类型对性能和编程便利性至关重要。

9. 符号和数字相关

符号/数字	说明
#（哈希前缀）	用于表
.（句点）	用于角色名称
@@TRANCOUNT 全局变量	用于事务计数
[]（方括号）	用于括住 SQL Server 对象名称、分隔标识符、标识符和表名
_（下划线）	用于表名
“（双引号）	用于括住 SQL Server 对象名称、分隔标识符和表名
0、1 和 5 RAID 系统	有相关描述
1NF（第一范式）	有相关内容
266 错误消息	会接收到
1205 错误消息	会引发

10. 其他相关概念

最佳实践 ：涵盖 SQL Server 应用程序架构、概念数据建模、并发控制、数据完整性、数据库互操作性、数据库结构实现、索引、NET CLR、规范化、模式、安全性、存储过程和表结构等方面。
各种实体和数据类型 ：如 AFTER 触发器、聚合函数、 AdventureWorks 数据库、 ALTER 系列语句、备用键、原子属性、审计要求等。

通过以上对 SQL Server 2005 各种数据类型及相关概念的介绍，我们可以更深入地了解如何在实际应用中选择合适的数据类型和使用相关功能，以提高数据库的性能和可维护性。

11. 访问控制与事务相关

访问规则与控制 ：SQL Server 2005 有严格的访问控制。Codd 的访问规则（Access Rule）有其特定解释，它与整个数据库的访问控制体系相关。同时，在数据建模和数据库设计中，需要考虑不同用户对数据的访问权限，以确保数据的安全性和完整性。
事务相关
- ACID 属性 ：事务具有原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），即 ACID 属性。这些属性与事务的执行密切相关，保证了数据在并发环境下的正确性和可靠性。例如，在进行数据库操作时，一个事务要么全部执行成功，要么全部失败回滚，这体现了原子性。
- 事务命令 ：
  - BEGIN TRANSACTION ：用于开始一个事务，可通过它来确定事务的执行时机。
  - BEGIN DISTRIBUTED TRANSACTION ：用于开始分布式事务，适用于涉及多个数据库或服务器的事务场景。
  - COMMIT TRANSACTION ：用于提交事务，将事务中的操作永久保存到数据库中。
  - ROLLBACK TRANSACTION ：用于回滚事务，撤销事务中已经执行的操作。

12. 索引相关

B - tree 索引 ：B - tree 索引是一种高效的索引结构，在 SQL Server 中被广泛使用。它的效率高，可用于实现各种索引，能加快数据的查询速度。例如，在对大量数据进行查询时，使用 B - tree 索引可以减少磁盘 I/O 操作，提高查询性能。

graph TD;
    A[根节点] --> B[中间节点1];
    A --> C[中间节点2];
    B --> D[叶子节点1];
    B --> E[叶子节点2];
    C --> F[叶子节点3];
    C --> G[叶子节点4];

索引使用建议 ：在使用临时查询（ad hoc queries）时，可以结合多个索引来提高查询性能。但同时，要注意索引的维护成本，避免创建过多不必要的索引，以免影响数据的插入、更新和删除操作性能。

13. 数据建模与规范化

实体与属性
- 抽象实体 ：在数据建模中，存在抽象实体，它们代表一类具有共同特征的实体，但不对应具体的物理对象。例如，在一个企业管理系统中，“部门”可以是一个抽象实体，它包含了不同具体部门的共性信息。
- 属性命名 ：属性命名应避免使用缩写，除非有特殊情况。良好的属性命名有助于提高数据模型的可读性和可维护性。例如，在一个学生信息管理系统中，使用“studentName” 而不是 “sName” 作为学生姓名的属性名。
规范化
- 第一范式（1NF） ：要求属性具有原子性，即属性不能再分解为更小的部分。例如，在一个订单表中，“订单详情” 不能作为一个属性，而应该将其拆分为 “商品名称”、“商品数量” 等原子属性。
- 第二范式（2NF） ：在满足第一范式的基础上，要求非主属性完全依赖于主键。例如，在一个学生选课表中，如果主键是 “学生 ID” 和 “课程 ID”，那么 “成绩” 这个非主属性应该完全依赖于这两个主键，而不是只依赖其中一个。
- 第三范式（3NF） ：在满足第二范式的基础上，要求非主属性之间不存在传递依赖。例如，在一个员工信息表中，如果 “部门名称” 依赖于 “部门 ID”，“员工 ID” 依赖于 “部门 ID”，那么 “部门名称” 不应该通过 “员工 ID” 来间接依赖，而应该直接与 “部门 ID” 关联。
- BCNF（Boyce - Codd 范式） ：比第三范式更严格，要求每个非平凡的函数依赖的左边都是候选键。它与第四范式有一定的关联和区别，在数据建模中，需要根据具体情况来判断是否需要满足 BCNF。

14. 触发器相关

AFTER 触发器 ：
- 功能用途 ：可用于多种场景，如级联插入、级联从子表到父表的操作、维护审计跟踪、对多行进行范围检查以及处理跨数据库和服务器的关系等。
- 示例：在 Accounting.AccountActivity 表中添加触发器，可用于记录该表的操作信息，实现审计功能。

-- 示例：为 Accounting.AccountActivity 表添加触发器
CREATE TRIGGER trg_AccountActivity_Audit
ON Accounting.AccountActivity
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- 记录操作信息到审计表
    INSERT INTO AuditTable (TableName, Operation, DateTime)
    VALUES ('Accounting.AccountActivity', 
            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, 
            GETDATE());
END;

15. 数据库操作与性能优化

临时 SQL（ad hoc SQL）
- 优点：具有灵活性、可控制性和性能优势。可以根据不同的需求动态生成 SQL 语句，并且能够对查询进行精细的控制。
- 缺点：存在一些陷阱，如批量执行多个语句时可能出现问题、低内聚高耦合以及安全问题等。
- 性能调优 ：可以通过合理使用索引、优化查询语句等方式来提高临时 SQL 的性能。例如，使用 EXPLAIN 命令分析查询执行计划，找出性能瓶颈。
存储过程 ：与临时 SQL 相比，存储过程具有更好的性能和可维护性。在编写存储过程时，需要遵循一些最佳实践，如避免使用过多的动态 SQL、合理使用参数等。

16. 总结与展望

通过对 SQL Server 2005 中众多数据类型、数据库操作、数据建模和规范化等方面的介绍，我们可以看到，合理选择数据类型、正确使用各种数据库功能和遵循最佳实践对于构建高效、安全和可维护的数据库系统至关重要。在实际应用中，需要根据具体的业务需求和数据特点，综合考虑各种因素，以实现数据库系统的最佳性能和稳定性。

未来，随着数据库技术的不断发展，SQL Server 也会不断更新和完善。我们需要持续关注新技术的发展，不断学习和掌握新的知识和技能，以适应不断变化的数据库应用场景。

总之，深入理解 SQL Server 2005 的各种特性和功能，将有助于我们在数据库开发和管理中做出更明智的决策，提高工作效率和质量。