Should Alice and alice identify two accounts or one account? A case-insensitive unique index can enforce your answer without changing every database comparison.
Define Equality for the Business Key
Case-sensitive databases can legitimately distinguish differently cased text. Account names sometimes need a different rule. Define that rule on the relevant key rather than changing the entire database collation.
Collation controls more than letter case. Accent, width, and other comparison properties also matter for linguistic data. Choose a supported collation whose comparison rules match the actual account-name contract.
I discuss those comparison rules before building a uniqueness index. I also test representative international names with the application owner. A simple English example cannot settle every identity question.
This demonstration uses a case-sensitive source column and a case-insensitive comparison column. The selected comparison is accent-sensitive. Differently cased equivalents collide, while accent handling follows the selected collation's rules.
Inspect the Database and Choose a Local Rule
The first query reports the current database collation without changing it. Use a fresh isolated test database for the table setup. The source column explicitly uses a case-sensitive collation so the example's intent remains visible.
The computed column applies a case-insensitive collation to the same Unicode value. Its unique index enforces comparison under that rule. The original entered spelling remains available in UserName.
SELECT DB_NAME() AS DatabaseName,
DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
CREATE TABLE dbo.UserAccount
(
AccountId int NOT NULL PRIMARY KEY,
UserName nvarchar(80) COLLATE Latin1_General_100_CS_AS NOT NULL,
UserNameCI AS
(UserName COLLATE Latin1_General_100_CI_AS) PERSISTED
);
CREATE UNIQUE INDEX UX_UserAccount_UserNameCI
ON dbo.UserAccount(UserNameCI);
INSERT dbo.UserAccount(AccountId, UserName) VALUES (1, N'Alice');Verify the Case-Insensitive Unique Index Requirements
Indexed computed columns need a permitted data type and an indexable expression. Determinism and precision requirements still apply. Explicit Unicode input avoids unnecessary non-Unicode collation conversion ambiguity in this example.
Persisting the column stores its computed value alongside the row. It does not convert an arbitrary nondeterministic expression into a valid key. Inspect the expression properties instead of treating PERSISTED as a universal fix.
The required SET options also apply to modifying sessions using this indexed expression. Keep application connection settings compatible with the index. A deployment that creates the index successfully does not validate every later writer connection.
SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.UserAccount'),
N'UserNameCI', 'IsDeterministic') AS IsDeterministic,
COLUMNPROPERTY(OBJECT_ID(N'dbo.UserAccount'),
N'UserNameCI', 'IsPrecise') AS IsPrecise;
SELECT name, is_unique, ignore_dup_key
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.UserAccount')
AND name = N'UX_UserAccount_UserNameCI';The index is created with ordinary duplicate rejection behavior. Do not enable duplicate ignoring to avoid application error handling. Silently omitting conflicting rows can hide a broken account creation process.
Use explicit names for the index and its business purpose. That makes error investigations and schema reviews more understandable. The constraint comes from the unique index, even though the table has no separately declared UNIQUE constraint here.
Test the Case-Insensitive Unique Rejection
The next insert attempts a different casing of the existing name. The independent unique index raises error 2601 for that duplicate. A UNIQUE constraint raises error 2627 instead, so callers must understand their actual schema.
The example catches only the expected index error and rethrows anything else. A permission error is not evidence of successful uniqueness enforcement. The final query lets you inspect the retained original spelling.
BEGIN TRY
INSERT dbo.UserAccount(AccountId, UserName) VALUES (2, N'alice');
THROW 50001, 'The equivalent account name was unexpectedly accepted.', 1;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() <> 2601 THROW;
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
SELECT AccountId, UserName, UserNameCI FROM dbo.UserAccount;On my case-sensitive test database, the second insert failed with error 2601. The table kept one row, with the original spelling Alice. Add your application's actual name examples before adopting the rule.
Concurrent requests also benefit from database enforcement. Two callers can both pass a prior existence check. The unique index still arbitrates their conflicting inserts at the database boundary.
Use the Comparison Key in Lookups
Enforcing uniqueness and finding an account should use consistent comparison semantics. Querying the original case-sensitive column with another casing misses a stored name; in my test, UserName = N’ALICE’ returned no rows. The computed key exposes the intended comparison for lookup.
Use a Unicode parameter with the same supported length. Keep the original value for display and audit requirements. The following query describes the lookup without lowercasing the stored source value.
DECLARE @RequestedName nvarchar(80) = N'ALICE';
SELECT AccountId, UserName
FROM dbo.UserAccount
WHERE UserNameCI = @RequestedName COLLATE Latin1_General_100_CI_AS;A small table can still receive a scan despite having the unique index. The index provides a valid access option and enforces the rule independently. Inspect actual plans before making performance claims for larger account catalogs.
Review surrounding application behavior too. Duplicate errors need a clear user-facing response and safe retry decisions. A new spelling of an existing name should not create another account through a fallback path.
Compare the Lowercase-Copy Alternative
Another design stores a normalized lowercase copy beside the entered value. A unique index then protects that copy under a deliberately selected collation. Every writer must produce the same normalization for the same input.
An application-maintained copy can drift when one update path forgets it. A computed expression avoids that synchronization gap for database-defined normalization. A manually stored copy needs an equally strong enforcement mechanism.
LOWER depends on linguistic rules rather than a universal account-identity standard. It does not perform every possible Unicode normalization or application-specific equivalence rule. Define those requirements before choosing normalization as the authority.
A lowercase copy is therefore not automatically equivalent to a chosen case-insensitive collation. Test accents, supplementary characters, and other relevant input categories. Keep the equality rule consistent across the application and database.
Migrate Existing Names to a Case-Insensitive Unique Key
Before adding a case-insensitive unique index, find existing values that collide under its proposed collation. Group using that exact comparison rather than the database default. Resolve collisions through the account ownership process before deployment.
Never delete one conflicting account simply because its identifier is larger. References, authentication history, and ownership all need review. A database index cannot decide which business identity should survive a migration.
The sample requires nonnullable names and does not define trimming rules. SQL Server string comparisons also have trailing-space behavior worth testing. Specify empty-string, whitespace, and nullable-name policies separately from case handling.
I use a case-insensitive unique key to protect a specific identity contract. I preserve entered spelling when readers need it. A username should not gain a secret twin just by changing its capitalization.
Keep the chosen collation and duplicate-handling behavior in the account specification. Revalidate them when accepting new character sets or migration sources. A local comparison rule works best when every caller knows which equality the database enforces.
Related reading on this blog: Unique Indexes on Nullable Columns and Case-Sensitive Search.
A case-insensitive key is not a database-wide collation change, it is a precise rule for one business identity.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
