Microsoft Corporation
摘要本文是为希望将其应用程序转为 Microsoft SQL Server 应用程序的 Oracle 应用程序开发人员所写的文中讲述了成功地进行转换所需要的工具过程和技巧并突出强调了创建高性能高并发性 SQL Server 应用程序的基本设计原则
本文的目标读者应该具有
坚实的 Oracle RDBMS 基础知识背景
全面的数据库管理知识
熟悉 Oracle SQL 和 PL/SQL 语言
实际使用 C/C++ 编程语言的知识
sysadmin 固定服务器角色的成员身份
本文假定您熟悉与 Oracle RDBMS 有关的术语概念和工具有关 Oracle RDBMS 及其体系结构的详细信息请参见 Oracle Server Concepts Manual（Oracle Server概念手册）至于使用 Oracle 脚本和示例还假定您熟悉 Oracle Server Manager 和 Oracle SQL*Plus 工具有关这些工具的详细信息请参见 Oracle 文档
目录
开发和应用程序平台
概述
本文组织结构
体系结构和术语
安装和配置 Microsoft SQL Server
定义数据库对象
实施数据完整性和业务规则
事务锁定和并发性
死锁
SQL 语言支持
游标的实现
优化 SQL 语句
使用 ODBC
开发和管理数据库复制
迁移数据和应用程序
数据库示例
开发和应用程序平台
为了清楚和便于表述假定开发和应用程序平台是 Microsoft Visual Studio 版Microsoft Windows NT (Service Pack )SQL Server 和 Oracle Oracle 使用 Visigenic Software ODBC 驱动程序（ 版）SQL Server 使用 Microsoft Corporation ODBC 驱动程序（ 版）Microsoft SQL Server 包括用于 Oracle 的 OLE DB 驱动程序但在本章中不予详细讨论
概述
应用程序迁移过程似乎很复杂两种 RDBMS 之间有很多体系结构方面的差异描述 Oracle 体系结构的词汇和术语在 Microsoft SQL Server 中其含义常常完全不同此外Oracle 和 SQL Server 都有许多专有的 SQL 标准扩展
从应用程序开发人员的角度来看Oracle 和 SQL Server 管理数据的方式是相似的但是Oracle 和 SQL Server 之间内部的差异是相当大的如果管理得当它对迁移应用程序造成的影响就会微乎其微
开发人员面临的最严峻迁移问题是SQL SQL 语言标准的实现和每种 RDBMS 提供的扩展一些开发人员只使用标准的 SQL 语言语句并倾向于使其程序代码尽可能通用通常这意味着把程序代码限定在初级 SQL 标准该标准在许多数据库产品中均得到了一致的实现其中包括 Oracle 和 SQL Server
这种方法可能给程序代码带来不必要的复杂性并显着影响程序性能例如Oracle 的 DECODE 函数是 Oracle 特有的非标准 SQL 扩展Microsoft SQL Server 的 CASE 表达式已不止是初级 SQL 的扩展并未在所有的数据库产品上实现
如果不使用这两个函数则可以编程方式实现其功能但可能需要从 RDBMS 检索更多的数据
此外SQL 语言的过程扩展也可能带来困难Oracle PL/SQL 和 SQL Server TransactSQL 语言功能相似但语法不同各 RDBMS 及其过程扩展之间不存在精确的对等关系因此您可能会放弃使用存储程序例如过程和触发器这是令人遗憾的因为这些程序能够提供极好的性能和安全性而这些用任何其它方式均无法实现
使用专用的开发接口也会带来其它的问题使用 Oracle OCI（Oracle 调用接口）转换程序通常需要大量的资源投入当开发的应用程序可能使用多个 RDBMS 时应考虑使用开放式数据库连接 (ODBC) 接口
ODBC 是专为使用多种数据库管理系统而设计的ODBC 提供一致的应用程序编程接口 (API)它通过数据库特有驱动程序的服务与不同的数据库一同工作
一致的 API 是指不论程序与 Oracle 还是与 SQL Server 交互它在建立连接执行命令和检索结果时所调用的函数是相同的
ODBC 还定义了一个标准调用级接口并使用标准转义序列指定执行公用任务的 SQL 函数但该函数在不同的数据库中语法不同不需要修改任何程序代码ODBC 驱动程序就可以自动地把 ODBC 语法转换成原本的 Oracle 或 Microsoft SQL Server SQL 语法在某些情况中最好的方法是编写一个程序使 ODBC 在运行时进行转换
ODBC 并不是一个神奇的解决方案不能对所有的数据库均实现完全的数据库独立性完备的功能以及较高的性能不同的数据库和第三方厂商提供不同级别的 ODBC 支持一些驱动程序只实现了映射在其它接口库顶层的核心 API 函数其它驱动程序例如 Microsoft SQL Server 驱动程序在原本的高性能的驱动程序中提供全面的级别 支持
如果程序只使用核心 ODBC API它可能放弃了一些数据库带有的功能和性能再者并不是所有原本的 SQL 扩展都可以用 ODBC 转义序列表示例如 Oracle DECODE 和 SQL Server CASE 表达式就是这样
此外通过编写 SQL 语句使用数据库优化程序也是通常的做法在 Oracle 中用来提高性能的技巧和方法在 SQL Server 中并不一定最好ODBC 接口无法将技巧从一个 RDBMS 转化到另一个 RDBMS 中
ODBC 并不禁止应用程序使用数据库特有的功能也不禁止优化性能但是应用程序需要一些数据库特有的代码部分有了 ODBC要使程序结构和绝大部分程序代码在多个数据库上保持一致就变得十分简单
OLE DB 是下一代的数据访问技术Microsoft SQL Server 利用了 SQL Server 自身组件中的 OLE DB此外应用程序开发人员在 SQL Server 新的开发过程中应考虑使用 OLE DBMicrosoft 在 SQL Server 中加入了用于 Oracle 的 OLE DB 提供程序
OLE DB 是 Microsoft 的一个战略性系统级编程接口用于管理整个组织内的数据OLE DB 是建立在 ODBC 功能之上的一个开放规范ODBC 是为访问关系型数据库而专门开发的OLE DB 则用于访问关系型和非关系型信息源例如主机 ISAM/VSAM 和层次数据库电子邮件和文件系统存储文本图形和地理数据以及自定义业务对象
OLE DB 定义了一组 COM 接口对各种数据库管理系统服务进行封装并允许创建软件组件实现这些服务OLE DB 组件包括数据提供程序（包含和表现数据）数据使用者（使用数据）和服务组件（处理和传送数据例如查询处理器和游标引擎）
OLE DB 接口有助于平滑地集成组件这样OLE DB 组件厂商就可以快速地向市场提供高质量 OLE DB 组件此外OLE DB 包含了一个连接 ODBC 的桥梁对现用的各种 ODBC 关系型数据库驱动程序提供一贯的支持
本文组织结构
为了帮助您实现从 Oracle 向 SQL Server 的逐步迁移每节都包括一个 Oracle 和 Microsoft SQL 之间相关差异的概述此外还包括转换时要考虑的因素SQL Server 的优势以及多个示例
体系结构和术语
要成功地迁移开始之前应该了解与 Microsoft SQL Server 有关的基础体系结构和术语本节中的许多例子均取自 Oracle 和 SQL Server 应用程序示例（附在文中）
在 Oracle 中数据库指整个 Oracle RDBMS 环境并包括以下组件
Oracle 数据库进程和缓沖区（实例）
包含一个集中系统编录的 SYSTEM 表空间
其它由 DBA 定义的表空间（可选）
两个或多个在线重做日志
存档的重做日志（可选）
各种其它文件（控制文件Initora 等等）
Microsoft SQL Server 数据库从逻辑上将数据应用程序和安全机制分离这一点与表空间非常相似Oracle 支持多个表空间SQL Server 则支持多个数据库表空间还可用于支持数据的物理存放SQL Server 使用文件组提供相同的功能
Microsoft SQL Server 还默认安装下列数据库
model 数据库是所有新创建的用户数据库的模板
tempdb 数据库与 Oracle 临时表空间相似它用于临时工作存储和排序操作与 Oracle 临时表空间不同的是用户可以创建临时表并在用户注销时自动删除
msdb 支持 SQL Server 代理及其计划的作业警报和复制信息
pubs 和 Northwind 数据库作为培训示例数据库提供
有关默认数据库的详细信息请参见 SQL Server Books Online
每个 Oracle 数据库均在一个集中系统编录或数据字典上运行它驻留在 SYSTEM 表空间中每个 Microsoft SQL Server 数据库均维护其自身的系统编录它包含下列信息
数据库对象（表索引存储过程视图触发器等等）
约束
用户和权限
用户定义的数据类型
复制定义
数据库使用的文件
在 master 数据库中SQL Server 还加入了一个集中系统编录它包括系统编录以及有关每个数据库的一些信息
数据库名称和每个数据库的主文件位置
SQL Server 登录帐户
系统消息
数据库配置值
远程和/或链接的服务器
当前活动信息
系统存储过程
与 Oracle 中的 SYSTEM 表空间一样要访问任何其它数据库SQL Server master 数据库必须可用因此当 master 数据库做重大修改后应对该数据库进行备份以防止数据库出现故障这一点非常重要数据库管理员也可以镜像构成 master 数据库的文件
Oracle RDBMS 由表空间组成而表空间又是由数据文件组成的表空间数据文件被格式化为称为块的内部单元块的大小是 DBA 在 Oracle 数据库首次创建时设定的其范围从 到 字节在 Oracle 表空间中创建一个对象时用户用称为扩展盘区的单位定义其大小（初始扩展盘区下一扩展盘区最小扩展盘区和最大扩展盘区）Oracle 扩展盘区大小是可变的但必须包括至少五个连续的块
在数据库一级中Microsoft SQL Server 使用文件组来控制表和索引的物理存储文件组是一个或多个文件的逻辑容器文件组中包含的数据按比例填充到所有属于该组的文件中
如果没有定义和使用文件组数据库对象就会被放在一个默认文件组中该文件组是数据库创建过程中隐式定义的文件组允许
把大型表分布在多个文件上以提高 I/O 吞吐量
把索引存储在不同的文件上而不是它们各自的表上从而进一步提高了 I/O 吞吐量和磁盘并发性
将 textntextimage 列（大对象）从表中存储到不同的文件上
把数据库对象放在特定的磁盘上
备份和恢复文件组中单个表或一组表
SQL Server 将文件格式化为称为页的内部单元页大小是固定的为 字节 ( KB)扩展盘区由页组成其大小也是固定的由 个连续的页组成在 SQL Server 数据库中创建表或索引时会自动给其分配一个页与分配一个整个扩展盘区相比它可更有效地存储较小的表和索引
对于大多数 Microsoft SQL Server 安装来说不需要 Oracle 类型的段相反SQL Server 可以使用基于硬件的 RAID 或基于 Windows NT 软件的 RAID更好地分布数据或将数据条带化基于 Windows NT 软件的 RAID 或基于硬件的 RAID 可以设定条带集它包括多个磁盘驱动器看起来就像一个逻辑驱动器一样如果数据库文件在此条带集上创建磁盘子系统就负责把 I/O 负载分布到多个磁盘上建议管理员使用 RAID把数据分布到多个物理磁盘上
SQL Server 推荐的 RAID 配置是 RAID （镜像）或 RAID （带有一个额外的奇偶校验驱动器的条带集用作冗余）也建议使用 RAID （带有奇偶校验的条带集的镜像）但是它比前两种配置昂贵得多条带集非常适于分布数据库文件上常常随机产生的 I/O
如果不能选择 RAID文件组则是一个有吸引力的替代选择它提供与 RAID 相同的一些优点此外对于可能跨越多个物理 RAID 阵列的大型数据库文件组是一个很吸引人的方法它以一种可控的方式将 I/O 进一步分布到多个 RAID 阵列上
对于有序 I/O必须优化事务日志文件并加以保存防止单点失败因此对于事务日志建议使用 RAID （镜像）这个驱动器的大小至少要和联机重做日志和回滚段表空间的总计大小一样应创建一个或多个日志文件来占用该逻辑驱动器上定义的所有空间与存储在文件组中的数据不同事务日志项目总是按顺序地写入并且不是按比例填充的
有关 RAID 的详细信息请参见 SQL Server Books OnlineWindows NT Server 文档和 Microsoft Windows NT 资源工具包
每次启动时Oracle RDBMS 执行自动恢复它检验表空间文件的内容是否与联机重做日志文件一致如果不一致Oracle 将联机重做日志文件内容应用到表空间文件（前滚）并删除回滚段中发现的任何未提交的事务（回滚）如果 Oracle 不能从联机重做日志文件中得到它所需要的信息它就会查询存档重做日志文件
每次启动时Microsoft SQL Server 还通过检查系统中的每个数据库进行自动数据恢复它首先检查 master 数据库然后启动恢复系统中所有其它数据库的线程对于每个 SQL Server 数据库自动恢复机制均检查事务日志如果事务日志包含任何未提交的事务该事务被回滚然后恢复机制在事务日志中查找已提交但还未写到数据库的事务如果找到再次执行这些事务前滚
每个 SQL Server 事务日志均有 Oracle 回滚段与 Oracle 联机重做日志的组合功能每个数据库都有自已的事务日志它记录了对数据库所作的全部更改并且由数据库的所有用户共享当一个事务开始且发生数据修改时就会在日志中记录一个 BEGIN TRANSACTION 事件（以及修改事件）在自动故障恢复过程中这个事件用于确定事务的起始点在收到每个数据修改语句时先将更改写入事务日志然后再写入数据库有关详细信息请参见本章后面的事务锁定和并发性一节
SQL Server 有一个自动检查点机制确保完成的事务被定期地从 SQL Server 磁盘缓存写入事务日志文件检查点功能将自上一个检查点之后修改过的任何已被缓存的页面写入数据库在数据库上对这些被缓存过的页面（称为髒页）标出检查点以确保所有完成的事务均被写到磁盘中这个过程缩短了从系统故障（如停电）进行恢复所用的时间通过使用 SQL Server Enterprise Manager 或 TransactSQL（sp_configure 系统存储过程）修改恢复间隔设置可对此设置进行修改
Microsoft SQL Server 给备份数据提供了以下几个选项
完全数据库备份
要进行完全数据库备份请使用 BACKUP DATABASE 语句或备份向导
差异备份
当完成完全数据库备份后使用 BACKUP DATABASE WITH DIFFERENTIAL 语句或备份向导只定期备份更改的数据和索引页
事务日志备份
Microsoft SQL Server 中的事务日志与各自数据库关联在备份或被截断之前事务日志都是不断填充的SQL Server 的默认配置是事务日志自动增长直到用尽了所有磁盘空间或达到最大配置尺寸为止当事务日志变得太满时它就会产生一个错误并且在备份或截断之前禁止对数据进一步修改其它数据库不受影响可以使用 BACKUP LOG 或备份向导备份事务日志
文件或文件组备份
SQL Server 可以备份文件或文件组有关详细信息请参见 SQL Server Books Online
可以在数据库使用过程中对它进行备份这样就可以对必须连续运行的系统进行备份SQL Server 的备份处理和内部数据结构已进行了改进这样可将备份的数据传输率提高到最大同时对事务吞吐量的影响降至最小
Oracle 和 SQL Server 均需要特定的日志文件格式在 SQL Server 中这些文件称为备份设备它们是使用 SQL Server Enterprise ManagerTransactSQL sp_addumpdevice 存储过程或相应的 SQLDMO 命令创建的
尽管可以手动进行备份但是建议使用 SQL Server Enterprise Manager 和/或 Database Maintenance Plan Wizard 计划定期备份或基于数据库活动的备份
通过在完全数据库备份（设备）中应用事务日志备份和/或差异备份可以将数据库恢复到某个时点数据库恢复使用备份中包含的信息来覆盖数据可以使用 SQL Server Enterprise ManagerTransactSQL (RESTORE DATABASE) 或 SQLDMO 进行恢复
正如可以关闭 Oracle 归档文件来覆盖自动备份一样在 Microsoft SQL Server 中db_owner 固定数据库角色的成员可以在每次出现检查点时强制事务日志清除其内容这一操作可以使用 SQL Server Enterprise Manager（在检查点处截断日志）TransactSQL（sp_dboption 存储过程）或 SQLDMO 来完成
Oracle SQL*Net 支持 Oracle 数据库服务器及其客户之间的网络连接它使用透明网络底层 (TNS) 数据流协议进行通信并允许用户运行多个不同的网络协议而不必编写专用的代码核心 Oracle 数据库软件产品并不包括 SQL*Net
有了 Microsoft SQL ServerNetLibraries（网络库）通过使用表格格式数据流 (TDS) 协议支持客户和服务器之间的网络连接它们允许同时连接运行命名管道TCP/IP 套接字或其它进程间通信 (IPC) 机制的客户SQL Server CDROM 包括所有的客户 NetLibraries因此不需要再另行购买
SQL Server NetLibrary 选项可在安装后进行更改客户网络实用工具为运行 Windows NTWindows 或 Windows 操作系统的客户配置默认的 NetLibrary 和服务器连接信息除非在 ODBC 数据源配置过程中更改或在 ODBC 连接字符串中明确写明所有的 ODBC 客户应用程序均使用相同的默认 NetLibrary 和服务器连接信息有关 NetLibraries 的详细信息请参见 SQL Server Books Online
要将 Oracle 应用程序完全迁移到 Microsoft SQL Server 必须了解 SQL Server 数据库安全性和角色的实现
登录帐户
登录帐户允许用户访问 SQL Server 数据或管理选项登录帐户只允许用户登录到 SQL Server并查看允许 guest（来宾）访问的数据库（guest 帐户不是默认建立的必须单独创建）
SQL Server 提供两种类型的登录安全性Windows NT 身份验证模式（也称为集成模式）和 SQL Server 身份验证模式（也称为标准模式）SQL Server 也支持标准和集成安全的组合称为混合模式
验证登录连接时Windows NT 身份验证模式使用 Windows NT 内的安全机制并且依赖用户的 Windows NT 安全凭据用户不需要输入 SQL Server 的登录 ID 或密码 他们的登录信息直接从网络连接中获取此时一个条目被写入 syslogin 表并在 Windows NT 和 SQL Server 之间进行验证这称为一个信任连接就像两个 Windows NT 服务器之间的信任关系一样它与 Oracle 用户帐户相关的 IDENTIFIED EXTERNALLY 选项作用相似
SQL Server 身份验证模式要求用户在请求访问 SQL Server 时输入登录 ID 和密码这称为非信任连接它与 Oracle 用户帐户相关的 IDENTIFIED BY PASSWORD 选项作用类似使用标准安全模型登录过程只提供对 SQL Server 数据库引擎的访问而不提供对用户数据库的访问
有关这些安全机制的详细信息请参见 SQL Server Books Online
组角色和权限
Microsoft SQL Server 和 Oracle 均使用权限来实施数据库安全性SQL Server 语句级权限用于限制创建新的数据库对象（类似于 Oracle 系统级权限）
SQL Server 还提供对象级权限与 Oracle 一样对象级所有权被授予对象的创建者并且不能被转让在其他数据库用户访问对象前必须给他们授予对象级权限sysadmin 固定服务器角色db_owner 固定数据库角色或 db_securityadmin 固定数据库角色的成员也可以将一个用户对象上的权限授予其他用户
可以将 SQL Server 语句级和对象级权限直接授予数据库用户帐户而管理数据库角色的权限通常要简单得多SQL Server 角色用于授予或撤销一组数据库用户的权限（与 Oracle 角色非常相似）角色是与特定数据库相关的数据库对象对于每种安装均有相关的专有固定服务器角色可用于整个数据库固定服务器角色的一个例子是 sysadmin当 SQL Server 登录时可以添增 Windows NT 组或数据库用户可以给 Windows NT 组或 Windows NT 用户授予权限
数据库可以有任意数量的角色或 Windows NT 组在每个数据库中均可找到默认角色 public并且该角色不能被删除public 角色和 Oracle 中 PUBLIC 帐户的作用相似每个数据库用户始终是 public 角色的一个成员除了 public 角色之外数据库用户还可以是任何数量角色的成员Windows NT 用户和组也可以是任何数量角色的成员并且始终是 public 角色的成员
数据库用户和 guest 帐户
在 Microsoft SQL Server 中要使用数据库及其对象用户登录帐户必须被授权登录帐户可以使用下列方法访问数据库
登录帐户可被指定为数据库用户
登录帐户可使用数据库中的 guest 帐户
可以将 Windows NT 组登录映射为一个数据库角色然后作为该组成员的各 Windows NT 帐户可以连接到该数据库
db_ownerdb_accessadmin 角色或 sysadmin 固定服务器角色的成员创建数据库用户帐户角色帐户可以包括以下几个参数SQL Server 登录 ID数据库用户名（可选）和最多一个角色名（可选）数据库用户名不需和用户的登录 ID 相同如果没有提供数据库用户名则用户的登录 ID 和数据库用户名是相同的如果没有提供角色名则数据库用户只是 public 角色的成员创建数据库用户之后可根据需要赋予该用户相应的角色
db_owner 或 db_accessadmin 角色的成员还可以创建 guest 帐户guest 帐户允许任何有效的 SQL Server 登录帐户访问数据库即便没有数据库用户帐户也可以默认情况下guest 帐户继承授予 public 角色的任何权限但是这些权限可以更改使其高于或低于 public 角色的权限
与 SQL Server 登录一样Windows NT 用户帐户或组帐户可被授权访问数据库当作为组成员的 Windows NT 用户连接到该数据库时此用户就获得授予 Windows NT 组的权限如果他是多个 Windows NT 组（已授权访问数据库）的成员则该用户可收到所有这些组的组合权限
sysadmin 角色
Microsoft SQL Server sysadmin 固定服务器角色成员的权限与 Oracle DBA 的权限相似在 SQL Server 中默认情况下sa SQL Server 身份验证模式登录帐户是该角色的成员这就如同当 SQL Server 安装在 Windows NT 计算机上它就是本地 Administrators 组成员一样 sysadmin 角色的成员可以添加或删除 Windows NT 用户和组以及 SQL Server 登录该角色的成员通常有下列职责
安装 SQL Server
配置服务器和客户
创建数据库*
设置登录权限和用户权限*
向 SQL Server 数据库导入数据和从中导出数据*
备份和恢复数据库*
实现和维护复制
计划无值守操作*
监视和优化 SQL Server 性能*
分析系统问题
*这些项目可以委派给其他安全角色或用户
在 SQL Server 中没有对 sysadmin 固定服务器角色成员的权限进行限制因此该角色的成员可以访问 SQL Server 特定实例上的任何数据库及其所有对象（包括数据）与 Oracle DBA 一样有一些命令和系统过程只有 sysadmin 角色的成员可以使用
db_owner 角色
尽管在使用上Microsoft SQL Server 数据库与 Oracle 表空间类似但各个的管理方式不同每个 SQL Server 数据库都是一个自包含的管理域每个数据库均被指派一个数据库所有者 (dbo)该用户始终是 db_owner 固定数据库角色的一个成员其他用户也可以是 db_owner 角色的成员作为该角色成员的任何用户都有能力管理与其数据库有关的管理任务（Oracle 则不同一个 DBA 可管理所有表空间的管理任务）这些任务包括
管理数据库访问
更改数据库选项（只读单用户等等）
备份和恢复数据库内容
授予和撤销数据库权限
创建和删除数据库对象
db_owner 角色的成员在其数据库中具有所有权限授予该角色的大多数权限可以分给几个固定数据库角色或被授予数据库用户要在数据库中拥有 db_owner 权限不需要有服务器范围内的 sysadmin 权限
安装和配置 Microsoft SQL Server
搞清了 Oracle 和 SQL Server 之间基本的结构差异之后就可以开始进行迁移过程的第一步应使用 SQL Server 查询分析器运行以下脚本
使用基于 Windows NT 软件的 RAID 或基于硬件的 RAID 创建一个可容纳所有数据的逻辑驱动器通过计算 Oracle 系统临时表空间和应用程序表空间所使用的全部文件空间来预估空间大小
使用基于 Windows NT 软件的 RAID 或基于硬件的 RAID 来创建用于存放事务日志的第二个逻辑驱动器此驱动器大小应至少和联机重做与回滚段表空间之和一样大
使用 SQL Server Enterprise Manager创建一个与 Oracle 应用程序表空间名称相同的数据库（示例应用程序使用的数据库名称为 USER_DB）将数据和事务日志的文件位置分别指定为步骤 和 创建的磁盘如果使用多个 Oracle 表空间不必甚至不建议创建多个 SQL Server 数据库RAID 会为您分布数据
创建 SQL Server 登录帐户
USE MASTER
EXEC SP_ADDLOGIN STUDENT_ADMIN STUDENT_ADMIN
EXEC SP_ADDLOGIN DEPT_ADMIN DEPT_ADMIN
EXEC SP_ADDLOGIN ENDUSER ENDUSER
GO
向数据库中添加角色
USE USER_DB
EXEC SP_ADDROLE DATA_ADMIN
EXEC SP_ADDROLE USER_LOGON
GO
给角色授予权限
GRANT CREATE TABLE CREATE TRIGGER CREATE VIEW
CREATE PROCEDURE TO DATA_ADMIN
GO
把登录帐户添加为数据库用户帐户
EXEC SP_ADDUSER ENDUSER ENDUSER USER_LOGON
EXEC SP_ADDUSER DEPT_ADMIN DEPT_ADMIN DATA_ADMIN
EXEC SP_ADDUSER STUDENT_ADMIN STUDENT_ADMIN DATA_ADMIN
GO
此插图给出了此步骤完成后的 SQL Server 和 Oracle 环境
定义数据库对象
Oracle 数据库对象（表视图和索引）可以很方便地迁移到 Microsoft SQL Server因为每种 RDBMS 都严格遵循 SQL 标准该标准是一个关于对象定义的标准将 Oracle SQL 表索引和视图定义转换为 SQL Server 表索引和视图定义只需要进行相对简单的语法更改即可下表着重阐述了Oracle 和 Microsoft SQL Server 数据库对象之间的一些差异 类别
Microsoft SQL Server
Oracle
列数
行大小
字节加 字节指向每个 text 或 image 列
没有限制（但每行只允许一个 long 或 long raw）
最大行数
没有限制
没有限制
BLOB 类型存储
和行一起存储的 字节指针数据存储在其它数据页上
每表一个 long 或 long raw必须在行尾数据存储在与行相同的块上
聚集的表索引
每表一个
每表一个（索引组织的表）
非聚集的表索引
每表 个
没有限制
单索引中索引的最大列数
索引中列值的最大长度
字节
/ 块
表命名规则
[[[server]database]owner]
table_name
[schema]table_name
视图命名规则
[[[server]database]owner]
table_name
[schema]table_name
索引命名规则
[[[server]database]owner]
table_name
[schema]table_name
假定您从用来创建数据库对象的 Oracle SQL 脚本或程序入手只要复制这个脚本或程序并进行下列修改即可每个更改均在本节的其它部分进行了讨论该例取自脚本示例程序脚本 Oratablesql 和 Sstablesql
确保数据库对象标识符符合 Microsoft SQL Server 命名规则可能只需要更改索引名称
修改数据存储参数使之用于 SQL Server如果使用 RAID则不需要存储参数
修改 Oracle 约束定义使之用于 SQL如有必要则创建触发器以支持外键 DELETE CASCADE 语句如果表跨几个数据库则使用触发器强制外键关系
修改 CREATE INDEX 语句以使用聚集索引
使用数据转换服务创建新的 CREATE TABLE 语句检查该语句注意 Oracle 数据类型与 SQL Server 数据类型是如何对应的
删除所有 CREATE SEQUENCE 语句在 CREATE TABLE 或 ALTER TABLE 语句中使用标识符列替代序列的使用
如有必要修改 CREATE VIEW 语句
删除任何对同义词的引用
评估 Microsoft SQL Server 临时表的使用及其在应用程序中的用途
把 Oracle 的所有 CREATE TABLEUAS SELECT 命令改成 SQL Server 的 SELECTUINTO 语句
评估用户定义的规则数据类型和默认值的潜在用途
下面图表比较了Oracle 和 Microsoft SQL Server 处理对象标识符的方式在大多数情况下向 SQL Server 迁移时不需要更改对象名称 Oracle
Microsoft SQL Server
个字符长
数据库名称最多 个字符长
数据库链接名称最多 个字符长
个 Unicode 字符长
临时表名称最多 个字符长
标识符名称必须以字母开头并包含字母数字字符或 _$和 # 字符
标识符名称可以以字母数字字符或 _ 开头并且几乎可包含任何字符
如果标识符以空格开始并包含除 _@# 或 $ 以外的字符则必须使用 []（分隔符）将标识符名称括起来
如果对象开始字符是
@ 它是一个局部变量
# 它是一个局部临时对象
## 它是一个全局临时对象
表空间名称必须唯一
数据库名称必须唯一
在用户帐户（架构）中标识符名称必须是唯一的
在数据库用户帐户中标识符名称必须是唯一的
在表或视图中列名必须是唯一的
在表或视图中列名必须是唯一的
在用户架构中索引名称必须是唯一的
在数据库表名称中索引名称必须是唯一的
当访问 Oracle 用户帐户中的表时仅按其不合格的名称来选定它访问其它 Oracle 架构中的表时在表名称前加上架构名称和一个英文句点 ()Oracle 同义词可提供其它的位置透明性
当 Microsoft SQL Server 引用表时使用了另一套命名规则因为 SQL Server 登录帐户可以在多个数据库中使用同一名称创建表所以可使用下列规则访问表和视图[[database_name]owner_name]table_name 访问以下项中的表
Oracle
Microsoft SQL Server
用户帐户
SELECT *
FROM STUDENT
SELECT * FROM USER_DBSTUDENT_
ADMINSTUDENT
其它架构
SELECT * FROM STUDENT_ADMINSTUDENT
SELECT * FROM OTHER_DBSTUDENT_
ADMINSTUDENT
以下是命名 Microsoft SQL Server 表和视图的指导原则
使用数据库名和用户名是可选的当只按名称来引用表时（例如STUDENT）SQL Server 在当前数据库的当前用户帐户中查找该表如果没有找到它就会在该数据库中查找保留用户名 dbo 拥有的相同名称的一个对象在数据库的用户帐户中表名称必须唯一
一个 SQL Server 登录帐户可在多个数据库中拥有名称相同的表例如ENDUSER 帐户拥有下列数据库对象USER_DBENDUSERSTUDENT 和 OTHER_DBENDUSERSTUDENT限定符是数据库用户名而不是 SQL Server 登录名因为它们并不一定相同
同时这些数据库中的其他用户可以拥有相同名称的对象
USER_DBDBOSTUDENT
USER_DBDEPT_ADMINSTUDENT
USER_DBSTUDENT_ADMINSTUDENT
OTHER_DBDBOSTUDENT
因此建议把所有者名称作为数据库对象引用的一部分如果应用程序有多个数据库建议把数据库名称也作为引用的一部分如果查询跨多个服务器也将服务器名称加到引用中
每个 SQL Server 连接都有一个当前的数据库上下文它是在登录时使用 USE 语句设定的例如假定下列场景
一个用户使用 ENDUSER 帐户登录到 USER_DB 数据库用户请求 STUDENT 表SQL Server 查找 ENDUSERSTUDENT 表如果找到该表则 SQL Server 在 USER_DBENDUSERSTUDENT 上执行请求的数据库操作如果在 ENDUSER 数据库帐户中没有找到该表SQL Server 则在此数据库的 dbo 帐户中查找 USER_DBDBOSTUDENT如果该表仍没有找到SQL Server 就会返回一个错误信息指出该表不存在
如果另一个用户例如 DEPT_ADMIN拥有这个表表名称前面一定加上数据库用户的名称（ DEPT_ADMINSTUDENT）否则数据库名称默认为当前在上下文中的数据库
如果引用的表在另一个数据库中该数据库名称必须用作引用的一部分例如在 OTHERDB 数据库中要访问 ENDUSER 拥有的 STUDENT 表时就要使用 OTHER_DBENDUSERSTUDENT
可用两个英文句点将数据库和表的名称分隔开省略对象的所有者名称例如如果应用程序引用 STUDENT_DBSTUDENTSQL Server 进行如下查询
STUDENT_DBcurrent_userSTUDENT
STUDENT_DBDBOSTUDENT
如果用户一次只使用一个数据库在对象的引用中省略数据库名称这样在其它数据库中使用该应用程序就变得简单了所有对象引用隐式访问当前使用的数据库如果在同一服务器上要维护一个测试数据库和一个生产数据库这是很有用的
因为 Oracle 和 SQL Server 均支持标识 RDBMS 对象的 SQL 初级规则所以CREATE TABLE 语法是相似的 Oracle
Microsoft SQL Server
CREATE TABLE
[schema]table_name
(
{col_name column_properties
[default_expression] [constraint [constraint
[nstraint]]]| [[] constraint]}
[[] {next_col_name | next_constraint}]
)
[Oracle Specific Data Storage Parameters]
CREATE TABLE [server][database][owner] table_name
(
{col_name column_properties[constraint
[constraint [nstraint]]]| [[] constraint]}
[[] {next_col_name | next_constraint}]
)
[ON filegroup_name]
Oracle 数据库对象名称不区分大小写在 Microsoft SQL Server 中取决所选的安装选项数据库对象名可以是区分大小写的
SQL Server 第一次安装时默认的排序次序是字典顺序不区分大小写（可以使用 SQL Server 安装程序设定不同的配置）因为 Oracle 对象名称始终是唯一的所以把数据库对象迁移到 SQL Server不应有任何问题建议在 Oracle 和 SQL Server 中所有的表和列名都使用大写以避免用户在区分大小写的 SQL Server 上安装时出现问题
有了 Microsoft SQL Server使用 RAID 通常可简化数据库对象的存放与 Oracle 索引组织的表一样SQL Server 聚集索引被集成到表的结构中 Oracle
Microsoft SQL Server
CREATE TABLE DEPT_ADMINDEPT (
DEPT VARCHAR() NOT NULL
DNAME VARCHAR() NOT NULL
CONSTRAINT DEPT_DEPT_PK
PRIMARY KEY (DEPT)
USING INDEX TABLESPACE USER_DATA
PCTFREE STORAGE (INITIAL K NEXT K
MINEXTENTS MAXEXTENTS UNLIMITED)
CONSTRAINT DEPT_DNAME_UNIQUE
UNIQUE (DNAME)
USING INDEX TABLESPACE USER_DATA
PCTFREE STORAGE (INITIAL K NEXT K
MINEXTENTS MAXEXTENTS UNLIMITED)
)
PCTFREE PCTUSED
TABLESPACE USER_DATA
STORAGE (INITIAL K NEXT K
MINEXTENTS MAXEXTENTS UNLIMITED
FREELISTS )
CREATE TABLE USER_DBDEPT_ADMINDEPT (
DEPT VARCHAR() NOT NULL
DNAME VARCHAR() NOT NULL
CONSTRAINT DEPT_DEPT_PK
PRIMARY KEY CLUSTERED (DEPT)
CONSTRAINT DEPT_DNAME_UNIQUE
UNIQUE NONCLUSTERED (DNAME)
)
