SQL - Collations & Charsets
Overview
Charsets define how text is encoded; collations define sorting/compare rules. Choose UTF-8/UTF-16 families for modern apps.
Inspect Current Settings
SHOW VARIABLES LIKE 'character\_%';
SHOW VARIABLES LIKE 'collation%';
SELECT SERVERPROPERTY('Collation');
SELECT name, collation_name FROM sys.databases;
-- SQLite stores text as UTF-8/UTF-16 dynamically; collations can be custom
PRAGMA encoding; -- returns UTF-8 by default
Specify Collation
CREATE TABLE users (
name VARCHAR(100) COLLATE utf8mb4_0900_ai_ci
);
SELECT * FROM users ORDER BY name COLLATE utf8mb4_0900_as_cs;
CREATE DATABASE AppDb COLLATE Latin1_General_100_CI_AI_SC_UTF8;
SELECT * FROM dbo.Users ORDER BY name COLLATE Latin1_General_100_CS_AS;
-- Use built-in NOCASE collation or define custom collations via extensions
CREATE TABLE users(name TEXT);
SELECT * FROM users ORDER BY name COLLATE NOCASE;