SQL SERVER – Effect of Case Sensitive Collation on Resultset

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 Data
SELECT *
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 Data
SELECT *
FROM ColTable
ORDER BY Col1
GO
-- Retrieve Data
SELECT *
FROM ColTable
ORDER 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 up
DROP TABLE ColTable
GO

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

Checkout following video on the subject:

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

7 thoughts on “SQL SERVER – Effect of Case Sensitive Collation on Resultset

  1. Good post as usual Pinal. I have to mess with collation a lot because I retrieve data from an Oracle (case sensitive) database to a SQL (case insensitve) database. I remember seeing a similar article in which the author suggested that a DBA should try to ensure that all databases within his environment have the same collation – not always possible with different requirements but a good goal.

    I know this is not the place to ask but I don’t know what is but do you have any previous posts on CDCs (if thats what they’re called – With statement?). I have an issue where I’m trying to create records in a table from two other tables but its a moving target. Its a little difficult to explain but basically, one table holds payments due and another holds balance available. As each payment is matched to a balance it needs to reduce the balance available as there can be multipel payments due fighting for the same balance available. The balance available should never go below zero. I’ve done it with a loop but it takes forever. Any help would be gratefully received.

  2. Pingback: SQL SERVER – Example of Width Sensitive and Width Insensitive Collation « SQL Server Journey with SQL Authority

  3. I have datatable with datatiem col

    1- how can i select distinct Date

    for example I have a Following Result :

    ID EMPID INTIME EMP Name

    32 2 2012-05-23 13:14:00.000 Anita
    33 5 2012-05-23 13:16:00.000 Kumar Singh
    34 5 2012-05-23 13:20:00.000 Kumar Singh
    35 2 2012-05-23 13:21:00.000 Anita
    37 1 2012-05-23 13:21:00.000 Kunal
    38 5 2012-05-25 18:17:51.000 Kumar Singh
    40 2 2012-05-25 18:17:55.000 Anita
    41 1 2012-05-25 18:17:57.000 Kunal
    42 1 2012-05-25 18:18:00.000 Kunal

    I Want This Result as

    32 2 2012-05-23 13:14:00.000 Anita
    33 5 2012-05-23 13:16:00.000 Kumar Singh
    37 1 2012-05-23 13:21:00.000 Kunal
    38 5 2012-05-25 18:17:51.000 Kumar Singh
    40 2 2012-05-25 18:17:55.000 Anita
    41 1 2012-05-25 18:17:57.000 Kunal

    One Record for 1 Day With Associated ID..

    any suggestion?

    • Dear Jilani,

      Let we assume Your Table Name as TestTable.

      The Query is

      Select * From TestTable A
      Where A. INTIME In
      (
      Select Min(B. INTIME )
      From TestTable B
      Where A.EMPID=B.EMPID
      Group By B.EMPID, Convert(Varchar(10),B. INTIME ,112)
      )
      Order By A.ID

  4. Pingback: SQL SERVER – Effect of Collation on Resultset – SQL in Sixty Seconds #026 – Video « SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – Resolve Cannot Resolve Collation Conflict Error – SQL in Sixty Seconds #047 | SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #037 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s