Case Sensitive SQL Query Search and Joining
This article gives you a total overall idea related to collation. It not only contains definition but also contains SQL comparisons and joins with different type collation table in different database.
So let's start
Collation is defined by a set of rules that determines how the data is stored and compare. There are for types of collation and they are
1. Case sensitive: if "A" and "a" is treated as same way, then it is case in-sensitive. And id "A" and "a" are treated as different way than it is case sensitive. As the ASCII code of both letters are different. Capital "A" takes 65 and small "a" takes 97.
2. Accent sensitivity: if "a" and "á" treated as same way then it is accent in-sensitive and if treated differently than it is accent sensitive. The ASCII code of "a" is 97 and "á" is 225.
3. Kana Sensitivity: When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
4. Width sensitivity: When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently than it is width sensitive.
Example of database creation, with different collations is mentioned bellow
USE MASTER
GO
CREATE DATABASE BIN COLLATE LATIN1_GENERAL_BIN
GO
CREATE DATABASE CI_AI_KS COLLATE LATIN1_GENERAL_CI_AI_KS
GO
CREATE DATABASE CS_AS_KS_WS COLLATE LATIN1_GENERAL_CS_AS_KS_WS
GO
A table objects creation example with different collation
CREATE TABLE Mytable
([colu] CHAR(10) COLLATE Albanian_CI_AI_KS_WS NULL,
[Maydate] CHAR(8) COLLATE Korean_Wansung_Unicode_CS_AS_KS NOTNULL ,
[Risk_Rating] CHAR(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL)
As a SQL server developer you have to face a situation to compare the two columns of different table having different collation. For Example you are joining two table which support different collations. If you are not careful it gives you an error msg.
To illustrate my points here I give u a simple example of case sensitive collation.
CREATE TABLE Mytable
(Sname CHAR(100) NOT NULL)
INSERT INTO Mytable (Sname)
VALUES ('JOYDEEP'),('TUHIN'),('PALASH'),('SANGRAM')
-- Get the result
SELECT * FROM Mytable WHERE Sname='joydeep'
To make the query case sensitive uses this.
-- To make the query case sensitive
SELECT * FROM Mytable WHERE Sname COLLATELatin1_General_CS_AS='joydeep'
-- Now
SELECT * FROM Mytable WHERE Sname COLLATELatin1_General_CS_AS='JOYDEEP'
To find the collation of any table use this
EXEC sp_help Mytable
Now I am giving an example to join two tables from different database with different collations.
If we perform this join it gives an error
SELECT ProductCode, ProductResalePrice
FROM [database1].[dbo].[PRODUCTS]
INNER JOIN [database2].[dbo].[items] ON ProductCode =[items].itemCode
Error:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
We have to first know what type of collation both of the databases used to further precede the joining operations.
SELECT DATABASEPROPERTYEX('database1', 'Collation') SQLCollation;
SELECT DATABASEPROPERTYEX('database2', 'Collation') SQLCollation;
Now we can
SELECT ProductCode, ProductResalePrice
FROM [database1].[dbo].[PRODUCTS]
INNER JOIN [database2].[dbo].[items]
ON ProductCode COLLATE SQL_Latin1_General_CP1_CI_AS =[items].itemCode
I think that the article is quite informative and thanking you to provide your valuable time on it.
