This problem can be solved using accent insensitive collations.
Your database is probably using a AS (Accent Sensitive) collation so by default it will search for the exact match including accents.
You could instruct the WHERE clause to use another collation than the database default by specifying a collation with the comparison.
In this dbfiddle I created an example using the LATIN1 collations but you could use the same approach with the collation you are using by just changing AS into AI for the collation your column is currently using.
Use the Accent Insensitive collation that matches the collation the colummn is using. For example if the column is using
SQL_Latin1_General_CP1_CI_AI and not
Latin1_General_100_CI_AS or any of the variations of those two since the behavior of the non-SQL_ collations will differ in more ways than just accent-insensitivity, and that might not be expected by users.
You can check the current collation in
CREATE TABLE testaccent (name nvarchar(50));
INSERT INTO testaccent (name) VALUES ('Millière') , ('Milliere');
-- returns Miliere
SELECT * FROM testaccent WHERE name = 'Milliere';
-- returns both
SELECT * FROM testaccent WHERE name='Milliere' COLLATE Latin1_General_CI_AI
--only returns Miliere
SELECT * FROM testaccent WHERE name='Milliere' COLLATE Latin1_General_CI_AS
Read through Using SQL Server Collations for more information.
Then again you'd probably want sorting to use this collation (as peufeu noted in the comments) to ensure that "é" sorts with "e". Otherwise, someone who paginates through results in alphabetical order would be surprised not to find the "é" where they expect them to be, but if you only want to touch this query you can add the
COLLATE clause to the
ORDER BY too.
As noted by Solomon Rutzky in the comments, if this only affects 1 or a few columns, another option is to create a non-persisted computed column that simply repeats the "name" column and provides the accent insensitive collation, and then index the computed column. This avoids the scan caused by changing the collation within the query. Then the query needs to filter on the new column.
dbo.[table_name] ADD [SearchName] datatype_of_name_column
AS ([Name] COLLATE LATIN1_GENERAL_100_CI_AI));
CREATE INDEX [IX_table_name_SearchName]
ON dbo.[table_name] ([SearchName] ASC);
Or you could also create a view instead of adding a computed column (as jyao prefers).