SQL Collation

I came across very interesting question related to SQL Collation on SQLServerCentral.com.

Question: You administer two SQL Server 2005 SP3 installations at Niagara Falls, one on the US side and one on the Canadian side. Each server instance was installed with default settings on a Windows machine set to the proper regional settings for that country. You execute the following script on both servers:

DECLARE @var VARCHAR(MAX);
SET @var = 'Hello World';
SELECT REPLACE(@var, CHAR(0), '')

What is the result on each server?

Correct answer: US: 'Hello World'; Canada: [server hangs]

Explanation: The key to this question is the default collation each server will be set to based on the regional settings of the machine:

    English (United States): SQL_Latin1_General_CP1_CI_AS

    English (Canada): Latin1_General_CI_AS

This only happens with a varchar(max) value in the first argument of the REPLACE() function. If you alter the script to use an nvarchar, or a varchar(x), or eliminate the variable entirely and simply run the REPLACE() on the string itself (without casting the string as a varchar(max)), both servers return 'Hello World'. The same behavior can be reproduced on a single server by specifying the collation when casting 'Hello World' to varchar(max):

SELECT REPLACE(CAST('Hello World' COLLATE SQL_Latin1_General_CP1_CI_AS AS VARCHAR(MAX)), CHAR(0), ''); --returns 'Hello World'
SELECT REPLACE(CAST('Hello World' COLLATE Latin1_General_CI_AS AS VARCHAR(MAX)), CHAR(0), ''); --hangs

Click here for more information on SQLServerCentral.com