Applying a Collation to Columns in a View….

Steve Evans asked me a question today that was kind of interesting.  His question was something like:

“I have a view that returns data from multiple tables.  I want two of the columns returned through the view to be case sensitive.  Can I just have the view treat those two columns as case sensitive, or do I have to define those two columns on the tables as case sensitive?”

Or more generically, “Can I return a column in a view with a different collation than the source column?”.

The short answer, is that yes, we can enforce a collation on a column in a view that is different than the collation on the source columns.

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’;

Anyhow, that was a fun question to answer, and thought it might be of either real use, or at last theoretical use to others.  Let me know if you end up using somewhere and why. 

One Response

  • Just in case your curious the reason I needed it was I have two identical tables (one current, one a copy from yesterday) that feed a view that creates a delta of those tables. Without case sensitivity on the first and last name field when Mcdonald has his last name fixed to McDonald it won’t show up in the delta view. This view feeds our identity system so case changes on users first or last names were not getting propagated.

Leave a Reply