In my Differences between Oracle and SQL Server when working with NULL and blank values post I already showed you how blanks and NULLS are handled differently between Oracle and SQL Server. Today I found another interesting tidbit.

I you have a varchar or char datatype in SQL Server and you store a blank, you get back a blank or padded spaces.

DECLARE @Test1 varchar(10) = ''
DECLARE @Test2 char(10) = ''

SELECT @Test1,@Test2

The output is one blank and ten spaces.

You can verify this by using the DATALENGTH function .

DECLARE @Test1 varchar(10) = ''
DECLARE @Test2 char(10) = ''

SELECT datalength(@Test1),datalength(@Test2)

Ouput

0     10

In Oracle…not the same

If you run this

SET SERVEROUTPUT ON
 DECLARE 
    Test1 varchar(10):='';
    Test2 char(10):='';
BEGIN
  IF Test1 IS NULL 
  THEN
  DBMS_OUTPUT.PUT_LINE('Test1 is null');
  ELSE
  DBMS_OUTPUT.PUT_LINE('Test1 is NOT null');
  END IF;
  IF Test2 IS NULL 
  THEN
  DBMS_OUTPUT.PUT_LINE('Test2 is null');
  ELSE
  DBMS_OUTPUT.PUT_LINE('Test2 is NOT null');
  END IF;
END;

Output

anonymous block completed
Test1 is null
Test2 is NOT null

As you can see the varchar variable becomes NULL while the nchar variable gets padded

However when inserting into a table this becomes a little bit different with Oracle

Running this in SQL Server

CREATE TABLE TestNull(Col1 CHAR(10),Col2 VARCHAR(10));
INSERT INTO TestNull VALUES(NULL,NULL);
INSERT INTO TestNull VALUES('','');

SELECT * FROM TestNull;

Output

Col1	Col2
NULL	NULL
          	

You get a row with NULLS and a row with blanks, same as with the variables

Running that in Oracle

CREATE TABLE TestNull(Col1 CHAR(10),Col2 VARCHAR(10));
INSERT INTO TestNull VALUES(NULL,NULL);
INSERT INTO TestNull VALUES('','');

SELECT * FROM TestNull;

Here is what you get

As you can see when inserting NULL or a blank into a char column into a table, it does NOT get padded like it did with the variable

If converting code between these two database systems be aware of these kind of things