SQL Server 检查区分大小写？

SQL Server check case-sensitivity?

提问by Kyle

How can I check to see if a database in SQL Server is case-sensitive? I have previously been running the query:

如何检查 SQL Server 中的数据库是否区分大小写？我以前一直在运行查询：

SELECT CASE WHEN 'A' = 'a' THEN 'NOT CASE SENSITIVE' ELSE 'CASE SENSITIVE' END

But I am looking for other ways as this has actually given me issues in the past.

但我正在寻找其他方法，因为这实际上给我带来了过去的问题。

Edit - A little more info: An existing product has many pre-written stored procedures. In a stored procedure @test != @TESTdepending on the sensitivity of the server itself. So what I'm looking for is the best way to check the server for its sensitivity.

编辑 - 更多信息：现有产品有许多预先编写的存储过程。在存储过程中@test != @TEST取决于服务器本身的敏感性。所以我正在寻找的是检查服务器敏感性的最佳方法。

回答by Raj More

Collation can be set at various levels:

可以在不同级别设置排序规则：

1. Server
2. Database
3. Column

1. 服务器
2. 数据库
3. 柱子

So you could have a Case Sensitive Column in a Case Insensitive database. I have not yet come across a situation where a business case could be made for case sensitivity of a single column of data, but I suppose there could be.

所以你可以在不区分大小写的数据库中有一个区分大小写的列。我还没有遇到过可以针对单列数据的区分大小写制定业务案例的情况，但我认为可能存在。

Check Server Collation

检查服务器排序规则

SELECT SERVERPROPERTY('COLLATION')

Check Database Collation

检查数据库整理

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

Check Column Collation

检查列排序规则

select table_name, column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @table_name

回答by Will Marcouiller

If you installed SQL Server with the default collation options, you might find that the following queries return the same results:

如果您使用默认排序规则选项安装 SQL Server，您可能会发现以下查询返回相同的结果：

CREATE TABLE mytable 
( 
    mycolumn VARCHAR(10) 
) 
GO 

SET NOCOUNT ON 

INSERT mytable VALUES('Case') 
GO 

SELECT mycolumn FROM mytable WHERE mycolumn='Case' 
SELECT mycolumn FROM mytable WHERE mycolumn='caSE' 
SELECT mycolumn FROM mytable WHERE mycolumn='case' 

You can alter your query by forcing collation at the column level:

您可以通过在列级别强制整理来更改您的查询：

SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'caSE' 

SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'case' 

SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'Case' 

-- if myColumn has an index, you will likely benefit by adding 
-- AND myColumn = 'case' 

SELECT DATABASEPROPERTYEX('<database name>', 'Collation') 

As changing this setting can impact applications and SQL queries, I would isolate this test first. From SQL Server 2000, you can easily run an ALTER TABLE statement to change the sort order of a specific column, forcing it to be case sensitive. First, execute the following query to determine what you need to change it back to:

由于更改此设置会影响应用程序和 SQL 查询，因此我将首先隔离此测试。在 SQL Server 2000 中，您可以轻松地运行 ALTER TABLE 语句来更改特定列的排序顺序，强制它区分大小写。首先，执行以下查询以确定需要将其改回的内容：

EXEC sp_help 'mytable' 

The second recordset should contain the following information, in a default scenario:

在默认情况下，第二个记录集应包含以下信息：

Column_Name Collation

Column_Name 排序规则

---

---

mycolumn SQL_Latin1_General_CP1_CI_AS

mycolumn SQL_Latin1_General_CP1_CI_AS

Whatever the 'Collation' column returns, you now know what you need to change it back to after you make the following change, which will force case sensitivity:

无论“排序规则”列返回什么，您现在都知道在进行以下更改后需要将其改回什么，这将强制区分大小写：

ALTER TABLE mytable 
    ALTER COLUMN mycolumn VARCHAR(10) 
    COLLATE Latin1_General_CS_AS 
GO 



SELECT mycolumn FROM mytable WHERE mycolumn='Case' 
SELECT mycolumn FROM mytable WHERE mycolumn='caSE' 
SELECT mycolumn FROM mytable WHERE mycolumn='case' 

If this screws things up, you can change it back, simply by issuing a new ALTER TABLE statement (be sure to replace my COLLATE identifier with the one you found previously):

如果这把事情搞砸了，你可以把它改回来，只需发出一个新的 ALTER TABLE 语句（确保用你之前找到的那个替换我的 COLLATE 标识符）：

ALTER TABLE mytable 
    ALTER COLUMN mycolumn VARCHAR(10) 
    COLLATE SQL_Latin1_General_CP1_CI_AS 

If you are stuck with SQL Server 7.0, you can try this workaround, which might be a little more of a performance hit (you should only get a result for the FIRST match):

如果您坚持使用 SQL Server 7.0，您可以尝试此解决方法，这可能会稍微影响性能（您应该只获得第一个匹配的结果）：

SELECT mycolumn FROM mytable WHERE 
    mycolumn = 'case' AND 
    CAST(mycolumn AS VARBINARY(10)) = CAST('Case' AS VARBINARY(10)) 

SELECT mycolumn FROM mytable WHERE 
    mycolumn = 'case' AND 
    CAST(mycolumn AS VARBINARY(10)) = CAST('caSE' AS VARBINARY(10)) 

SELECT mycolumn FROM mytable WHERE 
    mycolumn = 'case' AND 
    CAST(mycolumn AS VARBINARY(10)) = CAST('case' AS VARBINARY(10)) 

-- if myColumn has an index, you will likely benefit by adding 
-- AND myColumn = 'case' 

回答by Devraj Gadhavi

SQL server determines case sensitivity by COLLATION.

SQL 服务器通过COLLATION.

COLLATIONcan be set at various levels.

COLLATION可以设置不同的级别。

1. Server-level
2. Database-level
3. Column-level
4. Expression-level

1. 服务器级
2. 数据库级
3. 列级
4. 表达水平

Here is the MSDN reference.

这是 MSDN 参考。

One can check the COLLATIONat each level as mentioned in Raj More's answer.

可以检查Raj More 的回答中COLLATION提到的每个级别。

Check Server Collation

检查服务器排序规则

SELECT SERVERPROPERTY('COLLATION')

Check Database Collation

检查数据库整理

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

Check Column Collation

检查列排序规则

select table_name, column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @table_name

Check Expression Collation

检查表达式排序规则

For expression level COLLATIONyou need to look at the expression. :)

对于表达水平，COLLATION您需要查看表达。:)

It would be generally at the end of the expression as in below example.

它通常位于表达式的末尾，如下例所示。

SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;

Collation Description

整理说明

For getting description of each COLLATIONvalue try this.

要获得每个COLLATION值的描述，试试这个。

SELECT * FROM fn_helpcollations()

And you should see something like this.

你应该看到这样的东西。

You can always put a WHEREclause to filter and see description only for your COLLATION.

您始终可以放置一个WHERE子句来过滤并仅查看您的COLLATION.

You can find a list of collations here.

您可以在此处找到排序规则列表。

回答by Joel Coehoorn

You're interested in the collation. You could build something based on this snippet:

您对整理感兴趣。你可以基于这个片段构建一些东西：

SELECT DATABASEPROPERTYEX('master', 'Collation');

---

---

Update
Based on your edit — If @testand @TESTcan ever refer to two different variables, it's not SQL Server. If you see problems where the samevariable is not equal to itself, check if that variable is NULL, because NULL = NULLreturns `false.

根据您的编辑更新- 如果@test并且@TEST可以引用两个不同的变量，则它不是 SQL Server。如果您看到相同变量不等于自身的问题，请检查该变量是否为NULL，因为NULL = NULL返回 `false。

回答by DareDevil

The best way to work with already created tables is that, Go to Sql Server Query Editor

使用已创建表的最佳方法是，转到 Sql Server 查询编辑器

Type: sp_help <tablename>

类型： sp_help <tablename>

This will show table's structure , see the details for the desired field under COLLATE column.

这将显示表的结构，查看 COLLATE 列下所需字段的详细信息。

then type in the query like :

然后输入查询，如：

SELECT myColumn FROM myTable  
WHERE myColumn COLLATE SQL_Latin1_General_CP1_CI_AS = 'Case'

It could be different character schema <SQL_Latin1_General_CP1_CI_AS>, so better to find out the exact schema that has been used against that column.

它可能是不同的字符架构 < SQL_Latin1_General_CP1_CI_AS>，因此最好找出针对该列使用的确切架构。

回答by shA.t

How can I check to see if a database in SQL Server is case-sensitive?

如何检查 SQL Server 中的数据库是否区分大小写？

You can use below query that returns your informed database is case sensitive or not or is in binary sort(with null result):

您可以使用以下查询来返回您的知情数据库是否区分大小写或二进制排序（结果为空）：

;WITH collations AS (
    SELECT 
        name,
        CASE 
            WHEN description like '%case-insensitive%' THEN 0 
            WHEN description like '%case-sensitive%' THEN 1 
        END isCaseSensitive
    FROM 
        sys.fn_helpcollations()
)
SELECT *
FROM collations
WHERE name = CONVERT(varchar, DATABASEPROPERTYEX('yourDatabaseName','collation'));

For more read this MSDN information;).

有关更多信息，请阅读此 MSDN 信息；)。

回答by Mark Callison

SQL Server is not case sensitive. SELECT * FROM SomeTableis the same as SeLeCT * frOM soMetaBLe.

SQL Server 不区分大小写。SELECT * FROM SomeTable与 相同SeLeCT * frOM soMetaBLe。