Home Database Mysql Tutorial SQL Server的排序规则(collation)冲突和解决方案

SQL Server的排序规则(collation)冲突和解决方案

Jun 07, 2016 pm 04:21 PM
server conflict sort rule solution

什么是排序规则(collation) 关于SQL Server的排序规则,估计大家都不陌生,在创建数据库时我们经常要选择一种排序规则(conllation),一般我们会留意到每一种语言的排序规则都有许多种,比如标准大陆简体中文Chinese_PRC的排序规则就有数十种之多。 这些排序

   什么是排序规则(collation)

  关于SQL Server的排序规则,估计大家都不陌生,在创建数据库时我们经常要选择一种排序规则(conllation),一般我们会留意到每一种语言的排序规则都有许多种,比如标准大陆简体中文Chinese_PRC的排序规则就有数十种之多。

SQL Server的排序规则(collation)冲突和解决方案  三联

  这些排序规则有什么作用呢?让我们先来看看MS官方的解释:

  排序规则指定了表示每个字符的位模式。它还指定了用于排序和比较字符的规则。排序规则具有下面的特征:

  语言

  区分大小写

  区分重音

  区分假名

  比如在SQL Server 2005中,排序规则名称由两部份构成,比如 Chinese_PRC_CI_AI_WS

  前半部份是指本排序规则所支持的字符集,,如Chinese_PRC 指针对大陆简体字UNICODE的排序规则。

  后半部份即后缀的含义如下:

_BIN                 指定使用向后兼容的二进制排序顺序。

_BIN2      指定使用 SQL Server 2005 中引入的码位比较语义的二进制排序顺序。

_Stroke   按笔划排序

_CI(CS) 是否区分大小写,CI不区分,CS区分

_AI(AS) 是否区分重音,AI不区分,AS区分

_KI(KS) 是否区分假名类型,KI不区分,KS区分

_WI(WS) 是否区分全半角,WI不区分,WS区分

  既然排序规则如此复杂,那么应用了不同排序规则的列之间默认情况下便不能进行Union、Join、Like等equal操作了,于是便有了排序规则(collation)冲突。

  排序规则(collation)冲突。

  我们知道,SQL Server 从2000 开始,便支持多个排序规则。SQL Server 2000 的数据库可使用除默认排序规则以外的其他排序规则。此外,SQL Server 2000 还支持为列专门制定排序规则。

  这样一来,我们在写跨表、跨数据库、跨服务器操作的T-SQL时,如果equal的字段排序规则不同,便会发生排序规则冲突。

  比如我们先见两个结构相同的表,但字段的排序规则不同:

  Copy to Clipboard

  -- 1. Create TableA.

  CREATE TABLE TagsTableA

  (

  TagName NVARCHAR(64) COLLATE Chinese_PRC_BIN

  )

  -- 2. Create TableB.

  CREATE TABLE TagsTableB

  (

  TagName NVARCHAR(64) COLLATE Chinese_PRC_CI_AS

  )

  当表建好之后执行:

  Copy to Clipboard

  -- 3. Try to join them

  SELECT * from TagsTableA A INNER JOIN TagsTableB B on A.TagName = B.TagName

  便会出下类似下面的问题:

  无法解决 equal to 操作中 "Chinese_PRC_BIN" 和 "Chinese_PRC_CI_AS" 之间的排序规则冲突。

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Solution for Win11 unable to install Chinese language pack Solution for Win11 unable to install Chinese language pack Mar 09, 2024 am 09:15 AM

Win11 is the latest operating system launched by Microsoft. Compared with previous versions, Win11 has greatly improved the interface design and user experience. However, some users reported that they encountered the problem of being unable to install the Chinese language pack after installing Win11, which caused trouble for them to use Chinese in the system. This article will provide some solutions to the problem that Win11 cannot install the Chinese language pack to help users use Chinese smoothly. First, we need to understand why the Chinese language pack cannot be installed. Generally speaking, Win11

An effective solution to solve the problem of garbled characters caused by Oracle character set modification An effective solution to solve the problem of garbled characters caused by Oracle character set modification Mar 03, 2024 am 09:57 AM

Title: An effective solution to solve the problem of garbled characters caused by Oracle character set modification. In Oracle database, when the character set is modified, the problem of garbled characters often occurs due to the presence of incompatible characters in the data. In order to solve this problem, we need to adopt some effective solutions. This article will introduce some specific solutions and code examples to solve the problem of garbled characters caused by Oracle character set modification. 1. Export data and reset the character set. First, we can export the data in the database by using the expdp command.

Oracle NVL function common problems and solutions Oracle NVL function common problems and solutions Mar 10, 2024 am 08:42 AM

Common problems and solutions for OracleNVL function Oracle database is a widely used relational database system, and it is often necessary to deal with null values ​​during data processing. In order to deal with the problems caused by null values, Oracle provides the NVL function to handle null values. This article will introduce common problems and solutions of NVL functions, and provide specific code examples. Question 1: Improper usage of NVL function. The basic syntax of NVL function is: NVL(expr1,default_value).

Implementing Machine Learning Algorithms in C++: Common Challenges and Solutions Implementing Machine Learning Algorithms in C++: Common Challenges and Solutions Jun 03, 2024 pm 01:25 PM

Common challenges faced by machine learning algorithms in C++ include memory management, multi-threading, performance optimization, and maintainability. Solutions include using smart pointers, modern threading libraries, SIMD instructions and third-party libraries, as well as following coding style guidelines and using automation tools. Practical cases show how to use the Eigen library to implement linear regression algorithms, effectively manage memory and use high-performance matrix operations.

Common causes and solutions for Chinese garbled characters in MySQL installation Common causes and solutions for Chinese garbled characters in MySQL installation Mar 02, 2024 am 09:00 AM

Common reasons and solutions for Chinese garbled characters in MySQL installation MySQL is a commonly used relational database management system, but you may encounter the problem of Chinese garbled characters during use, which brings trouble to developers and system administrators. The problem of Chinese garbled characters is mainly caused by incorrect character set settings, inconsistent character sets between the database server and the client, etc. This article will introduce in detail the common causes and solutions of Chinese garbled characters in MySQL installation to help everyone better solve this problem. 1. Common reasons: character set setting

How to install, uninstall, and reset Windows server backup How to install, uninstall, and reset Windows server backup Mar 06, 2024 am 10:37 AM

WindowsServerBackup is a function that comes with the WindowsServer operating system, designed to help users protect important data and system configurations, and provide complete backup and recovery solutions for small, medium and enterprise-level enterprises. Only users running Server2022 and higher can use this feature. In this article, we will explain how to install, uninstall or reset WindowsServerBackup. How to Reset Windows Server Backup If you are experiencing problems with your server backup, the backup is taking too long, or you are unable to access stored files, then you may consider resetting your Windows Server backup settings. To reset Windows

How to sort WPS scores How to sort WPS scores Mar 20, 2024 am 11:28 AM

In our work, we often use wps software. There are many ways to process data in wps software, and the functions are also very powerful. We often use functions to find averages, summaries, etc. It can be said that as long as The methods that can be used for statistical data have been prepared for everyone in the WPS software library. Below we will introduce the steps of how to sort the scores in WPS. After reading this, you can learn from the experience. 1. First open the table that needs to be ranked. As shown below. 2. Then enter the formula =rank(B2, B2: B5, 0), and be sure to enter 0. As shown below. 3. After entering the formula, press the F4 key on the computer keyboard. This step is to change the relative reference into an absolute reference.

How to sort in excel How to sort in excel Mar 05, 2024 pm 04:12 PM

Sorting methods in excel: 1. Single column sorting; 2. Multiple column sorting; 3. Custom sorting. Detailed introduction: 1. Single-column sorting is the most common sorting method. It sorts according to a selected column; 2. Multi-column sorting refers to sorting data in multiple columns, usually sorting according to a certain column first. On the basis of, sort by another column; 3. Custom sorting, allowing users to define the sort order according to their own needs.

See all articles