SQL Server collations, Unicode and nvarchar vs varchar

For most applications on SQL Server, store human-readable text in nvarchar, write string literals as N'...', and create the database with a modern collation such as Latin1_General_100_CI_AS_SC. That combination stores any language and any emoji correctly, compares text case-insensitively the way users expect, and matches what every driver sends by default. The alternative since SQL Server 2019 is varchar with a UTF-8 collation, which stores mostly-English text in half the space but needs more care. Everything that goes wrong with text in SQL Server - question marks where accents should be, Cannot resolve the collation conflict, an index that is suddenly ignored - comes from mixing these choices without meaning to. This guide explains each piece so you can choose on purpose.

varchar, nvarchar, and what the N means#

SQL Server has two families of string type:

The trap is in the third column. varchar(50) is 50 bytes, not 50 characters, and nvarchar(50) is 50 UTF-16 code units, not 50 characters either. For everyday Latin text the difference never shows. For an emoji, which takes two UTF-16 code units, or for Chinese stored as UTF-8 at three bytes a character, it does.

Without a UTF-8 collation, varchar stores text in a single legacy code page - Windows-1252 for the Latin collations most servers use. That code page has room for English and most Western European accents and nothing else. Store Polish, Greek, Cyrillic, Arabic or any emoji in it and SQL Server substitutes a question mark or a look-alike character, silently, at write time. The data is gone; no later conversion brings it back.

The N prefix on a literal is the same choice applied to constants. 'Zoë' is a varchar literal in the database's code page; N'Zoë' is a Unicode literal. Insert 'Łódź' into an nvarchar column and you still get damaged text, because the literal was converted to the code page before it ever reached the column:

CREATE TABLE dbo.Cities (Name nvarchar(100) NOT NULL);INSERT dbo.Cities (Name) VALUES ('Łódź');   -- arrives as 'Lódz' or with '?'INSERT dbo.Cities (Name) VALUES (N'Łódź');  -- arrives intactSELECT Name FROM dbo.Cities;

Application code that uses parameters is safe here, because drivers send strings as Unicode by default. The N matters in scripts, migrations, seed data and anything typed into SSMS. Make it a habit for every string literal headed for an nvarchar column.

What a collation decides#

A collation is a set of rules attached to a server, a database, a column or an expression. It decides three things:

1. Which characters `varchar` can store - the code page, or UTF-8.
2. How strings compare - whether 'abc' = 'ABC', whether 'resume' = 'résumé'.
3. How strings sort - the order ORDER BY returns, and therefore the order of an index on a text column.

Collation names encode the rules, and once you can read them the choice gets much easier:

So Latin1_General_100_CI_AS_SC_UTF8 is: Latin general rules, version 100, case-insensitive, accent-sensitive, supplementary-character aware, UTF-8 in varchar. List every collation the server knows with SELECT name, description FROM sys.fn_helpcollations();.

Windows collations and SQL_ collations#

Collations starting with SQL_ are the legacy family, kept for compatibility with SQL Server versions from the 1990s. The best known is SQL_Latin1_General_CP1_CI_AS, still the default server collation for an English (United States) Windows install and the default on Linux. The others - Latin1_General_100_CI_AS and friends - are Windows collations.

The important difference is how they treat varchar. A Windows collation applies the same linguistic rules to varchar and nvarchar. A SQL_ collation uses older, simpler rules for varchar and Windows rules for nvarchar, so the same two strings can compare or sort differently depending on their type. It also changes what happens when an nvarchar parameter meets a varchar column: with a Windows collation the optimiser can often still seek an index, with a SQL_ collation it scans. On a busy table that is the difference between a query in a millisecond and one in a second, and it is invisible in the code. SQL Server indexes and execution plans shows how it looks in a plan.

There is no reason to choose a SQL_ collation for a new database. Use a version 100 Windows collation, and add _SC so that string functions count emoji and other supplementary characters correctly:

SELECT LEN(N'😀' COLLATE Latin1_General_100_CI_AS)     AS without_sc,   -- 2       LEN(N'😀' COLLATE Latin1_General_100_CI_AS_SC)  AS with_sc;      -- 1

UTF-8 collations: varchar that stores everything#

SQL Server 2019 added collations ending in _UTF8. With one, varchar columns store UTF-8, so they hold every Unicode character, and plain ASCII text takes one byte per character instead of the two that nvarchar uses.

For an application whose text is mostly English, product codes, URLs and JSON, UTF-8 varchar can roughly halve the space strings take - which matters on Express, where every database is capped at 10 GB. For text that is mostly East Asian, it is bigger than nvarchar, not smaller.

The costs are worth knowing before you commit:

• varchar(n) still counts bytes, so varchar(50) holds 50 ASCII characters but only 16 Chinese ones. Size columns for the byte length of real data, or use varchar(max) where it does not matter.
• Drivers still send strings as nvarchar by default, so every comparison with a parameter involves a conversion unless you declare parameters as varchar. EF Core does this when a property is mapped with IsUnicode(false); with ADO.NET, set SqlDbType.VarChar; with the JDBC driver, sendStringParametersAsUnicode=false.
• Only some tools and older client libraries are comfortable with it. Test the whole stack, including reporting and ETL.

A UTF-8 collation is a sound choice for a new application where you control the data access layer and storage matters. If in doubt, nvarchar is the choice that cannot surprise you.

Server, database, column and expression collation#

Collation is set at four levels, each the default for the next:

• Server - chosen at install time. It is the collation of the system databases, including tempdb. On Linux it is set with MSSQL_COLLATION or mssql-conf set-collation, and changing it later is a rebuild of the system databases.
• Database - set by CREATE DATABASE ... COLLATE, defaulting to the server's. It is the default for new columns, for literals and variables in that database, and it governs object names - so in a case-insensitive database dbo.Orders and dbo.ORDERS are the same table.
• Column - set in CREATE TABLE or ALTER TABLE ... ALTER COLUMN, defaulting to the database's.
• Expression - the COLLATE clause overrides it for one comparison or sort.

SELECT SERVERPROPERTY('Collation')                  AS server_collation,       DATABASEPROPERTYEX(DB_NAME(), 'Collation')   AS database_collation;SELECT t.name AS table_name, c.name AS column_name, c.collation_nameFROM sys.columns AS cJOIN sys.tables  AS t ON t.object_id = c.object_idWHERE c.collation_name IS NOT NULLORDER BY t.name, c.column_id;

On a hosted server you get a server collation you cannot change. What you control is the database: as sa, you can create a new database with the collation you want. On RE:NODE, a database is created for you and set as sa's default; check its collation with the query above before you build on it, and if it is not what you want, create another with CREATE DATABASE [app] COLLATE Latin1_General_100_CI_AS_SC; while it is still empty.

The collation conflict error#

The error everyone meets eventually:

Msg 468, Level 16, State 9Cannot resolve the collation conflict between "Latin1_General_100_CI_AS_SC" and"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Two strings with different collations cannot be compared, because SQL Server does not know whose rules to use. The most common source is tempdb. Temporary tables are created in tempdb, so their columns take the server's collation, not your database's. If the two differ, joining a temporary table to a real one fails.

-- Fails if the server and database collations differCREATE TABLE #ids (Code varchar(20));-- Works everywhere: take the current database's collationCREATE TABLE #ids (Code varchar(20) COLLATE DATABASE_DEFAULT);

Make COLLATE DATABASE_DEFAULT a habit on every string column of every temporary table, and code moved between servers stops breaking. Table variables are created with the database's collation and avoid the problem. For a one-off comparison, COLLATE on one side of the expression resolves it, at the cost that an index on the converted side cannot be used for a seek.

Restoring a database onto a server with a different collation causes the same issue, and the database keeps its own collation; only tempdb and the system databases differ. That is another reason to use DATABASE_DEFAULT rather than naming a collation.

Case sensitivity and other comparison surprises#

A few behaviours that are correct by the rules and surprising in practice:

• Case-insensitive uniqueness. With a CI collation, a unique index on Email rejects Bob@example.com when bob@example.com exists. Usually what you want; occasionally, for case-sensitive identifiers such as tokens, it is not, and that column needs a CS or BIN2 collation.
• Trailing spaces are ignored in comparisons. 'abc' = 'abc ' is true, following the SQL standard's padding rules. LEN also ignores trailing spaces; DATALENGTH does not. LIKE is the exception and does not pad the pattern.
• Accent sensitivity is separate from case. CI_AS says 'resume' <> 'résumé'. If users search names without typing accents, an AI collation on that column, or a separate normalised search column, gives the behaviour they expect.
• A `COLLATE` in a `WHERE` clause disables index seeks on that column. To search case-sensitively in a case-insensitive column without scanning, compare normally first (which can seek) and then add the case-sensitive check:

SELECT UserIdFROM dbo.UsersWHERE UserName = @name  AND UserName = @name COLLATE Latin1_General_100_CS_AS;

The first predicate seeks to the few rows that match ignoring case; the second filters those few exactly.

Language-specific and binary collations#

Latin1_General is a compromise that sorts reasonably for English and most Western European languages. Some languages have rules it does not follow, and if your users read sorted lists in those languages, they will notice:

• Turkish has a dotted and a dotless i, so the upper case of i is İ and the lower case of I is ı. Under Turkish_100_CI_AS, UPPER(N'i') returns İ, and a case-insensitive comparison of 'FILE' with 'file' is false. Under Latin1_General, Turkish users see their names sorted and cased wrongly.
• Danish and Norwegian sort æ, ø and å after z, and treat the old spelling aa as å. Danish_Norwegian_100_CI_AS does this; Latin1_General puts Ålesund near Aalborg at the start.
• Polish, Czech and other Central European languages sort accented letters as separate letters after their base letter. Each has its own collation family.
• German has two conventions. The default treats ä as a variant of a; German_PhoneBook_100_CI_AS sorts it as ae, the way telephone directories did.

You do not have to pick one language for the whole database. A column-level collation, or ORDER BY Name COLLATE Danish_Norwegian_100_CI_AS on one query, applies the rules where they matter. An index is sorted by its column's collation, though, so a query that sorts by a different collation cannot use the index order and must sort itself.

At the other end are the binary collations ending in BIN2. They compare raw code points: no linguistic rules, case-sensitive, accent-sensitive, and the cheapest comparisons SQL Server can do. Uppercase letters sort before all lowercase ones, so 'Zebra' comes before 'apple'. That makes them wrong for anything a person reads in sorted order and right for machine identifiers - API keys, hashes, tokens, case-sensitive codes - where exact matching is the point and speed helps. A BIN2 collation on such a column also stops a case-insensitive default from treating two different tokens as duplicates.

Changing a collation after the fact#

Changing a database's collation is possible but does less than people hope:

ALTER DATABASE [app] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;ALTER DATABASE [app] COLLATE Latin1_General_100_CI_AS_SC;ALTER DATABASE [app] SET MULTI_USER;

This changes the default for new columns and the rules for object names. It does not touch existing columns, which keep their old collation, and it fails if schema-bound objects such as indexed views or computed columns depend on the current collation. Each existing column must be altered individually, which requires dropping and recreating every index, constraint and statistic that uses it:

ALTER TABLE dbo.CustomersALTER COLUMN Name nvarchar(200) COLLATE Latin1_General_100_CI_AS_SC NOT NULL;

Repeat the column's nullability, or ALTER COLUMN will make it nullable. On a database of any size, the cleaner route is often to create a new database with the right collation, create the schema there, and copy the data across with bcp or INSERT ... SELECT. Migrating a database to SQL Server hosting covers the mechanics, and MySQL utf8mb4 and collations is the same story told for MySQL, if you are coming from there.

FAQ#

Should I use nvarchar or varchar for a new application?

nvarchar for anything people type - names, addresses, messages - unless storage is tight and you control every parameter type, in which case varchar with a UTF-8 collation is a good option. Plain varchar with a non-UTF-8 collation is only safe for codes and identifiers you know are ASCII.

Why do my accented characters turn into question marks?

The text passed through a code page that cannot represent them: a varchar column without a UTF-8 collation, or a string literal written without the N prefix. Check the column type and add N to literals. Text already stored with question marks cannot be recovered.

Can I store emoji in SQL Server?

Yes, in nvarchar with any collation, or in varchar with a UTF-8 collation. Use an _SC collation if you want LEN, SUBSTRING and similar functions to treat each emoji as one character rather than two.

What collation should a new database use?

Latin1_General_100_CI_AS_SC for an English or Western European application using nvarchar, or Latin1_General_100_CI_AS_SC_UTF8 if you plan to use UTF-8 varchar. Use a language-specific collation when sorting must follow one language's rules, such as Polish or Turkish.

Is SQL Server case-sensitive?

Not by default. Data comparisons and object names follow the collation, and the default collations are case-insensitive. A case-sensitive collation on the database makes table and column names case-sensitive too, which surprises application code, so apply it to specific columns instead.