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:
[youtube=http://www.youtube.com/watch?v=zPmL0z_aspo]
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
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.
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