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_ASto 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 column
--is not case sensitive
SELECT * FROM Production.ProductSensitive
WHERE Name='awc logo cap';
--Won't match any rows because is
--case sensitive
SELECT * FROM Production.ProductSensitive
WHERE NameSensitive='awc logo cap';
--Will match rows because is
--case sensitive, and the 'AWC Logo Cap' literal
--uses the proper case.
SELECT * FROM Production.ProductSensitive
WHERE NameSensitive='AWC Logo Cap';
页:
[1]