SQL Server:何时使用排序规则和nvarchar [英] SQL Server : when to use collation and nvarchar

问题描述

当前，我的SQL Server表中的列数据类型为varchar.

Currently my column datatype is varchar in my SQL Server table.

我想在我的栏中同时存储英文和中文字符.

I want to store both English and Chinese characters in my column.

使用排序规则必须遵循哪些步骤，或者必须将数据类型更改为NVARCHAR并以N'作为unicode插入?

What steps do I have to follow to use collation, or do I have to change the datatype to NVARCHAR and insert with N' as unicode?

如果我必须使用排序规则，应该使用哪种排序规则.

If I have to use collation what collation I should use.

请帮助我

推荐答案

您正在混合两个概念:

• 数据类型和编码

VARCHAR将数据存储在8位块中.基本字符是一个块.但是有时会有一个特殊的块告诉引擎，这个字符必须被解释为两个字节的字符.如果您使用字符集截然不同的语言，甚至会发现需要三个字节的字符.

VARCHAR stores your data in chunks of 8 bit. basic characters are one chunk. But sometimes there is one special chunk telling the engine, that this character has to be interpreted as a two-byte character. If you get in touch with languages with a very differing char-set you'll find even characters which need three bytes.

NVARCHAR将每个字符存储为16位(或2字节)的UniCode.这样就可以对所有字符进行单一编码，而无需任何技巧.

NVARCHAR stores each character as UniCode 16-bit (or 2-byte). This allows one single encoding for all characters, no tricks needed.

• 整理

排序规则用于字符串比较，并且当您在JOIN s内的WHERE中或在索引中处理字符串值并用于排序时使用.

The Collation is used for string comparisons and is used when you deal with string values in WHERE within JOINs or in indexes and for sorting.

SQL Server具有默认排序规则，该排序规则与新数据库一起使用，并且-非常重要！-在临时表中.

SQL Server has a default collation which is used with new databases and - very important! - within your temp table.

允许在数据库级别定义不同的默认排序规则，但是如果对排序规则不相同的temp表运行查询，则可能导致严重的问题.

It is allowed to define a different default collation on database level, but this can lead to severe problems, if you run queries against the temp table where the collation is not the same.

您也可以在列级别定义排序规则.

You are allowed to define the collation on column level too.

甚至在语句的每一列中也可以定义排序规则.这是最高的控制等级，但是意味着很多打字和非常难读的代码...

And you are allowed to define the collation even within your statements for each column separately. This is the highest grade of control but means a lot of typing and very hard-to-read code...

如果要将英语和中文存储在一列中，则必须使用NVARCHAR.没有适合所有内容排序规则，您必须尝试这样做.

If you want to store English and Chinese in one column you must use NVARCHAR. There is no fitting-to-everything collation, this you must try.

您可以使用正确的配置将字符串存储在边表中，并将其绑定到查询中...

You might store your strings in a side table with proper configuration and bind it within your queries...

对于英文和中文字符串，您应该使用不同的列.甚至每种语言都有一个单独的边表...这使您可以分别为每种列/语言设置最佳排序规则.而且，它使在多语言环境中添加新语言变得容易.

You should use different columns for English and Chinese strings. Or even one separate side table for each language... This allows you to set the best collation for each column/language separately. And it makes it easy to add new languages in a multi-language environment.

这篇关于SQL Server:何时使用排序规则和nvarchar的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！