A customer types Jose and expects to find José. An accent-insensitive collation makes that comparison work without removing the accent from the stored name. Choose the search rule separately from the spelling you display.
Read the Collation Suffixes
Collation controls comparison and sorting rules for character data. AI means accent insensitive, while AS means accent sensitive. CI and CS control case sensitivity independently. A CI_AI choice ignores both differences for matching.
That doesn't rewrite the original text. The value can retain its accents while the predicate uses a broader equality rule. Keep storage and comparison as separate concepts.
I ask how users expect names to match before changing the database default. A broad default change affects more than one search box. It can change uniqueness, joins, and ordering throughout the application.
A focused query-level collation is easier to evaluate first. Use Unicode strings and columns for these examples. That keeps character conversion from hiding behind the collation test.
List the Available Choices
The sys.fn_helpcollations function returns collation names and descriptions supported by the instance. Filter the list to a relevant family rather than selecting a name from memory. The example compares two related choices with different accent behavior.
Choosing a familiar language family matters because collations contain more rules than their suffixes. An AI suffix doesn't mean every linguistic comparison behaves identically across families.
The query is read-only and works as a starting inventory. Keep the selected collation in the search specification. Don't let different endpoints pick different rules for the same customer lookup.
When a user reports a mismatch, that record gives you something precise to check. Spelling preferences shouldn't have to be rediscovered by every developer who touches a WHERE clause.
SELECT name, description
FROM sys.fn_helpcollations()
WHERE name IN (N'Latin1_General_100_CI_AI',N'Latin1_General_100_CI_AS',
N'Latin1_General_100_CS_AI')
ORDER BY name;Apply an Accent-Insensitive Rule to One Predicate
Use COLLATE on the expression to test the desired rule. For the accent-insensitive comparison, the sample source retains Jose, José, and an uppercase variant. Under CI_AI, all three rows match. Under CI_AS, Jose and JOSE match, but José does not.
The difference comes from comparison semantics. The stored values remain unchanged. A broader match doesn't give permission to normalize everyone's displayed name to one spelling.
I use this small comparison before designing the index. It makes the business behavior visible with minimal schema work. Add the relevant case-sensitive test too if that is the requirement.
Accent and case rules are independent dimensions. Ignoring accents while preserving case needs a CS_AI choice. Don't copy a CI_AI option from another screen.
DECLARE @Names TABLE(DisplayName nvarchar(60));
INSERT @Names VALUES (N'Jose'),(N'José'),(N'JOSE');
SELECT DisplayName FROM @Names
WHERE DisplayName COLLATE Latin1_General_100_CI_AI = N'Jose';
SELECT DisplayName FROM @Names
WHERE DisplayName COLLATE Latin1_General_100_CI_AS = N'Jose';
Give Frequent Accent-Insensitive Searches Their Own Column
A COLLATE expression on the original column can prevent the existing index from supplying the desired seek. That index orders values under its own collation. For a frequent lookup, add a computed search column with the chosen rule.
Preserve the display column under its original rules. The two columns represent two requirements, making the distinction clear in the schema instead of hiding it in every query.
Create an index on the computed column and query that column directly. The sample uses a bounded nvarchar value appropriate for an index key. Required SET options matter for indexed computed columns.
Include them in deployment and connection checks. The engine must be able to use the indexed expression under supported settings. An index name in the catalog doesn't establish that a request actually used it.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
CREATE TABLE dbo.AccentSearchDemo
(
PersonId int NOT NULL PRIMARY KEY,
DisplayName nvarchar(100) COLLATE Latin1_General_100_CI_AS NOT NULL,
SearchName AS (DisplayName COLLATE Latin1_General_100_CI_AI) PERSISTED
);
CREATE INDEX IX_AccentSearchDemo_SearchName ON dbo.AccentSearchDemo(SearchName);
INSERT dbo.AccentSearchDemo(PersonId,DisplayName) VALUES (1,N'José'),(2,N'Jose');Verify the Accent-Insensitive Access Path
Use a parameter with the same Unicode type as the search column. Ask for an actual plan and inspect the access predicate. A small sample can legitimately scan.
The purpose is confirming that the intended expression and index are available, then measuring representative requests at realistic scale. Don't force a seek for a demonstration where reading the whole sample is cheaper.
Compare equality and prefix searches separately. A leading wildcard asks for a different access pattern. The correct collation doesn't turn it into a simple prefix seek.
Index design follows the predicate as well as the text rules. Keep that distinction in performance discussions. A tolerant spelling match doesn't provide a free full-text search engine inside a regular B-tree.
DECLARE @Search nvarchar(100) = N'Jose';
SELECT PersonId, DisplayName
FROM dbo.AccentSearchDemo
WHERE SearchName = @Search;Review Uniqueness Under the Broader Rule
Two display names can compare equal under the search collation. That is normally fine for a name lookup. It becomes important if you add a unique index to the computed column.
The broader comparison can reject values previously treated as distinct. Check existing values and the business identity rule before imposing uniqueness. A search label isn't automatically a person's identifier.
What should the application do when several names match? Return candidates with stable identifiers and enough permitted context to choose. Don't silently take TOP one. Matching Jose with José improves recall, but it doesn't establish identity.
The query still needs a clear duplicate-handling rule. The accent is small. The difference between a search result and an authorization decision is considerably larger.
Keep the Display Value Intact
Test names with accents, case differences, and characters relevant to your actual users. Retain the display spelling through inserts, updates, and exports. Document the selected search collation and keep it consistent across joins.
A schema or connection change can reintroduce conflicting comparison rules. That is easier to diagnose when the search contract has one explicit name and a focused index.
Use accent-insensitive matching where the user expects it, then verify both correctness and access paths. Avoid changing the entire database to fix one lookup without reviewing the wider effects. A computed column makes the targeted decision visible.
The search can be forgiving while the stored name remains accurate. Preserve the customer's spelling and the application's lookup rule.
Include a join between the search value and any staging input in the review. Both expressions need compatible rules. A correctly indexed column can still meet an implicit conversion from a mismatched parameter. Test the application's actual parameter binding instead of approving only the literal example in the query window.
Related reading on this blog: Change Database and Table Collation: SQL in Sixty Seconds #145 and Fixing Collation Conflicts Between tempdb and Your Database.
A tolerant name search is not a spelling correction, it is an explicit comparison rule.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
