Does SQL Server treat Sharma and SHARMA as the same name? That is a collation decision — and the main one is locked in during installation. Here is how to read the names and pick without regret.
A collation is SQL Server's rulebook for text: the sort order in ORDER BY, and whether comparisons treat upper and lower case — or accents — as equal. You will see names like SQL_Latin1_General_CP1_CI_AS. Every part of that string means something, and one choice made in setup, the instance collation, follows the server for its whole life.
After the character-set portion, two abbreviations run the show. CI means Case Insensitive: a search for rahul also finds Rahul — the behaviour most users expect. CS would mean Case Sensitive, where those are different values. AS means Accent Sensitive: the words cafe and café are different; AI would treat them as equal. For Indian business data in English — names, invoices, item codes — CI_AS, the usual default, is the right, boring choice. Creative choices here come back as 'the search is broken' complaints that are actually comparison mismatches.
Collation exists at three levels. The instance (server) collation is chosen during installation; it governs the system databases and, importantly, TempDB, which is rebuilt in that collation at every restart. The database collation is set when a database is created — it defaults to the server's but can be chosen differently, and changing it later, while not trivial, is at least possible. The column collation can override both for a single text column. Most trouble comes from mixing levels unknowingly, not from any one of them.
Here is the part setup wizards do not shout about: there is no settings page to change the instance collation afterwards. Microsoft's documented route is to rebuild the system databases using setup with a special rebuild switch, which resets logins and server configuration, and then re-attach or restore every user database — each of which still carries its own old collation anyway. On a live server that means planned downtime and careful documentation. Compare that cost with the one minute it takes to check the Collation tab during installation, and the title of this guide explains itself.
The classic real-world failure looks like this: your database uses one collation, TempDB uses the server's different one, and a report that joins a table to a temporary table explodes with Cannot resolve the collation conflict. The fix at query level is adding COLLATE DATABASE_DEFAULT to the comparison — a patch, not a cure. The cure is alignment: keep the server and your main databases on the same collation unless an application genuinely demands otherwise, and the conflict never gets a chance to exist.
Three rules cover nearly every install. One: if an application vendor specifies a collation, use exactly that — their support will test against it. Two: otherwise accept the installer's default for your Windows locale (on English systems, SQL_Latin1_General_CP1_CI_AS) and move on. Three: if you are migrating an old server, match its collation — run SELECT SERVERPROPERTY('Collation'); on the old box and set the new one the same, so restored databases and TempDB agree from day one.
Watch the install video and pause at the Server Configuration screen — the Collation tab is right there, one click that saves a rebuild later.
▶️ Watch Full VideoRun SELECT SERVERPROPERTY('Collation') in SSMS. For a single database, right-click it in SSMS, choose Properties and look at the collation on the General page, or query sys.databases. Keep the result in your server documentation — you will need it at migration time.
Yes. Each database carries its own collation, chosen at creation. Queries inside one database are consistent; the friction appears when you join across databases or against temporary tables, because TempDB always uses the server collation. It is manageable, but alignment is simpler.
On the Server Configuration page of setup there is a Collation tab with a Customize option — set the instance collation there before installing. If the requirement is only for one database, you can also set it in the CREATE DATABASE statement later; the instance choice is the one that cannot be casually revisited.