Oracle to SQL Server: Translating NVL, ROWNUM and Sequences

The procedure compiles after a syntax edit, but its results change. Moving Oracle to SQL Server requires translating behavior as well as function names. NULL handling, row ordering, empty strings, and sequence allocation deserve small tests before the larger procedure moves.

Oracle NVL in SQL Server: Mind the Result Type

ISNULL and COALESCE can both replace a NULL value. Their type rules differ. ISNULL generally returns the type of its first argument, including its length. COALESCE chooses a type using precedence across its arguments. That can change width or introduce conversion. Choose the intended output type explicitly instead of replacing every NVL mechanically.

DECLARE @short varchar(3)=NULL;
SELECT ISNULL(@short,'longer') AS isnull_value,
       COALESCE(@short,'longer') AS coalesce_value;
DECLARE @amount decimal(12,2)=NULL;
SELECT COALESCE(@amount,CONVERT(decimal(12,2),0)) AS amount_value;

In the first query, ISNULL returns lon and COALESCE returns longer. I test the NULL branch and the non-NULL branch. A migration test that supplies only populated values never exercises the fallback. What data type and length should the consuming application receive? That answer belongs in the conversion, not in an assumption about similarly named functions.

Express NVL2 as a Searched CASE

NVL2 chooses one expression when its first argument is not NULL and another when it is NULL. In T-SQL, searched CASE states that rule directly. The THEN and ELSE expressions still converge to one result type. A numeric branch and a text branch do not create a column that changes type by row.

DECLARE @contact nvarchar(40)=NULL;
SELECT CASE WHEN @contact IS NOT NULL
            THEN N'contact supplied' ELSE N'contact missing' END AS contact_state;

ISNULL alone does not express the two independent NVL2 result branches. COALESCE is useful when selecting the first non-NULL value from several alternatives. I write the desired NULL behavior as a small truth table, then choose the expression that implements it clearly.

Replace Oracle ROWNUM With an Ordered SQL Server Limit

For a simple limited result, use TOP with ORDER BY. Without an order, the selected rows are not a stable top set. Oracle's ROWNUM can be applied before ordering in the same query block, so inspect the original nesting before deciding what the source meant. A textual replacement can preserve a mistake rather than preserve intent.

DECLARE @work TABLE(ItemID int PRIMARY KEY,Score int);
INSERT @work VALUES(1,20),(2,50),(3,50),(4,10);
SELECT TOP (2) ItemID,Score FROM @work
ORDER BY Score DESC,ItemID;
WITH numbered AS
(
    SELECT ItemID,Score,
           ROW_NUMBER() OVER(ORDER BY Score DESC,ItemID) AS rn
    FROM @work
)
SELECT ItemID,Score FROM numbered
WHERE rn BETWEEN 2 AND 3 ORDER BY rn;

ROW_NUMBER supports an explicitly ordered sequence and later filtering. Use a unique tie breaker, as ItemID does here. Decide whether ties should all be included or whether the contract requires a fixed number of rows. TOP WITH TIES is another choice, but it changes cardinality and deserves a separate test.

Match Date Precision and Time Meaning

SYSDATE is commonly translated to a SQL Server current-time function, but choose precision and time zone deliberately. SYSDATETIME returns a datetime2 value for the server's local time. SYSUTCDATETIME returns UTC. GETDATE returns the older datetime type. Casting can be necessary when the original application expects a date without fractional seconds.

SELECT SYSDATETIME() AS server_local_time,
       SYSUTCDATETIME() AS utc_time,
       CONVERT(date,SYSDATETIME()) AS server_local_date;

Capture the business timestamp once when several expressions must agree. Do not let a migration silently turn local calendar rules into UTC rules or vice versa. I test midnight boundaries and client serialization. The database server's clock has no special knowledge of the user's reporting day.

Date and numeric conversions need the same care. Set formats explicitly at the interface. A string that happened to parse under one session's language can fail elsewhere. Keep database values typed through the procedure, and format them only for the caller that needs text. I test the result metadata along with the values because a changed type can break a client before a row looks wrong.

Translate DECODE Without Losing NULL Matching

CASE is the T-SQL tool for DECODE-style branching. A simple CASE uses equality, so WHEN NULL will not match a NULL input. Oracle DECODE's NULL comparison behavior makes that an important translation trap. Use searched CASE with IS NULL for that branch, then state other comparisons and the default explicitly.

DECLARE @code varchar(10)=NULL;
SELECT CASE WHEN @code IS NULL THEN 'missing'
            WHEN @code='A' THEN 'active'
            WHEN @code='I' THEN 'inactive'
            ELSE 'unknown' END AS code_label;

Check every branch's result type and precedence. A missing ELSE produces NULL, which can alter later aggregates. Keep unknown source codes visible during migration rather than mapping them quietly to a valid category. A tidy label can conceal an untidy conversion.

Move Oracle Sequences to SQL Server Deliberately

A sequence is an independent schema object. NEXT VALUE FOR allocates a number without requiring an identity insert. Create the object with the desired type, bounds, increment, cycling rule, and cache policy. Do not promise gap-free values: allocated sequence numbers are consumed independently of whether the surrounding transaction commits.

CREATE SEQUENCE dbo.OrderNumber
AS bigint START WITH 1000 INCREMENT BY 1 NO CYCLE;
GO
SELECT NEXT VALUE FOR dbo.OrderNumber AS order_number;
SELECT name,current_value,increment,is_cycling
FROM sys.sequences WHERE object_id=OBJECT_ID(N'dbo.OrderNumber');

Use a disposable database for the example and create it only once. Existing applications can already have allocated values, so choose the start from a verified migration plan. A unique constraint on the destination remains valuable. Sequence allocation and persisted uniqueness are related but separate guarantees.

A sequence is also independent of row ordering. If a bulk statement assigns sequence values, document whether their association with input order matters. Use supported ordered allocation syntax where that requirement exists. Never infer business priority from a number merely because it was allocated earlier in a concurrent workload.

Preserve the Empty-String Contract

Oracle treats a zero-length character string as NULL in familiar string contexts. SQL Server stores an empty string separately from NULL. Normalize explicitly if the application depends on the old rule. Use DATALENGTH when you mean exactly empty, because LEN ignores trailing spaces and ordinary string comparison has padding behavior.

DECLARE @text nvarchar(20)=N'';
SELECT CASE WHEN @text IS NULL THEN 1 ELSE 0 END AS stored_null,
       CASE WHEN DATALENGTH(@text)=0 THEN NULL ELSE @text END AS normalized_text;

Do not collapse whitespace unless the business rule asks for it. I include empty, NULL, one space, and ordinary text in the test set. That small matrix protects searches, uniqueness rules, and optional-field behavior from an invisible change.

Choose Concatenation by NULL Behavior

The + operator concatenates compatible string operands, but modern SQL Server returns NULL when a participating value is NULL. CONCAT treats NULL as an empty string and converts its arguments. SQL Server 2025 also supports ||, whose NULL behavior follows ANSI rules. Oracle's familiar treatment of NULL during concatenation must still be tested rather than assumed identical.

DECLARE @first nvarchar(20)=N'SQL',@second nvarchar(20)=NULL;
SELECT @first+@second AS plus_result,
       CONCAT(@first,@second) AS concat_result;
-- SQL Server 2025:
SELECT @first || @second AS pipes_result;

On SQL Server 2025, the + and || versions both returned NULL here, and CONCAT returned SQL. Oracle's || skips the NULL, so CONCAT is the closer match. I keep Oracle to SQL Server migration tests focused on result values, types, NULLs, ordering, and side effects. Syntax success is only the beginning. The translated statement should implement the same agreed business rule, with differences recorded where the new platform needs an explicit choice.

Related reading on this blog: Difference Between ISNULL and COALESCE and How is Oracle Temporary Table Different from SQL Server? Interview Question of the Week #133.

A syntax translation is not a behavior test, it is the first step toward matching the intended result.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.