Nice Article Which I Read...

Nice Article Which I Read...

• IMPLICIT CONVERTION(sqlskills.com by Jonathan Kehayias) -                                                                     While performance tuning investigation I have found very nice article about IMPLICIT CONVERTION. This is publish on sqlskills.com(one of my favourite website) website, written by Jonathan Kehayias.
• Finding a table name from a page ID(SQLskills) -  This is good article written by Paul Randal.
• Deadlock Troubleshooting From Bart Duncan's SQL Weblog
• Deadlock Troubleshooting, Part I
• Deadlock Troubleshooting, Part II
• Deadlock Troubleshooting, Part III

SQL Server Collation

SQL Server Collation

         
                       Sometimes you need to work with different SQL Server collation depend on your customers. If you need to have multiple languages in same database then you must use Unicode supported  data types. If you have different language with different database then, you can have different collation for each database.

• How SQL Server collation works?  

                                In high-level explanation is it will support proper use of characters for either a language. 

• SQL Server Collation with “LIKE” Operator

                              This is very importance when you are using LIKE Operator in different collations. Main consideration is case sensitive, if your database collation is case sensitive then like operator supports only case sensitive data. If accent-insensitive then you can have data with different accent.
Following examples will help you to get more idea,

use tempdb
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblCollate]') AND [type] in (N'U')) BEGIN
    DROP TABLE [dbo].[tblCollate];
END    
GO
CREATE TABLE tblCollate
    (varcharGeneral        varchar(100) COLLATE    SQL_Latin1_General_CP1_CI_AS NOT NULL,
     varcharCollateCIAI varchar(100) COLLATE Finnish_Swedish_CI_AI NOT NULL,
     varcharCollateCSAI varchar(100) COLLATE Finnish_Swedish_CS_AI NOT NULL,
     varcharCollateCSAS varchar(100) COLLATE Finnish_Swedish_CS_AS NOT NULL,
     varcharCollateCIAS varchar(100) COLLATE Finnish_Swedish_CI_AS NOT NULL                        
);
GO
--Insert Record Set 01
INSERT INTO [dbo].[tblCollate]
(varcharGeneral,varcharCollateCIAI,varcharCollateCSAS,varcharCollateCSAI,varcharCollateCIAS)
VALUES    ('Varchar','Varchar','Varchar','Varchar','Varchar'),
        ('varchar','varchar','varchar','varchar','varchar'),
        ('Value','Value','Value','Value','Value'),
        ('value','value','value','value','value'),
        ('Vacation','Vacation','Vacation','Vacation','Vacation'),
        ('vacation','vacation','vacation','vacation','vacation');
GO
--Insert Record Set 02
INSERT INTO [dbo].[tblCollate]
(varcharGeneral,varcharCollateCIAI,varcharCollateCSAS,varcharCollateCSAI,varcharCollateCIAS)
VALUES    ('Wallet','Wallet','Wallet','Wallet','Wallet'),
        ('wallet','wallet','wallet','wallet','wallet'),
        ('Wallpaper','Wallpaper','Wallpaper','Wallpaper','Wallpaper'),
        ('wallpaper','wallpaper','wallpaper','wallpaper','wallpaper'),
        ('Watch','Watch','Watch','Watch','Watch'),
        ('watch','watch','watch','watch','watch');
GO    
    
SELECT * FROM [dbo].[tblCollate]; --All Records

--SQL_Latin1_General_CP1_CI_AS
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%Va%');
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%va%');
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%Wa%');
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%Value%');
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%Wallet%');

--Finnish_Swedish_CI_AI
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%Va%');
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%va%');
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%Wa%');
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%Value%');
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%Wallet%');

--Finnish_Swedish_CS_AI
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%Va%');
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%va%');
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%Wa%');
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%Value%');
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%Wallet%');

--Finnish_Swedish_CS_AS
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%Va%');
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%va%');
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%Wa%');
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%Value%');
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%Wallet%');

--Finnish_Swedish_CI_AS
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%Va%');
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%va%');
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%Wa%');
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%Value%');
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%Wallet%');
GO

Now you will have some good idea about SQL Server collation and how you have to change collation with different languages, 

Hope following Microsoft Reference will help you for further investigation,

• SQL Server Collation Fundamentals
• LIKE (Transact-SQL) 
• Selecting a SQL Server Collation
• COLLATE (Transact-SQL)

Cheers...
and Most welcome your comments and ideas...