Effect of Case Sensitive Collation on Resultset

July 12, 2012 by pinaldave

Collation is a very interesting concept but I quite often see it is heavily neglected. I have seen developer and DBA looking for a workaround to fix collation error rather than understanding if the side effect of the workaround. Collation is a very deep subject. Earlier I wrote an article how one can resolve the collation error when different collation values are compared. Today in most simple way I would like to explain that different collation can return different result. Without understanding business needs (and sensitivity) one should not change the collation of the columns or database.

Let us see a simple example. I am going to create a table with two columns. Both the columns have different collation. One collation is case sensitive (CS) and another one is case insensitive (CI). You can see that col1 and col2 both have exactly the same data.

CREATE TABLE ColTable(Col1 VARCHAR(15) COLLATE Latin1_General_CI_AS,Col2 VARCHAR(14) COLLATE Latin1_General_CS_AS) ;INSERT ColTable(Col1, Col2)VALUES ('Apple','Apple'),
('apple','apple'),
('pineapple','pineapple'),
('Pineapple','Pineapple');GO

-- Retrieve DataSELECT *FROM ColTable
GO

Now let us run two queries and compared its result set. In the first query col1 is used in order by clause and in second query col2 is used in the order by clause.

-- Retrieve DataSELECT *FROM ColTableORDER BY Col1
GO-- Retrieve DataSELECT *FROM ColTableORDER BY Col2
GO

Technically both the columns have exactly the same data. When either of the columns used in order by it should give exactly the same result. However, in our case it is returning us different result. The reason is simple – collation of the column is different. As mentioned earlier one of the column has a case sensitive collation and another column has a case insensitive collation. When table is ordered by Col2 which is case sensitive leading to lowercase ‘apple’ row before upper case ‘apple’ row.

Let us clean up.
-- Clean upDROP TABLE ColTable
GO

As mentioned collation is a very important concept. It should be properly understood and explored before taking it granted or easy.