Oracle to SQL Server code migration notes

Oracle-to-SQL-Server-notes.md

Oracle to SQL Server code migration notes

Things to look for when rewriting Oracle SQL statements for use in SQL Server:

• Parameterize all the things!
• Fix unnamed subqueries (or consider converting to a join)
• If a filter should be case-sensitive but isn't, add a COLLATE clause
• Always specify column names in INSERT statements
• Move ORDER BY clauses out of subqueries
• Get rid of REPLACE(expression, "'", "''") (and parameterize the expression)
• Replace INSTR with CHARINDEX but be aware of the differences
• Change SUBSTR(expression, start, length) to SUBSTRING(expression, start, length) but watch for missing arguments or negative numbers
• Replace SYSDATE with GETDATE()
• When ROUND is used with dates, change to DATEDIFF
• Look for ROUND(expression) with only one argument; change to ROUND(expression, 0)
• Change DECODE to IF-ELSE statements
• Change TO_DATE(expression) to CONVERT(date, expression) (or maybe just remove altogether if expression is already a date)
• Global variable OracleDate can usually be replaced with GETDATE(), but watch for text formatting
• Look for TRUNC and fix it somehow
• Change TO_NUMBER(expression) to CONVERT(int, expression) (or decimal, etc.) (or maybe just remove altogether if conversion is unnecessary)
• Change TO_CHAR(expression, format) to something else, maybe just expression?
• Convert ROWNUM to ROW_NUMBER() OVER ([PARTITION BY value_expression] ORDER BY order_by_expression)
• Change ADD_MONTHS(date, integer) to DATEADD(datepart, number, date)
• Remove FROM DUAL
• Watch for LOWER(expression) and UPPER(expression) and consider that SQL Server is case-insensitive
• Change || to CONCAT for string concatenation. (CONCAT(exp1, exp2) is preferred over exp1 + exp2 because it automatically coerces each expression to a string. Using + can lead to errors if the first expression is numeric.)
• Replace Oracle (+) notation for outer joins with ANSI join syntax
• Change LENGTH to LEN
• Don't INSERT '' when you really want to INSERT NULL
• Replace NULLS FIRST construct in ORDER BY clause
• Replace sequence.NEXTVAL with NEXT VALUE FOR Sequence