Chữ tiếng Việt, collation và tìm không dấu

Khách gõ "nguyen thi anh" vào ô tìm kiếm và không thấy ai, dù bảng có khách tên Nguyễn Thị Ánh. Kiểu cột, tiền tố N và collation quyết định chữ có dấu được lưu đúng không và hai chuỗi có được coi là bằng nhau không. Đọc xong, bạn lưu đúng chữ tiếng Việt, biết collation nào coi chữ nào bằng nhau, và dựng ô tìm không dấu vẫn dùng Index Seek.

1. Chuỗi và chữ tiếng Việt #

n tối đa 8.000 với char/varchar và 4.000 với nchar/nvarchar. Kiểu (max) chứa tới 2 GB và đẩy giá trị lớn sang LOB_DATA như chương lưu trữ đã mô tả.

Vietnamese_100_CI_AS, collation của database BanHang, dùng code page 1258 cho char và varchar. Code page này có sẵn một số chữ như Á, Â, Ê, Đ, nhưng phần lớn chữ có dấu thanh như ễ, ị không có dạng một byte dựng sẵn. Vì vậy varchar không UTF-8 không chứa trọn tên tiếng Việt, và BanHang dùng nvarchar cho mọi cột chữ tiếng Việt.

Từ SQL Server 2019, collation có hậu tố _UTF8, như Vietnamese_100_CI_AS_SC_UTF8, biến char và varchar thành kiểu Unicode mã hóa UTF-8. Hậu tố này chỉ gắn được vào collation Windows hỗ trợ ký tự bổ sung, như các collation _SC.

Một tên, ba cách lưu #

Nguyễn Thị Ánh có 14 ký tự ở dạng dựng sẵn: 11 ký tự ASCII (N g u y n, T h, n h và hai dấu cách) và 3 chữ có dấu. UTF-16 tốn 2 byte cho mọi ký tự ở đây: 14 × 2 = 28 byte. UTF-8 tốn 1 byte cho ký tự ASCII, 2 byte cho Á (U+00C1), 3 byte cho ễ (U+1EC5) và ị (U+1ECB): 11 + 2 + 3 + 3 = 19 byte.

Đo bằng LEN và DATALENGTH trên một bảng tạm có một cột varchar(60) collation Vietnamese_100_CI_AS_SC_UTF8 và một cột nvarchar(60):

CREATE TABLE #Ten (
    TenUtf8 varchar(60) COLLATE Vietnamese_100_CI_AS_SC_UTF8 NOT NULL,
    TenUtf16 nvarchar(60) NOT NULL
);

INSERT #Ten VALUES
    (N'Nguyễn Thị Ánh', N'Nguyễn Thị Ánh'),
    (N'Trương Đức Hiếu', N'Trương Đức Hiếu');

SELECT
    TenUtf16,
    LEN(TenUtf8) AS so_ky_tu,
    DATALENGTH(TenUtf8) AS byte_utf8,
    DATALENGTH(TenUtf16) AS byte_utf16
FROM #Ten;

DROP TABLE #Ten;

UTF-8 tiết kiệm nhờ phần ASCII. Phần lớn chữ mang dấu thanh nằm trong khối U+1EA0 đến U+1EF9, tốn 3 byte UTF-8 so với 2 byte UTF-16, nên chuỗi dày chữ có dấu có thể lớn hơn ở UTF-8. Ba hệ quả khi dùng UTF-8:

• varchar(n) đếm byte. varchar(10) UTF-8 không chứa được Nguyễn Thị (10 ký tự, 14 byte). INSERT báo String or binary data would be truncated.
• Biến varchar luôn mang collation mặc định của database. Trên BanHang, DECLARE @Ten varchar(60) vẫn là code page 1258 dù cột đích là UTF-8, nên chữ mất ngay ở biến.
• Một database trộn cột UTF-8 và cột nvarchar phải đổi mã mỗi lần so sánh chéo. Chọn UTF-8 thì chọn ở mức database, từ đầu.

BanHang giữ nvarchar cho chữ tiếng Việt. Cột chỉ chứa ASCII như MaSanPham và SoDienThoai dùng varchar.

Tiền tố N #

Chuỗi '...' không có N là varchar theo code page của database hiện tại. Engine đổi chuỗi sang code page đó ngay lúc phân tích câu lệnh, trước khi giá trị tới cột nvarchar.

SELECT
    'Nguyễn Thị Ánh' AS khong_N,
    N'Nguyễn Thị Ánh' AS co_N,
    CASE WHEN 'Nguyễn Thị Ánh' = N'Nguyễn Thị Ánh'
         THEN N'bằng' ELSE N'khác' END AS so_sanh;

Trên BanHang, ễ và ị thành ?, còn Á vẫn còn vì code page 1258 có nó. Database dùng code page khác cho kết quả khác từng ký tự, nên đừng dựa vào việc "chữ này vẫn còn". WHERE Ten = 'Nguyễn Thị Ánh' không tìm thấy ai. UPDATE ... SET Ten = '...' ghi dấu ? vĩnh viễn. Literal tiếng Việt luôn viết N'...'.

LEN và DATALENGTH #

LEN đếm ký tự và bỏ dấu cách cuối. DATALENGTH đếm byte, kể cả dấu cách cuối. LEN('SP-00042 ') là 8, DATALENGTH là 10. Phép so sánh = cũng bỏ qua dấu cách cuối, nên 'SP-00042' = 'SP-00042 ' là đúng.

Cùng một chữ có thể đến ở dạng tổ hợp: ễ là e cộng hai dấu rời U+0302 và U+0303. LEN(N'Nguyễn') dạng tổ hợp ra 8 thay vì 6. Collation Vietnamese_100_CI_AS coi hai dạng là bằng nhau, collation nhị phân (_BIN2) thì không. Giới hạn độ dài và chỉ mục tính trên số đơn vị lưu trữ, nên chuẩn hóa về dạng dựng sẵn ở tầng ứng dụng trước khi ghi.

2. Collation: so sánh, sắp xếp và tìm không dấu #

Collation quyết định ba việc: ký tự nào bằng nhau, thứ tự sắp xếp, và code page cho char/varchar. Trong Vietnamese_100_CI_AS, Vietnamese là luật ngôn ngữ, 100 là phiên bản bảng sắp xếp có từ SQL Server 2008, CI là không phân biệt hoa thường, AS là phân biệt dấu.

Các hậu tố khác: CS phân biệt hoa thường, AI không phân biệt dấu, KS và WS phân biệt kana và độ rộng ký tự, SC hiểu ký tự ngoài U+FFFF như một ký tự, UTF8 mã hóa char/varchar bằng UTF-8, BIN2 so theo mã điểm Unicode thay vì theo ngôn ngữ.

Collation Windows (như Vietnamese_100_CI_AS) so varchar và nvarchar bằng cùng một thuật toán. Collation SQL (tiền tố SQL_, như SQL_Latin1_General_CP1_CI_AS) dùng luật khác nhau cho dữ liệu Unicode và không Unicode. Điểm này quyết định một tham số nvarchar có làm mất seek trên cột varchar hay không, ở bài ngày giờ và kiểu tham số.

Thứ tự chữ cái tiếng Việt #

SELECT STRING_AGG(v, N', ') WITHIN GROUP (ORDER BY v COLLATE Vietnamese_100_CI_AS) AS thu_tu
FROM (VALUES (N'Ánh'), (N'An'), (N'Ăn'), (N'Ân'), (N'Bình'), (N'Dũng'), (N'Đức'),
             (N'Duy'), (N'Em'), (N'Ơn'), (N'Ông'), (N'Oanh'), (N'Út'), (N'Ưng'),
             (N'Uyên')) AS t (v);

Collation tiếng Việt xếp đúng bảng chữ cái: a, ă, â; d, đ; o, ô, ơ; u, ư. Dấu thanh là khác biệt nhỏ hơn khác biệt chữ cái, nên Ánh đứng cạnh An. Collation Latin coi Đ là D có dấu và xếp Đức trước Dũng. Danh sách khách cho người Việt đọc sắp theo Vietnamese_100.

AI trong collation tiếng Việt không bỏ hết dấu #

Chữ tiếng Việt có hai loại dấu. Dấu thanh (sắc, huyền, hỏi, ngã, nặng) là dấu. Mũ, trăng, móc và gạch ngang (â ă ê ô ơ ư đ) tạo ra chữ cái riêng. Vietnamese_100_CI_AI tôn trọng bảng chữ cái đó: nó bỏ qua dấu thanh, không bỏ qua chữ cái.

SELECT
    CASE WHEN N'Nguyễn' = N'nguyen' COLLATE Vietnamese_100_CI_AI THEN N'bằng' ELSE N'khác' END AS vi_nguyen,
    CASE WHEN N'Nguyễn' = N'nguyên' COLLATE Vietnamese_100_CI_AI THEN N'bằng' ELSE N'khác' END AS vi_nguyen_mu,
    CASE WHEN N'Nguyễn' = N'nguyen' COLLATE Latin1_General_100_CI_AI THEN N'bằng' ELSE N'khác' END AS latin_nguyen,
    CASE WHEN N'Đức' = N'duc' COLLATE Vietnamese_100_CI_AI THEN N'bằng' ELSE N'khác' END AS vi_duc,
    CASE WHEN N'Đức' = N'duc' COLLATE Latin1_General_100_CI_AI THEN N'bằng' ELSE N'khác' END AS latin_duc;

Kiểm trên đủ 67 chữ có dấu của tiếng Việt (66 nguyên âm có dấu và đ), cả chữ thường lẫn chữ hoa:

Ô tìm kiếm cho người gõ không dấu vì vậy cần Latin1_General_100_CI_AI.

Tìm "nguyen" ra "Nguyễn" mà vẫn seek #

WHERE Ten COLLATE Latin1_General_100_CI_AI LIKE N'nguyen%' trả đúng dòng nhưng quét. Cây B-tree của một chỉ mục trên Ten được sắp theo Vietnamese_100_CI_AS. So sánh theo collation khác thì thứ tự đó vô dụng: kế hoạch là Clustered Index Scan, hoặc Index Scan nếu có chỉ mục trên Ten, với CONVERT(nvarchar(200), [Ten]) ở vị từ.

Cách sửa là cột tính toán mang sẵn collation tìm kiếm, rồi đặt chỉ mục lên cột đó. Biểu thức Ten COLLATE ... là xác định và chính xác (COLUMNPROPERTY trả IsDeterministic = 1, IsPrecise = 1), nên chỉ mục được mà không cần PERSISTED.

dbo.KhachHang có hai ràng buộc làm việc này dài hơn bảng thường. Bảng temporal không nhận cột tính toán khi SYSTEM_VERSIONING = ON (lỗi 13724). Security policy của chương kỹ thuật được tạo với schema binding, nên chặn cả lệnh tắt versioning (lỗi 3729), và STATE = OFF không đủ để gỡ chặn. Thứ tự đã chạy thử: xóa policy, tắt versioning, thêm cột tính toán vào bảng chính và cột thường vào bảng lịch sử, bật lại versioning, tạo lại policy, rồi tạo chỉ mục.

DROP SECURITY POLICY dbo.pol_KhachHang;
GO

ALTER TABLE dbo.KhachHang SET (SYSTEM_VERSIONING = OFF);
GO

ALTER TABLE dbo.KhachHang
    ADD TenKhongDau AS (Ten COLLATE Latin1_General_100_CI_AI);

ALTER TABLE dbo.KhachHang_LichSu
    ADD TenKhongDau nvarchar(200) COLLATE Latin1_General_100_CI_AI NULL;
GO

UPDATE dbo.KhachHang_LichSu SET TenKhongDau = Ten;

ALTER TABLE dbo.KhachHang_LichSu
    ALTER COLUMN TenKhongDau nvarchar(200) COLLATE Latin1_General_100_CI_AI NOT NULL;
GO

ALTER TABLE dbo.KhachHang SET (
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.KhachHang_LichSu)
);
GO

CREATE SECURITY POLICY dbo.pol_KhachHang
ADD FILTER PREDICATE dbo.fn_KhachHang_ChiNhanh (ChiNhanhId)
    ON dbo.KhachHang
WITH (STATE = ON);
GO

CREATE INDEX IX_KhachHang_TenKhongDau
    ON dbo.KhachHang (TenKhongDau);
GO

Bảng lịch sử nhận một cột thường, cùng kiểu, cùng collation và cùng tính NOT NULL với cột tính toán. Lệch collation báo lỗi 13526, lệch nullability báo lỗi 13531, và versioning không bật lại được. Sau lần sửa tên kế tiếp, dòng lịch sử mang giá trị TenKhongDau của phiên bản cũ.

Tìm theo tên không dấu:

EXEC sys.sp_set_session_context @key = N'ChiNhanhId', @value = 3;

SELECT KhachHangId, Ten
FROM dbo.KhachHang
WHERE TenKhongDau = N'nguyen thi anh';

Kết quả là các khách chi nhánh 3 tên Nguyễn Thị Ánh. Kế hoạch là Index Seek trên IX_KhachHang_TenKhongDau, rồi lookup về PK_KhachHang, nơi bộ lọc chi nhánh được áp. Khi chỉ mục đã có, câu viết dạng WHERE Ten COLLATE Latin1_General_100_CI_AI = N'nguyen thi anh' cũng được trình tối ưu khớp sang cột tính toán và seek.

LIKE N'nguyen thi anh%' seek được khi tiền tố đủ hẹp. Mỗi dòng khớp tốn một lookup, và bộ lọc chi nhánh chỉ áp sau lookup, nên khách mọi chi nhánh có tên khớp đều bị đọc. Trên bản thử 200.000 khách, một tiền tố khớp khoảng 1,2% số khách đã đủ để trình tối ưu chọn quét PK_KhachHang. Tìm theo đuôi tên (LIKE N'%anh') luôn quét. Ngưỡng giữa seek kèm lookup và quét ở Chỉ mục B-tree, seek, scan và key lookup.

Bảng tạm và lỗi xung đột collation #

Bảng tạm #... nằm trong tempdb và lấy collation của tempdb, tức collation của instance. Instance cài với SQL_Latin1_General_CP1_CI_AS còn BanHang là Vietnamese_100_CI_AS thì phép nối giữa bảng tạm và bảng thật thất bại:

CREATE TABLE #MaCanTim (MaSanPham varchar(20) NOT NULL);
INSERT #MaCanTim VALUES ('SP-00042'), ('SP-00043');

SELECT s.SanPhamId, s.MaSanPham
FROM dbo.SanPham AS s
JOIN #MaCanTim AS t
    ON t.MaSanPham = s.MaSanPham;

Msg 468, Level 16, State 9
Cannot resolve the collation conflict between "Vietnamese_100_CI_AS" and
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Sửa bằng cách khai báo cột bảng tạm theo collation của database hiện tại: MaSanPham varchar(20) COLLATE DATABASE_DEFAULT NOT NULL. Xóa bảng tạm cũ trong một batch riêng trước khi tạo lại: nếu cùng batch, câu SELECT được biên dịch theo bảng tạm cũ còn tồn tại và vẫn báo 468.

Bảng tạm tạo bằng SELECT ... INTO #t FROM dbo.SanPham giữ collation của cột nguồn. Biến bảng DECLARE @t TABLE (...) lấy collation của database hiện tại. Cả hai không gặp lỗi này. Kiểm tra collation trước khi sửa script bằng SERVERPROPERTY('Collation'), DATABASEPROPERTYEX(N'tempdb', 'Collation') và DATABASEPROPERTYEX(N'BanHang', 'Collation').

3. Áp dụng trong .NET #

Ba việc ở tầng ứng dụng: chuẩn hóa chữ trước khi ghi, hiểu rằng so sánh "bỏ dấu" của .NET cũng phụ thuộc văn hóa như collation, và khai báo cột tìm không dấu trong model EF Core để migration tạo đúng cột và chỉ mục.

Chữ dán từ PDF hay gõ trên macOS có thể ở dạng tổ hợp. string.Normalize() mặc định đưa về dạng dựng sẵn NFC trước khi chuỗi tới database:

var toHop = "Nguyễn".Normalize(NormalizationForm.FormD);   // e + U+0302 + U+0303: Length 8
khach.Ten = toHop.Normalize(NormalizationForm.FormC);        // dựng sẵn: Length 6

// So sánh bỏ dấu và hoa thường trong .NET cũng theo luật ngôn ngữ, như collation
var boQuaDau = CompareOptions.IgnoreCase | CompareOptions.IgnoreNonSpace;
CultureInfo.GetCultureInfo("vi-VN").CompareInfo.Compare("Nguyễn", "nguyen", boQuaDau);   // khác 0
CultureInfo.InvariantCulture.CompareInfo.Compare("Nguyễn", "nguyen", boQuaDau);         // 0

// EF Core: cột tính toán mang collation tìm kiếm, có chỉ mục
e.Property(k => k.TenKhongDau)
    .HasMaxLength(200)
    .HasComputedColumnSql("[Ten] COLLATE Latin1_General_100_CI_AI");
e.HasIndex(k => k.TenKhongDau).HasDatabaseName("IX_KhachHang_TenKhongDau");

var ketQua = await db.KhachHang.Where(k => k.TenKhongDau == tuKhoa).ToListAsync();

Chương trình đầy đủ chạy bằng dotnet run KhongDau.cs trên .NET 10.0.12, Microsoft.EntityFrameworkCore.SqlServer 10.0.12, Dapper 2.1.89, SQL Server 2019 LocalDB, Intel Core Ultra 5 125U. Nó tạo bảng từ model, nạp 200.000 khách có tên ghép ngẫu nhiên (hạt giống 42), rồi đọc kế hoạch từ plan cache. Bảng thử không có temporal và row-level security như dbo.KhachHang thật. Dòng #:property PublishAot=false tắt AOT mà file-based app bật mặc định, vì EF Core và Dapper sinh code lúc chạy.

vi-VN của .NET hành xử như Vietnamese_100_CI_AI, còn InvariantCulture như Latin1_General_100_CI_AI. Lọc danh sách khách trong bộ nhớ theo từ khóa không dấu thì dùng InvariantCulture với IgnoreNonSpace, như cột TenKhongDau ở database. EF Core sinh WHERE [k].[TenKhongDau] = @tuKhoa với tham số nvarchar(200) lấy từ HasMaxLength(200), và EF.Functions.Collate cũng được khớp sang cột tính toán.

#:package Microsoft.EntityFrameworkCore.SqlServer@10.0.12
#:package Dapper@2.1.89
#:property PublishAot=false
// Chữ tiếng Việt ở tầng .NET: chuẩn hóa NFC trước khi ghi, so sánh theo văn hóa,
// và cột tính toán TenKhongDau khai báo trong EF Core để tìm không dấu bằng Index Seek.
using System.Data;
using System.Globalization;
using System.Text;
using System.Xml.Linq;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

// ---------- 1. Chuẩn hóa và so sánh trong C# ----------
var toHop = "Nguyễn".Normalize(NormalizationForm.FormD);   // ễ thành e + U+0302 + U+0303, như chữ dán từ PDF hay macOS
var dungSan = toHop.Normalize(NormalizationForm.FormC);
Console.WriteLine($"Tổ hợp: Length {toHop.Length}; sau Normalize(FormC): Length {dungSan.Length}; " +
                  $"Ordinal bằng nhau: {string.Equals(toHop, "Nguyễn", StringComparison.Ordinal)} -> {string.Equals(dungSan, "Nguyễn", StringComparison.Ordinal)}");

var boQuaDau = CompareOptions.IgnoreCase | CompareOptions.IgnoreNonSpace;
var vi = CultureInfo.GetCultureInfo("vi-VN").CompareInfo;
var inv = CultureInfo.InvariantCulture.CompareInfo;
foreach (var (a, b) in new[] { ("Nguyễn", "nguyen"), ("Nguyễn", "nguyên"), ("Đức", "duc") })
    Console.WriteLine($"{a} = {b}: vi-VN {vi.Compare(a, b, boQuaDau) == 0}, Invariant {inv.Compare(a, b, boQuaDau) == 0}");

// ---------- 2. EF Core: cột tính toán có chỉ mục ----------
using (var db = new BanHangDb())
{
    var ddl = db.Database.GenerateCreateScript();
    Console.WriteLine(ddl.Trim());
    using var c0 = new SqlConnection(Db.Cs);
    c0.Execute("DROP TABLE IF EXISTS dbo.KhachHang;");
    foreach (var lo in ddl.Split("GO", StringSplitOptions.RemoveEmptyEntries)) if (lo.Trim().Length > 0) c0.Execute(lo);
}

// 200.000 khách, tên ghép từ họ, đệm, tên phổ biến (hạt giống 42).
string[] ho = ["Nguyễn", "Trần", "Lê", "Phạm", "Hoàng", "Huỳnh", "Phan", "Vũ", "Võ", "Đặng", "Bùi", "Đỗ", "Hồ", "Ngô", "Dương", "Lý"];
string[] dem = ["Thị", "Văn", "Đức", "Minh", "Ngọc", "Thanh", "Quốc", "Hữu", "Thu", "Gia", "Bảo", "Xuân"];
string[] ten = ["Ánh", "An", "Bình", "Dũng", "Đức", "Hiếu", "Hương", "Lan", "Long", "Mai", "Minh", "Nam", "Ngọc", "Phúc",
                "Quân", "Sơn", "Thảo", "Trang", "Tuấn", "Uyên", "Vy", "Yến", "Hải", "Khoa", "Linh", "Oanh", "Ơn", "Ưng"];
var rnd = new Random(42);
var dt = new DataTable();
dt.Columns.Add("KhachHangId", typeof(int));
dt.Columns.Add("ChiNhanhId", typeof(int));
dt.Columns.Add("Ten", typeof(string));
for (var i = 1; i <= 200_000; i++)
    dt.Rows.Add(i, rnd.Next(1, 121), $"{ho[rnd.Next(ho.Length)]} {dem[rnd.Next(dem.Length)]} {ten[rnd.Next(ten.Length)]}");
using (var c1 = new SqlConnection(Db.Cs))
{
    c1.Open();
    using var bulk = new SqlBulkCopy(c1) { DestinationTableName = "dbo.KhachHang" };
    foreach (DataColumn col in dt.Columns) bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
    bulk.WriteToServer(dt);
    c1.Execute("UPDATE STATISTICS dbo.KhachHang WITH FULLSCAN; ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;");
}

var tuKhoa = "nguyen thi anh";
using (var db = new BanHangDb())
{
    var q1 = db.KhachHang.Where(k => k.TenKhongDau == tuKhoa);
    var q2 = db.KhachHang.Where(k => EF.Functions.Collate(k.Ten, "Latin1_General_100_CI_AI") == tuKhoa);
    var q3 = db.KhachHang.Where(k => k.Ten == tuKhoa);
    Console.WriteLine(q1.ToQueryString());
    Console.WriteLine($"TenKhongDau == \"{tuKhoa}\": {q1.Count()} khách; ví dụ: {q1.Select(k => k.Ten).First()}");
    Console.WriteLine($"EF.Functions.Collate(Ten, ...) == ...: {q2.Count()} khách");
    Console.WriteLine($"Ten == \"{tuKhoa}\" (Vietnamese_100_CI_AS): {q3.Count()} khách");
}

using var conn = new SqlConnection(Db.Cs);
foreach (var row in conn.Query<(string Text, long Reads, string Plan)>("""
    SELECT st.text, qs.last_logical_reads, CAST(qp.query_plan AS nvarchar(max))
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
    WHERE pa.attribute = 'dbid' AND CAST(pa.value AS int) = DB_ID()
      AND st.text LIKE N'%COUNT(*)%FROM `[KhachHang`]%' ESCAPE N'`'
    ORDER BY qs.creation_time;
    """))
{
    XNamespace ns = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
    var xml = XDocument.Parse(row.Plan);
    var ops = xml.Descendants(ns + "RelOp")
        .Select(r => $"{r.Attribute("PhysicalOp")?.Value}{(r.Descendants(ns + "Object").FirstOrDefault()?.Attribute("Index")?.Value is string ix ? " " + ix : "")}")
        .Where(o => o.Contains("Index")).Distinct();
    Console.WriteLine($"{row.Text[(row.Text.IndexOf("WHERE"))..].ReplaceLineEndings(" ")}\n    -> {string.Join(", ", ops)}, {row.Reads} logical reads");
}

class KhachHang
{
    public int KhachHangId { get; set; }
    public int ChiNhanhId { get; set; }
    public string Ten { get; set; } = "";
    public string TenKhongDau { get; private set; } = "";
}

class BanHangDb : DbContext
{
    public DbSet<KhachHang> KhachHang => Set<KhachHang>();
    protected override void OnConfiguring(DbContextOptionsBuilder o) => o.UseSqlServer(Db.Cs);
    protected override void OnModelCreating(ModelBuilder b) => b.Entity<KhachHang>(e =>
    {
        e.ToTable("KhachHang");
        e.Property(k => k.KhachHangId).ValueGeneratedNever();
        e.Property(k => k.Ten).HasMaxLength(200);
        e.Property(k => k.TenKhongDau)
            .HasMaxLength(200)
            .HasComputedColumnSql("[Ten] COLLATE Latin1_General_100_CI_AI");
        e.HasIndex(k => k.TenKhongDau).HasDatabaseName("IX_KhachHang_TenKhongDau");
    });
}

static class Db
{
    public const string Cs = @"Server=(localdb)\MSSQLLocalDB;Database=Kumeo_kieudulieu;Integrated Security=true;TrustServerCertificate=true";
}

Những chỗ hay hiểu sai #

• "Collation AI bỏ hết dấu." Vietnamese_100_CI_AI chỉ bỏ dấu thanh. ă â ê ô ơ ư đ là chữ cái riêng. Latin1_General_100_CI_AI mới coi Nguyễn bằng nguyen.
• "nvarchar(200) chứa 200 ký tự, varchar(200) UTF-8 cũng vậy." n đếm cặp byte với nvarchar và byte với varchar. Tiếng Việt UTF-8 có chữ tốn 3 byte.
• "Cột nvarchar thì literal không cần N." Literal không N bị đổi sang code page của database trước khi tới cột, và ễ, ị thành ?.
• "IgnoreNonSpace trong .NET luôn bỏ hết dấu." Với vi-VN, Nguyễn vẫn khác nguyen; chỉ InvariantCulture coi chúng bằng nhau.

Kết luận #

Chữ tiếng Việt đúng cần ba thứ cùng lúc: nvarchar, literal hay tham số Unicode, và collation hợp việc. Sắp xếp cho người đọc dùng Vietnamese_100, tìm không dấu dùng Latin1_General_100_CI_AI trên cột tính toán có chỉ mục.

Trong dự án .NET của bạn:

• Gọi Normalize(NormalizationForm.FormC) cho mọi chuỗi tên, địa chỉ trước khi ghi, ví dụ trong setter hoặc trong SaveChanges override.
• Khai báo HasComputedColumnSql("[Ten] COLLATE Latin1_General_100_CI_AI") và HasIndex cho cột tìm kiếm, rồi lọc bằng TenKhongDau == tuKhoa hoặc EF.Functions.Collate.
• Lọc trong bộ nhớ thì so bằng CultureInfo.InvariantCulture.CompareInfo với IgnoreCase | IgnoreNonSpace, không dùng vi-VN.
• Script tạo bảng tạm khai báo COLLATE DATABASE_DEFAULT cho cột chữ, và kiểm SERVERPROPERTY('Collation') trên máy mới trước khi chạy.

Đọc tiếp #

• Bài trước: Kiểu dữ liệu: byte trên page và kiểu cho tiền. Bài tiếp: Ngày giờ, múi giờ và kiểu tham số.
• Tham số nvarchar gặp cột varchar collation SQL thì mất seek: Chuyển kiểu ngầm.
• Seek, lookup và ngưỡng chuyển sang quét: Chỉ mục B-tree, seek, scan và key lookup.

Nguồn #

• Collation and Unicode support
• Indexes on computed columns
• EF Core: Generated values, computed columns
• EF Core: Collations and case sensitivity
• String.Normalize