Applying a Collation 让sql server 对大小写敏感

sql server 默认安装时（SQL_Latin1_General_CP1_CI_AS ）是不区分大小写的，可以在安装时选择排序规则时使用SQL_Latin1_General_CP1_CS_AS ，如果系统已经安装好了，又不想修改数

据库，仅想在查询时是对大小写敏感可以这样写

select * from Production.Productwhere name='Abc' COLLATE SQL_Latin1_General_CP1_CS_AS

在视图里

CREATE VIEW

Production.ProductSensitive

AS

SELECT

ProductID,

Name,

Name COLLATE SQL_Latin1_General_CP1_CS_AS AS NameSensitive

FROM Production.Product;

/************************************************************************************************************/

The following code creates a view that returns the ProductID column, and two versions of the Name column from the AdventureWorks.Production.Product table. The first Name column is left to the same collation as the source column, but the second Name column (aliased as NameSensitive) specifies a case sensitive collation for the column in the view.

USE AdventureWorks;

GO

CREATE VIEW

Production.ProductSensitive

AS

SELECT

ProductID,

Name,

Name COLLATE SQL_Latin1_General_CP1_CS_AS AS NameSensitive

FROM Production.Product;

The key piece is the line:

Name COLLATE SQL_Latin1_General_CP1_CS_AS AS NameSensitive

Most database developers know collations can be applied to databases and columns, but many don’t realize that we can apply collations to expressions as well. That is exactly what we are doing in that line. We are applying the case sensitive collation SQL_Latin1_General_CP1_CS_AS to the expression in the select list.

After running the code above to make the view, we can test that it works correctly by running the following statements:

--Should match at least one row. The [Name] column

--is not case sensitive

SELECT * FROM Production.ProductSensitive

WHERE Name='awc logo cap';

--Won't match any rows because [NameSensitive] is

--case sensitive

SELECT * FROM Production.ProductSensitive

WHERE NameSensitive='awc logo cap';

--Will match rows because [NameSensitive] is

--case sensitive, and the 'AWC Logo Cap' literal

--uses the proper case.

SELECT * FROM Production.ProductSensitive

WHERE NameSensitive='AWC Logo Cap';