超级会员免费看
SQL Server 开发中的语言选择与数据库互操作性指南
1. SQL Server 中 CLR 的应用与思考
在 SQL Server 中引入公共语言运行时（CLR）带来了强大的功能、灵活性和更多设计选择。不过，数据库管理员（DBAs）担心 CLR 可能不必要且会被开发者滥用。尽管任何技术都有被滥用的可能，但我们不应轻易否定 SQLCLR，而应考虑在哪些场景下它能作为有效工具来改进数据库设计。
-
CLR 适用场景示例
- 标量和表值函数 ：用 .NET 语言编写的标量和表值函数，是改进设计的不错切入点。
- 替代扩展存储过程 ：若需要在数据库服务器上运行特定功能，使用 SQLCLR 替代扩展存储过程是合理的。
- 用户定义类型（UDTs）和用户定义聚合 ：这是扩展 SQL Server 的两种新方式。不过，实现 UDTs 前需谨慎考虑，因为它们存在维护和版本控制问题。用户定义聚合编写起来可能有一定难度，但如果数据库设计需要特殊聚合功能，无论是自行编写、下载还是购买相关实现，都可能有助于提升设计和性能。
2. T - SQL 与 .NET 的选择指南
2.1 选择 T - SQL 的准则
T - SQL 在 SQL Server 2005 中不仅不会被淘汰，还得到了增强。对于主要用于访问数据的例程，通常优先考虑使用 T - SQL。不过，若例程包含大量条件逻辑、循环结构或不适合基于集合编程的复杂过程代码，且使用 .NET 能带来性能提升或更易于编码和维护，那么可以考虑采用 .NET 方法。
T - SQL 是基于集合逻辑的最佳工具，在例程需要基于集合的功能时应优先考虑。除非有明确的好处，否则不建议将 T - SQL 例程重写为 .NET 代码，因为这样可能会降低代码效率，且更难维护。
T - SQL 还增强了一些特性，如递归查询、数据透视功能、新的 TRY - CATCH 语法用于改进错误处理等。如果可以利用这些新特性使代码更快、更易编写和维护，应在尝试用 .NET 编写等效功能之前考虑使用 T - SQL。
在数据库架构中使用触发器时，T - SQL 几乎是唯一的选择。对于触发器中的复杂验证，可使用 .NET 标量函数并在 T - SQL 触发器中调用。若触发器中存在大量过程逻辑，.NET 可能是比 T - SQL 更好的选择，但使用复杂触发器时需谨慎。
2.2 选择 .NET 的准则
.NET 集成是一项强大的技术，虽并非适用于所有场景，但相较于 T - SQL 有一些值得考虑的优势。.NET 编译为本机代码，更适合处理复杂逻辑和 CPU 密集型代码。
-
.NET 适用场景
- 无数据访问的标量函数 ：编写不需要访问数据的标量函数，CLR 方法通常比 T - SQL 对应方法快一个数量级。
- 利用 .NET 框架丰富支持 ：.NET 用户定义函数可利用 .NET 框架的丰富支持，如字符串操作、正则表达式和数学函数等优化类。
- 流式表值函数 ：这是 .NET 的另一个出色应用，可将任意数据结构（如文件系统或注册表）作为行集公开，使查询处理器能理解数据。
- 替代扩展存储过程 ：.NET 是编写扩展存储过程的安全且相对简单的替代方案，提供了对 .NET 框架中大量类的访问，比使用 C++ 更安全。
- 用户定义聚合和 .NET 基 UDTs ：用户定义聚合只能用 .NET 编写，可实现 SQL Server 未提供的聚合功能；.NET UDTs 在扩展类型系统时具有一定优势，但不应用于在数据库中定义业务对象。
3. 最佳实践
3.1 存储过程与即席访问
在架构 SQL Server 应用程序时，有两种主要方法：使用存储过程作为主要接口，或使用在服务器外部构建的即席调用。使用存储过程有诸多优点：
-
封装数据库细节
：作为预编译的 SQL 语句批处理，存储过程能为数据库提供一个良好的接口，将数据库细节封装起来，使调用者无需了解具体实现。
-
提升性能
：主要是因为调优针对的是已知的查询集，而非程序员随意编写且可能未经测试的查询。
-
定义一致的安全接口
：可定义一致的安全接口，方便对用户进行不同场景下的数据库资源访问授权。
不过，并非所有系统都使用存储过程，即席访问也能构建出优秀的系统，但可能导致代码难以维护，使客户端工具与数据库结构紧密耦合。
3.2 CLR 使用的最佳实践
- 用户定义函数 ：无数据访问时，CLR 几乎总是构建用户定义函数的更好方式；需要数据访问时，取决于函数中的操作类型，但大多数数据访问函数至少应先使用 T - SQL 实现。
- 存储过程 ：对于典型的数据导向存储过程，T - SQL 通常是最佳选择；使用 CLR 时，创建替代扩展存储过程（通常以 xp_ 命名）更简单、安全。
- 用户定义类型 ：除非有充分理由，否则应避免使用。例如，需要定义具有特定操作（如计算两点间距离）的复杂数据类型时可考虑使用。
- 用户定义聚合 ：只能用 .NET 创建，可对数据组进行有趣的操作。
- 触发器 ：使用 CLR 语言构建触发器的理由不多，触发器主要用于数据操作。
4. 数据库互操作性
4.1 设计互操作性数据库的步骤
设计具有互操作性的数据库时，需考虑更多额外要求，目标是从一开始就设计出无需或只需少量重新设计就能在其他数据库平台上运行的数据库。
-
步骤一：数据类型选择
设计数据库时，使用易于在主要数据库平台间转换的数据类型。不同数据库平台可能支持名称相似但实现不同的数据类型，可参考以下数据类型兼容性图表：
| 所有数据库平台的所有数据类型 | DB2 | MySQL | Oracle | MSSQL | 用于互操作性的使用类型 |
|---|---|---|---|---|---|
| bfile | X | image | |||
| bigint | X | X | X | bigint | |
| binary | X | image | |||
| binary_float | X | varbinary | |||
| binary_double | X | varbinary | |||
| bit | X | X | bit | ||
| blob | X | X | X | image | |
| bool, boolean | X | bit, char(1) | |||
| char, character | X | X | X | X | |
| char for bit data | X | char(1) | |||
| clob | X | X | text, varchar(max) | ||
| cursor | X | None | |||
| datalink | X | varchar | |||
| date | X | X | X | datetime | |
| datetime | X | X | |||
| dbclob | X | text | |||
| dlob | X | text | |||
| dec, decimal | X | X | X | X | |
| double, double precision | X | X | X | X | |
| enum | X | varchar | |||
| float | X | X | X | X | |
| graphic | X | image | |||
| image | X | ||||
| int, integer | X | X | X | X | |
| interval day to second | X | varchar | |||
| interval year to month | X | varchar | |||
| long | X | text, varchar(max) | |||
| long varchar | X | text, varchar(max) | |||
| longblob | X | image | |||
| long raw | X | image | |||
| long vargraphic | X | image | |||
| longtext | X | text, varchar(max) | |||
| mediumtext | X | varchar | |||
| mediumblob | X | image | |||
| money | X | float | |||
| national character varying, national char varying, nchar varying, nvarchar | X | X | X | ||
| nchar, national char, national character | X | X | |||
| nclob | X | ntext | |||
| ntext, national text | X | nclob | |||
| nvarchar2(n) | X | varchar | |||
| number | X | X | X | X | |
| raw | X | image | |||
| real | X | X | X | X | |
| rowid | X | int with identity | |||
| rowversion | X | None | |||
| set | X | varchar | |||
| smalldatetime | X | datetime | |||
| smallint | X | X | X | X | |
| smallmoney | X | None | |||
| sql_variant | X | None | |||
| table | X | None | |||
| text | X | X | clob | ||
| time | X | datetime | |||
| timestamp | X | X | X | datetime | |
| tinyint | X | int | |||
| uniqueidentifier | X | varchar | |||
| urowid | X | uniqueidentifier | |||
| varbinary | X | binary | |||
| varchar, char varying, character varying | X | X | X | X | |
| varchar2 | X | varchar | |||
| varchar for bit data | X | varbinary | |||
| vargraphic | X | image | |||
| XML | X | dlob, clob |
-
步骤二：标识符规则
创建数据库对象时，必须遵守相应平台的标识符规则。不同数据库平台的标识符规则存在差异，如下表所示：
| 特性 | 平台 | 规范 |
|---|---|---|
| 标识符大小 | DB2 | 128 个字符，取决于对象 |
| MySQL | 64 个字符 | |
| Oracle | 30 字节（字符数取决于字符集）；数据库名称限制为 8 字节 | |
| SQL Server | 128 个字符（临时表限制为 116 个字符）；旧版本使用 DB - LIB 的 SQL Server 限制结果集列名长度为 30 个字符 | |
| 标识符可包含 | DB2 | 任意数字、大写字符、数字或下划线字符 |
| MySQL | 任意数字、字符或符号 | |
| Oracle | 任意数字、字符以及下划线 (_)、井号 (#) 和美元符号 ($) | |
| SQL Server | 任意数字、字符以及下划线 (_)、at 符号 (@)、井号 (#) 和美元符号 ($) | |
| 标识符必须以 | DB2 | 字母开头 |
| MySQL | 字母或数字（但不能完全由数字组成） | |
| Oracle | 字母开头 | |
| SQL Server | 字母（符合 Unicode 标准 3.2）、下划线 (_)、at 符号 (@) 或井号 (#) 开头 | |
| 标识符不能包含 | DB2 | 空格或特殊字符 |
| MySQL | 句点 (.)、斜杠 (/) 或 ASCII(0) 和 ASCII(255)；引号 (‘) 和双引号 (“) 仅允许在带引号的标识符中使用 | |
| Oracle | 空格、双引号 (“) 或特殊字符 | |
| SQL Server | 空格或特殊字符 | |
| 是否允许带引号的标识符 | DB2 | 是 |
| MySQL | 是 | |
| Oracle | 是 | |
| SQL Server | 是 | |
| 带引号的标识符符号 | DB2 | 双引号 (“) |
| MySQL | 在 ANSI 兼容模式下使用单引号 (‘) 或双引号 (“) | |
| Oracle | 双引号 (“) | |
| SQL Server | 双引号 (“) 或方括号 ( [ ] )；优先使用方括号 |
5. 总结
SQL Server 2005 为编写访问数据的代码提供了更多选择。在架构关系数据库应用程序时，一方面要遵循规范化原则设计数据库结构，减少数据冗余和异常；另一方面，在编写访问数据的代码时，可根据具体情况选择存储过程或即席访问，以及 T - SQL 或 .NET。
CLR 作为一项强大的技术，能在合适的场景下提升查询性能和增加数据库功能，但需谨慎使用。同时，在设计具有互操作性的数据库时，要注意数据类型和标识符规则的选择，以确保数据库能在不同平台间顺利迁移。总之，要明智地使用各种工具，才能获得良好的效果。
SQL Server 开发中的语言选择与数据库互操作性指南
6. 数据类型转换与使用示例
在实际开发中，根据数据类型兼容性图表进行数据类型转换是实现数据库互操作性的关键步骤。以下通过一个简单示例说明如何在不同数据库平台间进行数据类型转换。
假设我们有一个 SQL Server 数据库，其中有一个表
Employees
，包含以下列：
| 列名 | 数据类型 |
| — | — |
|
EmployeeID
|
int
|
|
Name
|
varchar(50)
|
|
BirthDate
|
datetime
|
现在要将这个表迁移到 MySQL 数据库，根据数据类型兼容性图表，我们需要进行如下转换：
| SQL Server 数据类型 | MySQL 数据类型 |
| — | — |
|
int
|
int
|
|
varchar(50)
|
varchar(50)
|
|
datetime
|
datetime
|
在 SQL Server 中创建表的语句如下：
CREATE TABLE Employees (
EmployeeID int PRIMARY KEY,
Name varchar(50),
BirthDate datetime
);
在 MySQL 中创建等效表的语句如下：
CREATE TABLE Employees (
EmployeeID int PRIMARY KEY,
Name varchar(50),
BirthDate datetime
);
这个示例展示了如何根据数据类型兼容性图表进行简单的数据类型转换，确保表结构在不同数据库平台间的一致性。
7. 标识符使用注意事项
不同数据库平台的标识符规则差异可能会导致在迁移数据库时出现问题。以下是一些使用标识符时的注意事项：
- 长度限制 ：在设计数据库对象名称时，要考虑不同平台的标识符长度限制。例如，Oracle 数据库的标识符长度限制为 30 字节，而 SQL Server 为 128 字符（临时表为 116 字符）。因此，避免使用过长的名称，以免在迁移时出现错误。
-
特殊字符
：不同平台允许的特殊字符不同。如 SQL Server 允许使用
@、#、$等符号，而 DB2 只允许使用下划线。在命名对象时，尽量使用各平台都支持的字符，避免使用特定平台的特殊符号。 - 大小写敏感性 ：有些数据库平台（如 MySQL）默认对标识符大小写不敏感，而有些平台（如 Oracle）则敏感。在编写代码时，要统一大小写规范，避免因大小写问题导致的错误。
8. 数据库互操作性的流程图
下面是一个 mermaid 格式的流程图，展示了设计具有互操作性数据库的主要步骤：
graph LR
classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px;
classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px;
classDef decision fill:#FFF6CC,stroke:#FFBC52,stroke-width:2px;
A([开始设计数据库]):::startend --> B(选择数据类型):::process
B --> C{数据类型是否可互操作?}:::decision
C -->|是| D(遵循标识符规则):::process
C -->|否| E(根据兼容性图表转换数据类型):::process
E --> D
D --> F{标识符是否符合所有平台规则?}:::decision
F -->|是| G([完成设计]):::startend
F -->|否| H(调整标识符):::process
H --> D
这个流程图清晰地展示了设计具有互操作性数据库的关键步骤，从数据类型选择到标识符规则遵循，确保数据库在不同平台间的兼容性。
9. 综合案例分析
为了更深入地理解 SQL Server 开发中的语言选择和数据库互操作性，我们来看一个综合案例。假设我们要开发一个企业级的员工管理系统，该系统需要在 SQL Server 和 MySQL 两个数据库平台上运行。
-
数据库设计 ：
-
首先，根据规范化原则设计数据库结构，创建
Employees、Departments等表。在选择数据类型时，参考数据类型兼容性图表，确保使用可互操作的数据类型。 -
例如，
Employees表的设计如下：
| 列名 | SQL Server 数据类型 | MySQL 数据类型 |
| — | — | — |
|EmployeeID|int|int|
|Name|varchar(50)|varchar(50)|
|DepartmentID|int|int|
|HireDate|datetime|datetime|
-
首先，根据规范化原则设计数据库结构，创建
-
代码编写 ：
- 对于数据访问逻辑，考虑使用存储过程。由于存储过程能封装数据库细节、提升性能和定义一致的安全接口，我们优先选择这种方式。
- 在编写存储过程时，对于简单的数据查询和操作，使用 T - SQL 实现。例如，在 SQL Server 中查询所有员工信息的存储过程如下：
CREATE PROCEDURE GetAllEmployees
AS
BEGIN
SELECT * FROM Employees;
END;
- 如果存储过程中包含复杂的逻辑，如大量的条件判断和循环操作，可考虑使用 .NET 实现。例如，计算员工绩效得分的存储过程，可使用 .NET 编写用户定义函数来实现复杂的计算逻辑。
-
数据库迁移
：
- 在将系统从 SQL Server 迁移到 MySQL 时，根据数据类型兼容性图表和标识符规则进行调整。确保数据库结构和代码在新平台上能够正常运行。
10. 总结与建议
通过前面的讨论，我们对 SQL Server 开发中的语言选择和数据库互操作性有了更深入的了解。以下是一些总结和建议：
-
语言选择
：
- 对于简单的数据访问和操作，优先使用 T - SQL，因为它是基于集合逻辑的最佳工具，并且 SQL Server 不断增强其功能。
- 当需要处理复杂逻辑、CPU 密集型任务或实现特殊功能时，考虑使用 .NET，如编写无数据访问的标量函数、用户定义聚合等。
-
数据库互操作性
：
- 在设计数据库时，从数据类型和标识符规则入手，确保数据库能在不同平台间顺利迁移。
- 参考数据类型兼容性图表和标识符规则表，避免因数据类型不兼容或标识符不符合规则导致的问题。
-
最佳实践
：
- 尽量使用存储过程作为数据库访问的主要方式，以提高性能、封装细节和增强安全性。
- 对于 CLR 的使用，要谨慎评估其适用性，确保在合适的场景下使用，以提升数据库性能和功能。
总之，在 SQL Server 开发中，要根据具体需求和场景，明智地选择语言和工具，同时注重数据库的互操作性，这样才能开发出高效、可维护且具有良好兼容性的数据库应用程序。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
