CHAR Data type

Description

The CHAR data type specifies a fixed-length character string in the database character set.(Oracle SQL Language Reference CHAR Data type)

As denoted in the Oracle documentation, size in CHAR data type is a length constraint and should not be confused with capacity. Total characters that can be stored in a CHAR may vary according to the database character set and configuration, but commonly the maximum size allowed is 2000.

In Snowflake, CHAR types are synonymous with VARCHAR, and as you can check here:

Snowflake SQL Language reference text data types

The standard maximum size is quite bigger. But, this doesn’t mean that a Snowflake VARCHAR will consume more storage, as mentioned in their documentation:

A 1-character string in a VARCHAR(16777216) column only consumes a single character.

CHAR [ (size [ BYTE | CHAR ])

Sample Source Patterns

Char data types in Create Table

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE char_data_types
(
	char_column1 CHAR,
	char_column2 CHAR(15),
	char_column3 CHAR(15 BYTE),
	char_column4 CHAR(15 CHAR)
);

INSERT INTO char_data_types VALUES ('H', 'Hello world', 'Hello world', 'Hello world');

Snowflake

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE char_data_types
(
	char_column1 CHAR,
	char_column2 CHAR(15),
	char_column3 CHAR(15),
	char_column4 CHAR(15)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO char_data_types
VALUES ('H', 'Hello world', 'Hello world', 'Hello world');

Retrieving data from char columns

Oracle

Query

IN -> Oracle_02.sql

SELECT * FROM char_data_types;

Result

CHAR_COLUMN1|CHAR_COLUMN2   |CHAR_COLUMN3   |CHAR_COLUMN4   |
------------+---------------+---------------+---------------+
H           |Hello world    |Hello world    |Hello world    |

Snowflake

Query

OUT -> Oracle_02.sql

SELECT * FROM
char_data_types;

Result

CHAR_COLUMN1|CHAR_COLUMN2|CHAR_COLUMN3|CHAR_COLUMN4|
------------+------------+------------+------------+
H           |Hello world |Hello world |Hello world |

In Oracle, the value is filled with empty spaces to fit the fixed size determined in the column definition. On the other hand, Snowflakes uses dynamic size (keeping the length restriction) to store the value.

Checking internal data types for CHAR

As mentioned in the beginning, Snowflake internally uses a VARCHAR for the CHAR type columns, we can confirm it by describing the tables:

Oracle

Snowflake

The length restriction is preserved, but the memory that the columns are using is different on each DBMS.

Retrieving the size in bytes of each column:

Oracle

Query

IN -> Oracle_03.sql

SELECT 
LENGTHB(char_column1),
LENGTHB(char_column2),
LENGTHB(char_column3),
LENGTHB(char_column4)  
FROM char_data_types;

Result

LENGTHB(CHAR_COLUMN1)|LENGTHB(CHAR_COLUMN2)|LENGTHB(CHAR_COLUMN3)|LENGTHB(CHAR_COLUMN4)|
---------------------+---------------------+---------------------+---------------------+
                    1|                   15|                   15|                   15|

Snowflake

Query

OUT -> Oracle_03.sql

SELECT
OCTET_LENGTH(char_column1) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(char_column2) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(char_column3) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(char_column4) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/
FROM
char_data_types;

Result

OCTET_LENGTH(CHAR_COLUMN1)|OCTET_LENGTH(CHAR_COLUMN2)|OCTET_LENGTH(CHAR_COLUMN3)|OCTET_LENGTH(CHAR_COLUMN4)|
--------------------------+--------------------------+--------------------------+--------------------------+
                         1|                        11|                        11|                        11|

Besides these slight differences, the integrity of the data is preserved.

Known Issues

1. Results obtained from some built-in functions may vary

As explained in the previous section, there may be cases using built-in functions over the columns that may retrieve different results. For example, get the length of a column.

Related EWIs

1. SSC-FDM-OR0015: LENGTHB transformed to OCTET_LENGTH.