Artikel ini disiapkan untuk siswa kursus "Pengembang MS SQL Server"
Saya ingin berbagi cerita dari salah satu proyek sebelumnya, yang menggambarkan bahwa Collation harus dipilih dengan sangat hati-hati. Dan tentang apa yang terjadi jika parameter ini dipilih secara salah, dan opsi apa yang ada untuk menyelesaikan masalah.
Pertama, pengantar kecil tentang apa itu Collation. Dalam SQL Server, parameter Collation memberi tahu server cara mengurutkan dan membandingkan baris. Misalnya, garis "Apple" dan "apel". Apakah mereka berbeda atau tidak? Itu tergantung pada Collation yang ditentukan. Jika register menjadi kurang atau kurang jelas, lalu apa yang harus dilakukan dengan contoh "pohon Natal" dan "pohon Natal"? Hitung mereka sama atau berbeda? Ini semua dalam Collation juga.
Kisah ini terjadi di proyek yang fungsinya sangat mirip dengan DropBox atau Google Drive. Ini memberikan kemampuan untuk mengelola folder dan file yang disinkronkan pada mesin yang berbeda, serta kemampuan bagi pengguna lain untuk memiliki akses ke folder yang disinkronkan ini.
Jadi, cerita dimulai dengan fakta bahwa pada server Prod ada 75-90% kesalahan dalam log (lihat tangkapan layar di bawah), dan tidak jelas dari mana asalnya, dan apa alasannya. Kesalahannya adalah: "ReadWrtLst tidak lengkap". Berikutnya adalah detail pengguna dan folder mereka.
Kode dengan cepat menemukan tempat yang menghasilkan kesalahan, tetapi kami tidak dapat memahami mengapa itu terjadi dan bagaimana memperbanyaknya. Hanya jelas bahwa kesalahan itu entah bagaimana terkait dengan fakta bahwa entah bagaimana pengguna berhasil buat folder lain dengan nama yang sama di OS Anda.
Kami telah mengumpulkan informasi tentang pengguna untuk siapa kesalahan ini dikeluarkan. Dan di sini kami menunggu kejutan pertama: dari jutaan pengguna sistem, hanya 50 kesalahan ini terjadi, dan 50 pengguna ini menghasilkan 90% dari log kesalahan. Karena situasinya tidak dapat direproduksi, kami memutuskan untuk menghubungi salah satu pengguna dan mencari tahu mengapa salah satu folder tidak disinkronkan dengannya. Folder itu tampak sama bagi kami dengan yang lain, satu-satunya perbedaan adalah folder itu disebut dalam bahasa pengguna menggunakan hieroglif. Dan penggunanya orang Jepang. Ngomong-ngomong, di antara 50 pengguna ini, Jepang adalah mayoritas.
Berkat salah satu pengembang tim, kami dapat mereproduksi kesalahan. Kesalahannya adalah bahwa sistem operasi menganggap nama folder berbeda, dan SQL Server menganggap mereka sama karena Collation yang dipilih.
Kolasi yang digunakan dalam proyek:
SQL_Latin1_General_CP1_CI_AS
Penyimpangan kecil tentang cara membaca Collation. (Jika Anda terbiasa dengan itu, jangan lewatkan.)
Jadi, ada beberapa bagian untuk Collation:
- SQL - opsi pengurutan oleh SQL Server (SQL pada awal Collation) atau Windows (maka itu hanya Latin1_ ...);
- Latin1_General - lokal atau bahasa yang digunakan;
- CP1 - halaman kode - halaman kode;
- CI - Insensitive Case - case insensitive;
- AS - Accent Sensitive - dengan mempertimbangkan akson atau diakritik, dengan kata lain, 'a' tidak dianggap sama dengan 'แบฅ'.
Collation ini dulunya Collation default ketika menginstal SQL Server.
Opsi apa yang ada?
- _KS - dengan mempertimbangkan karakter Jepang dari hiragana dan katakana, jika opsi tidak dipilih, maka SQL Server akan menafsirkan hieroglif dari hiragana dan katakana sebagai sama.
- _WS - dengan mempertimbangkan lebar karakter, jika parameter tidak dipilih, maka "Teks" dan "T ext" dianggap baris yang sama.
- _VSS - dengan mempertimbangkan tanda-tanda memilih opsi ejaan dalam bahasa Jepang, muncul dari versi 2017.
- _UTF8 - memungkinkan Anda untuk menyimpan data dalam UTF8.
Semua bidang teks dalam database menggunakan tipe NVARCHAR.
Ternyata, karena Collation saat ini mengabaikan perbedaan dalam pengejaan karakter Jepang dan perbedaan lebar karakter, SQL Server tidak membandingkan string dengan cara yang sama seperti sistem operasi, yang menyebabkan masalah, mis. pengguna dapat membuat folder, tidak dapat menambahkannya ke sistem untuk sinkronisasi. Hal yang sama akan terjadi nanti ketika membandingkan nama file.
Kami mulai memikirkan cara mengatasi masalah ini dan mengubah Collation.
Kolasi dapat diatur di beberapa tingkatan:
- Contoh SQL Server
- Basis data
- Meja
- Lapangan
Pada saat yang sama, tidak disarankan untuk memiliki Collation yang berbeda di dalam database, karena setiap kali ketika membandingkan baris dengan Collation yang berbeda, Anda perlu melakukan konversi menggunakan COLLATE, yang menunjukkan ke server mana urutan perbandingan yang harus digunakan.
Opsi apa yang ada dalam situasi di mana jelas bahwa Collation tidak dipilih dengan benar?
- Ubah Collation di tingkat DB;
- Ubah Kolasi di tingkat bidang (dalam kasus kami, tidak ada gunanya mengubah seluruh tabel);
- Tambahkan bidang Varbinary, di mana untuk menulis duplikat dari bidang dengan nama folder, dan gunakan untuk perbandingan;
- Beri tahu pengguna bahwa ada batasan pada dukungan karakter dalam nama direktori.
Opsi pertama - mengubah Collation di tingkat basis data - adalah yang paling sulit. Dalam hal basis data, akan perlu membuat ulang basis data dan memuat kembali data di sana. Karena sistem bekerja 24/7, opsi ini langsung ditolak.
Opsi kedua tentang mengubah bidang: opsi termudah untuk mengimplementasikannya adalah menambahkan bidang dengan Kolasi yang diinginkan dan mentransfer data di sana. Tetapi kemudian akan diperlukan untuk mengubah kode dalam database yang bekerja dengan bidang ini, dan ada banyak kode dalam database.
Kami paling menyukai opsi ketiga, karena secara teori ia membuat sedikit perubahan, karena bidang utama akan terus ada dengan Collation saat ini, dan kami tidak akan memiliki masalah dengan konversi, sementara semua fungsi yang diperlukan dalam bentuk akuntansi untuk alfabet Jepang atau luas karakter akan berfungsi. Kelemahannya adalah bahwa itu perlu untuk membuat perubahan pada bagian perangkat lunak, tetapi karena bagian server ini, ini bisa dilakukan.
Opsi keempat adalah yang paling sederhana dalam hal ini, karena jumlah total pengguna adalah beberapa juta, dan hanya 50 yang memiliki masalah.Namun, jika aplikasi tersebut secara aktif digunakan di Jepang, solusi ini akan sedikit digunakan.
Setelah mempresentasikan data ke manajemen, diputuskan untuk memberi tahu pengguna bahwa perangkat lunak tidak mendukung sejumlah karakter, dan ketika digunakan atas nama file dan folder yang disinkronkan, perangkat lunak mungkin tidak berfungsi dengan benar. Ini adalah solusi sementara, karena dengan distribusi lebih lanjut, jumlah pengguna yang menghadapi masalah serupa akan meningkat, dan perlu mengubah sesuatu menggunakan tiga opsi pertama.
Pilihan terbaik untuk memilih Collation didasarkan pada persyaratan aplikasi Anda. Jika Anda ingin SQL Server membandingkan string dengan cara yang sama seperti OS, maka Collation jelas salah secara default. Sayangnya, nuansa seperti itu jarang terlihat pada awal proyek ketika merancang suatu sistem, tetapi, semoga, setelah membaca artikel tersebut, Anda akan mengingat kembali situasi yang dijelaskan dan jangan menginjak garu sendiri.
Sumber Daya Kolasi yang Berguna:
https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017
https://www.red-gate.com/simple-talk/sql/sql-development/questions-sql-server-collations-shy-ask/
https://www.virtual-dba.com/sql-server-collation/