Collations and case in SQL Server and Oracle

A collation is the rule a database uses to compare and sort text, and SQL Server and Oracle start from opposite rules. SQL Server’s setup takes its default from the operating system’s locale: SQL_Latin1_General_CP1_CI_AS for English (United States) and Latin1_General_CI_AS for English (India), both case-insensitive and accent-sensitive. So 'Pune' = 'PUNE' is true, DISTINCT folds Pune, PUNE and pune into one value, and 'café' = 'cafe' stays false. Oracle compares text as binary by default, because the session settings NLS_COMP and NLS_SORT are BINARY: the same comparison is false, DISTINCT keeps three cities, and every capital letter sorts before every small one. Collations therefore decide whether a login, a duplicate check or a report gives the same answer after a move between the engines.

Two defaults on one table

SQLite has two collations that behave like the two defaults for English letters: BINARY compares bytes as Oracle does, and NOCASE ignores the case of the letters A to Z as SQL Server’s _CI collations do. That makes it a fair model of both engines for the words below, which use English letters only:

-- The two defaults, modelled with SQLite's own collations for English letters only:
--   BINARY compares bytes, like Oracle's default (NLS_COMP = BINARY, NLS_SORT = BINARY) or a SQL Server _BIN2 collation;
--   NOCASE folds the 26 letters A to Z, like SQL Server's case-insensitive default (_CI). SQL Server's _CI collations
--   also fold accented capitals, which NOCASE does not, so these rows use English letters only.
CREATE TABLE cities (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO cities (name) VALUES ('Pune'), ('PUNE'), ('pune'), ('Mumbai'), ('mumbai');

CREATE TABLE produce (name TEXT NOT NULL);
INSERT INTO produce (name) VALUES ('banana'), ('Zucchini'), ('apple'), ('Mango');

-- 1. Equality and a filter.
SELECT 'Pune' = 'PUNE'                AS equal_binary,
       'Pune' = 'PUNE' COLLATE NOCASE AS equal_case_insensitive,
       (SELECT count(*) FROM cities WHERE name = 'pune')                AS rows_binary,
       (SELECT count(*) FROM cities WHERE name = 'pune' COLLATE NOCASE) AS rows_case_insensitive;

-- 2. DISTINCT and GROUP BY use the same rule as equality.
SELECT count(DISTINCT name)                AS distinct_binary,
       count(DISTINCT name COLLATE NOCASE) AS distinct_case_insensitive
FROM cities;

-- 3. Sorting: bytes put every capital letter before every small one.
SELECT group_concat(name, ', ' ORDER BY name)                AS order_binary,
       group_concat(name, ', ' ORDER BY name COLLATE NOCASE) AS order_case_insensitive
FROM produce;

┌──────────────┬────────────────────────┬─────────────┬───────────────────────┐
│ equal_binary │ equal_case_insensitive │ rows_binary │ rows_case_insensitive │
├──────────────┼────────────────────────┼─────────────┼───────────────────────┤
│ 0            │ 1                      │ 1           │ 3                     │
└──────────────┴────────────────────────┴─────────────┴───────────────────────┘
┌─────────────────┬───────────────────────────┐
│ distinct_binary │ distinct_case_insensitive │
├─────────────────┼───────────────────────────┤
│ 5               │ 2                         │
└─────────────────┴───────────────────────────┘
┌────────────────────────────────┬────────────────────────────────┐
│          order_binary          │     order_case_insensitive     │
├────────────────────────────────┼────────────────────────────────┤
│ Mango, Zucchini, apple, banana │ apple, banana, Mango, Zucchini │
└────────────────────────────────┴────────────────────────────────┘

Every answer differs. The filter finds one row under the binary rule and three without regard to case. COUNT(DISTINCT name) gives 5 or 2, so a report of “cities” changes size after a move. The sort puts Mango and Zucchini first under the binary rule, because capitals have smaller codes than small letters. Nothing fails loudly: a login, a search box or a duplicate check written for SQL Server keeps running in Oracle and quietly returns fewer rows.

Accents are where the model stops. Letter case in SQL Server is folded for accented letters too, so É equals é under _CI, while SQLite’s NOCASE folds only A to Z. And an accent-sensitive collation (_AS) keeps café and cafe apart in SQL Server, while _AI makes them equal.

Reading a collation’s name

The name of a collation states its rules. Ignoring case in SQL Server is a matter of choosing a name with _CI:

_BIN2 collations compare Unicode code points, which is what Oracle’s BINARY does with the bytes of its database character set. BINARY_CI ignores case but keeps accents; BINARY_AI ignores both.

Where the rule is set

In SQL Server each level is the default for the next one down: the instance collation for databases, the database collation for columns, and a column’s collation for every comparison that uses it. COLLATE after an expression overrides all of them for one comparison or sort. The instance collation also belongs to tempdb, where temporary tables live, which matters as soon as a database uses another collation.

Oracle puts the rule in the session: NLS_SORT names a sort order and NLS_COMP decides which operations follow it. A column can declare its own collation only when the database runs with MAX_STRING_SIZE = EXTENDED; otherwise the DDL fails with ORA-43929. The COLLATE operator on an expression works either way.

Hindi, Marathi and other Indian scripts

A collation in SQL Server also fixes the code page of char and varchar columns, and a Latin code page has no Devanagari letters at all. Microsoft’s documentation adds that data lost in a code-page conversion is not reported, so a Hindi name saved into such a column is damaged without any error. The run below counts characters and bytes:

-- Characters and bytes of four names: what a column sized in bytes can hold. SQLite stores text as UTF-8, so the
-- length of the text cast to a BLOB is its UTF-8 size. UTF-16, used by SQL Server's nvarchar and Oracle's
-- NVARCHAR2 (AL16UTF16), takes 2 bytes for every character up to U+FFFF and 4 bytes above it.
WITH names(label, name) AS (
  VALUES ('English name', 'Asha'), ('French name', 'Zoë'), ('Hindi greeting', 'नमस्ते'), ('Marathi, the word', 'मराठी')
),
chars(label, name, pos, cp) AS (
  SELECT label, name, 1, unicode(substr(name, 1, 1)) FROM names
  UNION ALL
  SELECT label, name, pos + 1, unicode(substr(name, pos + 1, 1)) FROM chars WHERE pos < length(name)
)
SELECT label,
       count(*)                                    AS characters,
       length(CAST(name AS BLOB))                  AS utf8_bytes,
       sum(CASE WHEN cp > 65535 THEN 4 ELSE 2 END) AS utf16_bytes,
       sum(cp > 255)                               AS beyond_latin1,
       name
FROM chars
GROUP BY label, name
ORDER BY utf8_bytes, label;

-- The six characters of नमस्ते: all in the Devanagari block U+0900 to U+097F, so 3 bytes each in UTF-8.
WITH chars(pos, ch) AS (
  SELECT 1, substr('नमस्ते', 1, 1)
  UNION ALL
  SELECT pos + 1, substr('नमस्ते', pos + 1, 1) FROM chars WHERE pos < length('नमस्ते')
)
SELECT pos, printf('U+%04X', unicode(ch)) AS code_point, length(CAST(ch AS BLOB)) AS utf8_bytes, ch
FROM chars;

┌───────────────────┬────────────┬────────────┬─────────────┬───────────────┬────────┐
│       label       │ characters │ utf8_bytes │ utf16_bytes │ beyond_latin1 │  name  │
├───────────────────┼────────────┼────────────┼─────────────┼───────────────┼────────┤
│ English name      │ 4          │ 4          │ 8           │ 0             │ Asha   │
│ French name       │ 3          │ 4          │ 6           │ 0             │ Zoë    │
│ Marathi, the word │ 5          │ 15         │ 10          │ 5             │ मराठी  │
│ Hindi greeting    │ 6          │ 18         │ 12          │ 6             │ नमस्ते │
└───────────────────┴────────────┴────────────┴─────────────┴───────────────┴────────┘
┌─────┬────────────┬────────────┬────┐
│ pos │ code_point │ utf8_bytes │ ch │
├─────┼────────────┼────────────┼────┤
│ 1   │ U+0928     │ 3          │ न  │
│ 2   │ U+092E     │ 3          │ म  │
│ 3   │ U+0938     │ 3          │ स  │
│ 4   │ U+094D     │ 3          │ ्  │
│ 5   │ U+0924     │ 3          │ त  │
│ 6   │ U+0947     │ 3          │ े  │
└─────┴────────────┴────────────┴────┘

Each Devanagari letter and sign is one code point between U+0900 and U+097F, and UTF-8 needs 3 bytes for each of them, so नमस्ते takes 18 bytes in UTF-8 and 12 in UTF-16. Store such text in nvarchar, or in varchar under a _UTF8 collation in SQL Server 2019 and later. In Oracle use an AL32UTF8 database with VARCHAR2 sized in characters, or NVARCHAR2.

Your turn

-- List each address that appears more than once without regard to case:
-- the address in capitals as email_key, and how many accounts share it as accounts.
SELECT email AS email_key, count(*) AS accounts
FROM users
GROUP BY email
HAVING count(*) > 1;

# Sample tests: each one builds a small users table, runs your query on it and compares the rows it returns (in any
# order).
tests:
  - name: finds two spellings of one address
    setup: |
      CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL);
      INSERT INTO users VALUES
        (1, 'asha@mail.in'),
        (2, 'Asha@Mail.in'),
        (3, 'ravi@mail.in');
    expected:
      columns: [email_key, accounts]
      rows:
        - [ASHA@MAIL.IN, 2]
  - name: counts three spellings once, and leaves single addresses out
    setup: |
      CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL);
      INSERT INTO users VALUES
        (1, 'meera.s@mail.in'),
        (2, 'MEERA.S@MAIL.IN'),
        (3, 'Meera.S@mail.IN'),
        (4, 'kabir@mail.in'),
        (5, 'zoya@mail.in');
    expected:
      columns: [email_key, accounts]
      rows:
        - [MEERA.S@MAIL.IN, 3]
  - name: reports every colliding address
    setup: |
      CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL);
      INSERT INTO users VALUES
        (1, 'imran@mail.in'),
        (2, 'Imran@mail.in'),
        (3, 'neha@mail.in'),
        (4, 'NEHA@mail.in'),
        (5, 'neha@MAIL.in'),
        (6, 'tenzin@mail.in');
    expected:
      columns: [email_key, accounts]
      rows:
        - [IMRAN@MAIL.IN, 2]
        - [NEHA@MAIL.IN, 3]
  - name: returns nothing when every address is unique
    setup: |
      CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL);
      INSERT INTO users VALUES
        (1, 'arjun@mail.in'),
        (2, 'arjun.k@mail.in'),
        (3, 'fatima@mail.in');
    expected:
      columns: [email_key, accounts]
      rows: []

Case-insensitive on purpose, per query, per session and in a unique index Pro: opens with a Pro, Premium or Ultimate pass

Collation conflicts, the tempdb trap and sizing Unicode columns, worked through Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• SQL Server’s usual default collation ignores case and respects accents; Oracle compares binary by default.
• In SQL Server, COLLATE sets the rule per database, column or expression.
• In Oracle, NLS_SORT alone changes only sorting; NLS_COMP = LINGUISTIC makes comparisons follow it.
• Store Indian scripts as Unicode, and size byte-counted columns at 3 bytes per Devanagari character in UTF-8.