Collation is a very important concept but often ignored. I have often seen developers either not understanding this or ignored it – this is plain wrong. In simple word we can say Collation is the language or interpreting done by SQL Server. Well, in today’s SQL in Sixty Seconds we are going to observe how collation affects the resultset. Today’s blog post is inspired from my earlier blog post SQL SERVER – Effect of Case Sensitive Collation on Resultset. I strongly encourage you to read this earlier blog post for sample code as well additional explanation related to the concept shared in today’s SQL in Sixty Seconds.

Here is the code used in the video.

USE TempDB
GO
-- Sample Data Building
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
-- Retrieve Data
SELECT *
FROM ColTable
ORDER BY Col1
GO
-- Retrieve Data
SELECT *
FROM ColTable
ORDER BY Col2
GO
-- Clean up
DROP TABLE ColTable
GO

Related Tips in SQL in Sixty Seconds:

• SQL SERVER – Effect of Case Sensitive Collation on Resultset
• Example of Width Sensitive and Width Insensitive Collation
• Collation and Collation Sensitivity – Quiz – Puzzle – 6 of 31
• Change Collation of Database Column – T-SQL Script
• Find Collation of Database and Table Column Using T-SQL
• Default Collation of SQL Server 2008
• Cannot resolve collation conflict for equal to operation

If we like your idea we promise to share with you educational material.

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