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)
Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology, Video
