Convert EMPTY_BLOB from Oracle to SQL Server

Oracle provides EMPTY_BLOB function to assign an empty value to a BLOB column. Although length of the value returned by EMPTY_BLOB is 0, it is not equal to NULL. Examples of use:

-- Use EMPTY_BLOB as DEFAULT value
CREATE TABLE people
(
       first_name VARCHAR2(100),
       last_name VARCHAR2(100),
       photo BLOB DEFAULT EMPTY_BLOB()
);
-- Insert a row with the default value:
INSERT INTO people (first_name, last_name) VALUES ('John', 'Smith');
-- Implicit use of EMPTY_BLOB() in INSERT:
INSERT INTO people VALUES ('Jane', 'Doe', EMPTY_BLOB());

SQL Server provides empty binary string constant 0x instead of EMPTY_BLOB. The examples above must be converted to MS SQL as follows:

-- Use 0x as DEFAULT value
CREATE TABLE people
(
       first_name VARCHAR(100),
       last_name VARCHAR(100),
       photo VARBINARY(max) DEFAULT 0x
);
-- Insert a row with the default value:
INSERT INTO people (first_name, last_name) VALUES ('John', 'Smith');
-- Implicit use of 0x in INSERT:
INSERT INTO people VALUES ('Jane', 'Doe', 0x);

Specialists of Intelligent Converters offer Database Migration Service to help you migrate SQL and PL/SQL from Oracle to SQL Server or Azure SQL.