To solve the case-insensitive problem in SQL server databases

Search for a lot of related information on the Internet and summarize the reasons for this situation mainly because of sorting rules:
If no sorting rule is specified

Whether the temporary table is case sensitive depends on the order of the tempdb database.
Whether database objects are case sensitive depends on Database sorting rules.
Whether the variables are case sensitive depends on the database sorting rules. It is also related to the tempdb database.
Binary encoding: sort and compare data in the Microsoft & reg; SQL Server data table based on the bit mode defined for each character. The Binary sorting method is case sensitive, that is, the lower-case ratio is preferred, and the accent is differentiated. This is the fastest sorting method.
If this option is not selected, SQL Server will follow the sorting and comparison rules defined in the dictionary of the associated language or letter.

Case sensitivity specifies that SQL Server distinguishes uppercase and lowercase letters. If this option is not selected, SQL Server treats uppercase and lowercase letters as equal. If [case-sensitive] is not selected, SQL Server does not define the sorting method for lowercase and uppercase letters.

Solution:
Method 1. When installing SQL, select case sensitive or recreate mastar after installation, and select case sensitive
C: \ Program Files \ Microsoft SQL Server \ 80 \ Tools \ Binn \ rebuildm.exe

Method 2: SQL server 8.0 or a later version is supported. SQL server 7.0 or a later version is not supported.
Alter database COLLATE Chinese_PRC_CS_AS
Modify the sorting rule to a case-sensitive sorting rule.
If only one table is modified, use the alter table statement.
If you modify the default sorting rules of a database, use the alter datebase statement.
If you modify the sequence rules of the entire server, use rebuildm.exe to recreate the master database.
 

-- Specify the sorting rule.

-- Example
Select replace ('abacb' collate Chinese_PRC_CS_AS_WS, 'B', 'test ')

-- If you require table support, you can specify the sorting rules when creating the table so that replace does not need to write the sorting rules.

-- Example
Create table tb (a varchar (20) collate Chinese_PRC_CS_AS_WS)
Insert tb values ('abac ')

Select replace (a, 'A', 'test') from tb

Drop table tb

Summary:

-- Table
Alter table tb
Alter column colname nvarchar (100) COLLATE Chinese_PRC_CI_AS
-- Case insensitive
Alter table tb
Alter column colname nvarchar (100) COLLATE Chinese_PRC_CS_AS
-- Case sensitive
Database
Alter database
Collate chinese_prc_cs_as
-- Case sensitive

Alter database
Collate chinese_prc_ci_as
-- Case insensitive

Specify the sorting rule.
Windows sorting rule name
Specify the Windows sorting rule name in the COLLATE clause. The Windows sorting rule name consists of the sorting rule indicator and comparison style.
Syntax:
<Windows_collation_name >::=
CollationDesignator _ <ComparisonStyle>
<ComparisonStyle >::=
Casesensitivity_accentsensiti.pdf
[_ KanatypeSensitive [_ WidthSensitive]
| _ BIN

Parameters
CollationDesignator
Specifies the basic sorting rules used by Windows sorting rules. Basic sorting rules include:

Specifies the alphabet or language in which the sorting rules are applied when sorting by dictionary is specified.


The code page used to store non-Unicode character data.
For example, Latin1_General or French, both use the code page 1252 or Turkish, and use the code page 1254.

Casesensiti.pdf

The CI parameter is case insensitive and the CS parameter is case sensitive.

Accentsensiti.pdf

AI specifies no accent, and AS specifies the accent.

KanatypeSensitive

Omitted specifies case-insensitive, and KS specifies a Kana type.

Widthsensiti.pdf

Omitted specifies case-insensitive, and WS specifies case-sensitive.

BIN

Specify the binary sorting order.


If you only distinguish the current query, do not change it like this to avoid regret. The query is as follows:
Select * from
/*
A_nam a_add
--------------------
1 aa
1 bb
2 cc
2 vv
2 kk
3 dd
3 ee
4 dd
5 ee
6 yy
6 yy

(11 row (s) affected)
*/
Now we can query a_add = 'A', 'A', and so on!
Example 1:

Select * from
Where a_add collate Chinese_PRC_CS_AS_WS = 'A'
/*
A_nam a_add
--------------------
1 aa

(1 row (s) affected)
*/

Example 2:

Select * from
Where a_add collate chinese_prc_cs_as_ws = 'A'
/*
A_nam a_add
--------------------

(0 row (s) affected)
*/

Method 3. The above cannot be remembered, so we use the most stupid method to convert it to ASCII
Select * from
Where
ASCII (substring (a_add, 1, 1) = ASCII (substring ('A', 1, 1 ))
And
Ascii (substring (a_add, 2, 1) = ascii (substring ('A', 2, 1 ))
/*
A_nam a_add
--------------------

(0 row (s) affected)
*/

Method 3: Any version can

Select * from
Where cast (a_add as varbinary (10) = cast ('A' as varbinary (10 ))