The term “vendor dependency” often evokes apprehension among business leaders. However, it’s widely acknowledged within the tech industry that absolute “vendor independence” is unattainable, particularly in the realm of databases.
Two dominant players in the enterprise Relational Database Management System (RDBMS) market are Oracle Database and Microsoft SQL Server (referred to as “Oracle” and “SQL Server” hereafter). While IBM Db2 maintains a presence alongside Oracle on the shrinking yet crucial mainframe systems, and open-source options like PostgreSQL gain traction in dynamic environments, the choice for many businesses often boils down to Oracle vs. SQL Server when adopting a new RDBMS. This decision hinges on various factors: licensing costs, internal expertise, existing infrastructure compatibility, vendor relationships, and future strategies. However, even with meticulous planning, circumstances can shift, necessitating a platform change - a process I’ve personally navigated through multiple migrations and feasibility assessments.
Both Oracle and SQL Server, rooted in tradition, partially adhere to the ANSI SQL standard. Beyond procedural extensions (PL/SQL for Oracle, Transact-SQL for SQL Server), which are syntactically distinct yet translatable, SQL code can appear deceptively alike - a potential pitfall.
Two pivotal aspects in any Oracle-to-SQL Server or vice versa migration are transactions and the closely intertwined temporary tables, crucial for managing transaction scope. We’ll also delve into nested transactions (transactions within transactions) due to their role in Oracle’s user security auditing, which SQL Server handles differently because of its COMMIT behavior.
Transaction Structure: A High-Level Comparison of Oracle and SQL Server
Oracle transactions are implicit, meaning you’re always within a transaction until an explicit commit or rollback. While you can manually initiate transactions, define rollback points, and nest transactions, the key takeaway is the ever-present transaction context requiring an explicit termination. DDL statements also trigger implicit commits.
Conversely, SQL Server employs explicit transactions. Without explicitly starting one, changes are committed immediately upon statement execution, as each DML statement effectively creates and commits its own transaction unless an error occurs.
This difference stems from how data storage and retrieval are implemented.
Oracle DML statements modify records directly within data files, writing the old record version to the rollback file and timestamping the change. SELECT statements operate on data modified up to that point, retrieving older versions from the rollback file if needed. This mechanism ensures read consistency and concurrent read/write access. However, it can lead to the infamous ORA-01555 error (“snapshot too old”) for long-running queries on highly active databases, signifying an insufficient rollback file size for the required older record version has already been reused. This is why Oracle transactions should be kept as short as necessary.
SQL Server interacts directly with data files for both reading and writing. Each SQL statement implicitly forms a transaction unless grouped within an explicit transaction block, facilitating rollback capability.
Each transaction locks required resources. While modern SQL Server versions optimize locking granularity, the required resources are determined by the SQL code itself, making query optimization crucial. Unlike Oracle, SQL Server transactions should be minimized, hence the default automatic commit behavior.
This difference in transaction implementation significantly impacts temporary tables.
Temporary Tables in Oracle and SQL Server
The ANSI SQL standard, while defining local and global temporary tables, doesn’t mandate specific implementations. Both Oracle and SQL Server offer global temporary tables, while SQL Server additionally provides local ones. Oracle 18c introduced “private temporary tables,” functionally similar to local temporary tables, simplifying SQL Server code migration compared to earlier versions and addressing Oracle’s lack of features earlier addition of a few related features like auto-incrementing identity columns.
However, private temporary tables in Oracle can be a double-edged sword. While easing SQL Server migration, they introduce new complexities. For instance, design-time code validation becomes impossible with private temporary tables, increasing error susceptibility. Debugging them mirrors the challenges of dynamic SQL without the unique use-case justification, hence their late introduction in Oracle 18c.
In my view, any use case for Oracle’s private temporary tables can be achieved equally well, if not better, using global temporary tables. Therefore, substantial conversions necessitate understanding the disparities between Oracle and SQL Server global temporary tables.
Global Temporary Tables in Oracle and SQL Server
In Oracle, a global temporary table is a persistent, database-level object defined at design time using DDL. Its “global” nature refers to database-wide accessibility with appropriate permissions. However, while the structure is global, the data is session-specific, invisible to other sessions. Essentially, each session interacts with its own isolated copy of the same global temporary table. Oracle utilizes global temporary tables primarily within PL/SQL for code streamlining and performance enhancement.
In contrast, a SQL Server global temporary table is a transient object created within a Transact-SQL code block. It persists as long as the creating session remains open and is visible (both structure and data) to other database sessions. It facilitates data sharing across sessions.
SQL Server’s local temporary tables differ by being accessible only within the creating session. They are more prevalent (and often more crucial for performance) than global temporary tables in the SQL Server world.
This begs the question: How are local temporary tables employed in SQL Server, and how can they be translated to Oracle?
Local temporary tables in SQL Server are instrumental in reducing or eliminating transaction resource locks, particularly in scenarios involving:
- Aggregation operations on a record set
- Analysis and modification of a dataset
- Multiple uses of the same dataset within a scope
In these cases, offloading the data into a local temporary table often proves beneficial, releasing the lock on the source table.
It’s worth noting that SQL Server’s common table expressions (CTEs, denoted by WITH <alias> AS (SELECT...)) are essentially syntactic sugar, converted to inline subqueries during execution. Oracle CTEs, with a /*+ materialize */ hint, are optimized, creating a temporary materialized view and accessing source data only once. Consequently, SQL Server might benefit from local temporary tables over multiple CTE references, unlike Oracle.
Due to the transaction implementation disparity, temporary tables serve different purposes. Directly migrating SQL Server temporary tables to Oracle (even with Oracle 18c’s private temporary tables) can not only hinder performance but also introduce functional errors.
Conversely, when migrating from Oracle to SQL Server, careful consideration must be given to transaction length, global temporary table visibility, and the performance of CTE blocks using the “materialized” hint.
In either scenario, the presence of temporary tables signifies a need for system re-implementation rather than simple code translation.
Introducing Table Variables
Developers might wonder about table variables and their migration implications. Can they be moved “as-is” during an Oracle-to-SQL-Server migration? The answer lies in their intended use within the code.
Let’s examine the use cases for both temporary tables and table variables, starting with Microsoft SQL Server.
Transact-SQL table variables resemble temporary tables but with added capabilities. The key differentiator is their ability to be passed as parameters to functions and stored procedures.
While this holds true in theory, practical usage presents nuances.
Coming from an Oracle background, I initially assumed SQL Server table variables resided in memory while temporary tables were disk-based. However, pre-2017 SQL Server versions didn’t inherently store table variables in memory, making full table scans on them equivalent to disk operations. Fortunately, SQL Server 2017 onwards supports declarative memory optimization for both.
This raises the question: What purpose do Transact-SQL table variables serve if temporary tables offer comparable or superior functionality? The answer lies in their variable nature. Unaffected by transaction rollbacks, they can be passed as parameters.
Transact-SQL functions are inherently restrictive, designed to return a single value without side effects. Even SELECT statements are deemed side effects because any table access in SQL Server initiates an implicit transaction and associated lock. Consequently, functions cannot access existing temporary tables or create new ones. Table variables become the only option for passing record sets to functions.
Oracle’s approach to using (global) temporary tables and collection variables (the PL/SQL equivalent of Transact-SQL table variables) differs. Oracle collection variables reside in memory, while temporary tables utilize temporary tablespaces. Oracle functions permit read-only access to tables (permanent or temporary), and simple SELECT statements never lock resources.
In Oracle, the choice between collection variables and temporary tables hinges on data volume, required data persistence, and memory/disk allocation considerations. Collection variables are also the standard method for returning row sets to host programs.
Due to the syntactic similarities between SQL Server and Oracle, converting code blocks involving table variables from Transact-SQL to PL/SQL is relatively straightforward. While passing basic validation, it won’t be functionally equivalent without proper temporary table re-implementation. On the other hand, migrating code from Oracle to SQL Server demands more extensive modifications for syntactic correctness and even more so for functional equivalence, particularly when dealing with temporary tables and CTEs.
Inner Transactions (“Nested Transactions”)
Nested transactions represent another significant challenge in Oracle-to-SQL-Server migrations.
The presence of any transaction, nested or not, in Transact-SQL code or any nested transaction in Oracle code signifies the need for functional reimplementation rather than mere code migration.
Let’s first analyze the behavior and use cases of Oracle’s nested transactions.
Nested Transactions in Oracle
Oracle nested transactions are fully atomic and independent of their outer scope. They find no practical application in interactive SQL queries. When working with Oracle interactively, you manually commit changes as needed. If uncertain steps require potential rollback while preserving prior work, you’d establish a savepoint instead of committing or rolling back the entire transaction.
The primary use case for nested transactions lies in PL/SQL code, specifically within autonomous procedures - those declared with PRAGMA AUTONOMOUS_TRANSACTION. This signifies that when invoked (as a named stored procedure or anonymously), the transaction commits or rolls back independently of the calling transaction.
Nested transactions aim to encapsulate units of work, ensuring their success or failure doesn’t affect the invoking code. When both commit and rollback are possible, they’re useful for checking or reserving shared resources (e.g., a room booking system). Commit-only inner transactions primarily serve activity monitoring, code tracing, and security auditing (logging unauthorized modification attempts).
SQL Server Transact-SQL handles nested transactions quite differently.
Nested Transactions in SQL Server
In Transact-SQL, an inner transaction’s commit depends on the outermost transaction’s fate. Rollbacks are straightforward. However, even a committed inner transaction isn’t truly committed until its outermost parent transaction commits. A rollback at any outer level cascades down, undoing inner commits.
This raises the question: What purpose do inner transactions serve if their commits are reversible? Similar to local temporary tables, they facilitate resource lock release, but within the immediate parent transaction’s scope. It’s a performance and resource management technique used in complex Transact-SQL code.
Given the contrasting behavior and use cases of inner/nested transactions in Oracle and SQL Server, migration necessitates not just rewriting but entirely re-architecting any code blocks involving them.
Other Factors
While temporary tables, table variables/collections, and nested transactions present the most prominent migration hurdles, other noteworthy differences warrant attention. Here are some commonly misunderstood aspects:
- Identity columns in SQL Server
- Sequences in Oracle
- Synonyms in Oracle
- Filtered indexes
- Read consistency (Oracle to SQL Server only)
- Use of migration tools
The subsequent part of this series will delve into these aspects, particularly the first three.
Temp Tables, Table/Collection Variables, and Nested Transactions: The Top 3 Migration Pain Points
We began with temporary tables, table variables/collections, and nested transactions because they represent the most prevalent and significant failure points in migration endeavors. Any non-trivial system utilizing Oracle Database or Microsoft SQL Server likely employs some of these elements, whose usage is tightly coupled to the specific transaction implementation of each RDBMS.
