Choosing the Server Collation at Install Time

Two strings look equal in one environment and different in another. The server collation chosen at installation sets defaults that reach system databases and tempdb, so this decision deserves a real test before Setup runs.

Know Which Server Collation You Are Choosing

A collation controls how character data compares and sorts. It includes rules for case, accents, and other language behavior. SQL Server has a server-level collation, while databases and columns can have their own. The instance choice sets the default for system databases and newly created databases unless another collation is specified. It is not a universal override for every column on the server.

I ask what the application expects when it compares values. Are CustomerA and customera the same key? Should accented names sort together or apart? Those are business and application questions. Picking a familiar collation name from another instance without testing them can leave an invisible rule in the foundation.

Test Comparison Rules With Real Examples

Write down a small set of strings that exposes case, accent, and sorting expectations. Include the characters the application actually stores. Test equality, uniqueness, and ordering under candidate collations. A case-insensitive rule can allow a query to find a row with different casing, but it can also reject two values that the business considers distinct under a unique key.

The query below compares two values under explicit collations. Replace the sample values with approved examples from your domain, and review the result before committing the instance setting. I keep this test beside the install plan so the choice can be explained later.

SELECT CASE WHEN N'CodeA' COLLATE Latin1_General_100_CI_AS =
                 N'codea' COLLATE Latin1_General_100_CI_AS
            THEN 1 ELSE 0 END AS equal_case_insensitive,
       CASE WHEN N'CodeA' COLLATE Latin1_General_100_CS_AS =
                 N'codea' COLLATE Latin1_General_100_CS_AS
            THEN 1 ELSE 0 END AS equal_case_sensitive;

Understand the Tempdb Surprise

Temporary tables use tempdb, and tempdb normally follows the server collation. A user database can have a different collation. Joining a character column from a temporary table to one in that database can then produce a collation conflict or an unexpected comparison rule. The problem appears in ordinary reporting and load code, not only in internationalized applications.

I check temporary table joins in a test copy before approving a server build. If a database deliberately uses another collation, define temporary character columns with COLLATE DATABASE_DEFAULT when that matches the procedure’s intended context. Do not scatter explicit collation overrides just to silence errors. Confirm the comparison semantics are right, then make the rule visible in code.

Inspect Current Instance Defaults

A replacement server should be compared with the system it replaces. Query server and database collations, then identify columns with explicit overrides in the application database. A matching server name or edition says nothing about these rules. The following checks are read only and do not require a sample table.

Look for mismatches that matter to joins, temporary work tables, unique keys, and sorting. I also check the database compatibility level separately because it affects other query behavior. One setting is not a shortcut for reviewing all of the environment.

SELECT SERVERPROPERTY('Collation') AS server_collation;
SELECT name, collation_name
FROM sys.databases
WHERE name IN (N'master', N'model', N'tempdb', N'AppDb')
ORDER BY name;

Plan for a Difficult Server Collation Change Later

Changing server collation after installation is not a simple sp_configure update. Rebuilding system databases is involved, and user databases and columns do not automatically become the new collation. The work can affect logins, jobs, system objects, and application code. Backups, a tested rebuild plan, and a comparison of object definitions are essential before any such operation.

That cost is why I prefer to decide once, with evidence, when the instance is first built. A future application can still choose a different database or column collation where justified. But each exception adds a rule that developers must understand. Which combinations will the application join? Answer that during design rather than after a failed production query.

Choose From the Application Contract

Start with the application’s supported collation requirements. For a new application, define desired case and accent behavior and the language rules it needs. Test those rules against searches, unique constraints, and reports. Then check any vendor product installed on the same instance. A vendor database with a documented collation requirement can narrow the choice.

A modern Windows collation can be a sensible option for new work, but the exact name should be selected from tested requirements. Do not copy a suffix because it sounds current. Record the selected name and the reason in the installation specification. I include a small comparison script with the build record so future maintainers can reproduce the decision.

Verify the Installed Server Collation

After Setup, query SERVERPROPERTY(‘Collation’) and the system database collations. Create a small test database with the intended default and run the comparison examples again. Check a temporary table join if the application database collation differs. The build is not complete until those results match the installation specification.

Review scripts that create new databases without an explicit collation. They inherit a default that can differ between instances. Make a deployment script explicit when consistent behavior matters across environments. A collation decision should become a documented contract, not a surprise found during the first import.

A collation decision also affects joins across databases and temporary objects. A query can work inside one database and fail when it compares a string with data from a database using another collation. Temp tables can inherit tempdb collation unless the column is declared with DATABASE_DEFAULT, so a cross-database workload needs deliberate testing. I use a small representative join and comparison set after installation, not just a query that prints the collation name.

Changing a database default later does not automatically rewrite the collation of existing character columns. Changing the instance collation is more involved still because system databases are part of the instance. That is why I ask application owners for sorting and case-sensitivity expectations before setup. If the answer is uncertain, build two disposable test environments and try the actual search, uniqueness and join cases. A spelling preference is easy to discuss; a production collation rebuild is a much less pleasant meeting.

Related reading on this blog: Change Database and Table Collation and Case of Different Default Collation on Two Servers.

Server collation is not a cosmetic install option, it is a comparison rule that follows the instance.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.