18

We're moving from SQL 2005 [Instance and DB have collation of SQL_Latin1_General_CP1_CI_AS] to SQL 2008 [which defaults to Latin1_General_CI_AS].

I completed a SQL 2008 R2 installation, and used default Latin1_General_CI_AS collation, with the restoration of the database still on SQL_Latin1_General_CP1_CI_AS. The excepted problems occurred - the #temp tables where in Latin1_General_CI_AS whilst the db was in SQL_Latin1_General_CP1_CI_AS and this is where I am now - I need advice on the pitfalls now please.

On installation of SQL 2008 R2, I have the option on installation to use 'SQL Collation, used for backwards compatibility' where I have the option to select the same collation as the 2005 database : SQL_Latin1_General_CP1_CI_AS.

  1. This will allow me to not have problems with #temp tables, but are there pitfalls?

  2. Would I lose any functionality or features of any kind by not using a "current" collation of SQL 2008?

  3. What about when we move (e.g. in 2 years ) from 2008 to SQL 2012? Will I have problems then?

  4. Would I at some point be forced to go to Latin1_General_CI_AS?

  5. I read that some DBA's script complete the rows of complete databases, and then run the insert script into the database with the new collation - I'm very scared and wary of this - would you recommend doing this?

Improve this question
1

2 Answers 2

Reset to default
20

First of all, apologies for such a long answer, as I feel that still there is a lot of confusion when people talk about terms like collation, sort order, code page, etc.

From BOL :

Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types such as char and varchar dictate the code page and corresponding characters that can be represented for that data type. Whether you are installing a new instance of SQL Server, restoring a database backup, or connecting server to client databases, it is important that you understand the locale requirements, sorting order, and case and accent sensitivity of the data that you will be working with.

This means that Collation is very important as it specifies rules on how character strings of the data are sorted and compared.

Note: More info on COLLATIONPROPERTY

Now Lets first understand the differences ......

Running below T-SQL :

SELECT *
FROM::fn_helpcollations()
WHERE NAME IN (
        'SQL_Latin1_General_CP1_CI_AS'
        ,'Latin1_General_CI_AS'
        )
GO

SELECT 'SQL_Latin1_General_CP1_CI_AS' AS 'Collation'
    ,COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'CodePage') AS 'CodePage'
    ,COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'LCID') AS 'LCID'
    ,COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'ComparisonStyle') AS 'ComparisonStyle'
    ,COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'Version') AS 'Version'

UNION ALL

SELECT 'Latin1_General_CI_AS' AS 'Collation'
    ,COLLATIONPROPERTY('Latin1_General_CI_AS', 'CodePage') AS 'CodePage'
    ,COLLATIONPROPERTY('Latin1_General_CI_AS', 'LCID') AS 'LCID'
    ,COLLATIONPROPERTY('Latin1_General_CI_AS', 'ComparisonStyle') AS 'ComparisonStyle'
    ,COLLATIONPROPERTY('Latin1_General_CI_AS', 'Version') AS 'Version'
GO

The results would be :

enter image description here

Looking at above results, the only difference is the Sort Order between the 2 collations.But that is not true, which you can see why as below :

Test 1 :

--Clean up previous query
IF OBJECT_ID('Table_Latin1_General_CI_AS') IS NOT NULL
    DROP TABLE Table_Latin1_General_CI_AS;

IF OBJECT_ID('Table_SQL_Latin1_General_CP1_CI_AS') IS NOT NULL
    DROP TABLE Table_SQL_Latin1_General_CP1_CI_AS;

-- Create a table using collation Latin1_General_CI_AS 
CREATE TABLE Table_Latin1_General_CI_AS (
    ID INT IDENTITY(1, 1)
    ,Comments VARCHAR(50) COLLATE Latin1_General_CI_AS
    )

-- add some data to it 
INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('Kin_Tester1')

-- Create second table using collation SQL_Latin1_General_CP1_CI_AS 
CREATE TABLE Table_SQL_Latin1_General_CP1_CI_AS (
    ID INT IDENTITY(1, 1)
    ,Comments VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS
    )

-- add some data to it 
INSERT INTO Table_SQL_Latin1_General_CP1_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_SQL_Latin1_General_CP1_CI_AS (Comments)
VALUES ('Kin_Tester1')

--Now try to join both tables
SELECT *
FROM Table_Latin1_General_CI_AS LG
INNER JOIN Table_SQL_Latin1_General_CP1_CI_AS SLG ON LG.Comments = SLG.Comments
GO

Results of Test 1:

Msg 468, Level 16, State 9, Line 35
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

From above results we can see that the we cannot directly compare values on columns with different collations, you have to use COLLATE to compare the column values.

TEST 2 :

The major difference is performance, as Erland Sommarskog points out at this discussion on msdn.

--Clean up previous query
IF OBJECT_ID('Table_Latin1_General_CI_AS') IS NOT NULL
    DROP TABLE Table_Latin1_General_CI_AS;

IF OBJECT_ID('Table_SQL_Latin1_General_CP1_CI_AS') IS NOT NULL
    DROP TABLE Table_SQL_Latin1_General_CP1_CI_AS;

-- Create a table using collation Latin1_General_CI_AS 
CREATE TABLE Table_Latin1_General_CI_AS (
    ID INT IDENTITY(1, 1)
    ,Comments VARCHAR(50) COLLATE Latin1_General_CI_AS
    )

-- add some data to it 
INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_Latin1_General_CI_AS (Comments)
VALUES ('kin_tester1')

-- Create second table using collation SQL_Latin1_General_CP1_CI_AS 
CREATE TABLE Table_SQL_Latin1_General_CP1_CI_AS (
    ID INT IDENTITY(1, 1)
    ,Comments VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS
    )

-- add some data to it 
INSERT INTO Table_SQL_Latin1_General_CP1_CI_AS (Comments)
VALUES ('kin_test1')

INSERT INTO Table_SQL_Latin1_General_CP1_CI_AS (Comments)
VALUES ('kin_tester1')

--- Create Indexes on both tables 

CREATE INDEX IX_LG_Comments ON  Table_Latin1_General_CI_AS(Comments)
go
CREATE INDEX IX_SLG_Comments ON  Table_SQL_Latin1_General_CP1_CI_AS(Comments)

--- Run the queries 

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_Latin1_General_CI_AS WHERE Comments = 'kin_test1'
GO

--- This will have IMPLICIT Conversion

enter image description here

--- Run the queries 

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_SQL_Latin1_General_CP1_CI_AS WHERE Comments = 'kin_test1'
GO

--- This will NOT have IMPLICIT Conversion

enter image description here

The reason for Implicit conversion is because, I have my database & Server collation both as SQL_Latin1_General_CP1_CI_AS and the table Table_Latin1_General_CI_AS has column Comments defined as VARCHAR(50) with COLLATE Latin1_General_CI_AS, so during the lookup SQL Server has to do an IMPLICIT conversion.

Test 3:

With the same set up, now we will compare the varchar columns with nvarchar values to see the changes in the execution plans.

-- run the query

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_Latin1_General_CI_AS WHERE Comments =  (SELECT N'kin_test1' COLLATE Latin1_General_CI_AS)
GO

enter image description here

-- run the query

DBCC FREEPROCCACHE
GO
SELECT Comments FROM Table_SQL_Latin1_General_CP1_CI_AS WHERE Comments = N'kin_test1'
GO

enter image description here

Note that the first query is able to do Index seek but has to do Implicit conversion while the second one does an Index scan which prove to be inefficient in terms of performance when it will scan large tables.

Conclusion :

  • All of the above tests shows that having right collation is very important for your database server instance.
  • SQL_Latin1_General_CP1_CI_AS is a SQL collation with the rules that allow you to sort data for unicode and non-unicode are different.
  • SQL collation wont be able to use Index when comparing unicode and non-unicode data as seen in above tests that when comparing nvarchar data to varchar data, it does Index scan and not seek.
  • Latin1_General_CI_AS is a Windows collation with the rules that allow you to sort data for unicode and non-unicode are same.
  • Windows collation can still use Index (Index seek in above example) when comparing unicode and non-unicode data but you see a slight performance penalty.
  • Highly recommend to read Erland Sommarskog answer + the connect items that he has pointed to.

This will allow me to not have problems with #temp tables, but are there pitfalls?

See my answer above.

Would I lose any functionality or features of any kind by not using a "current" collation of SQL 2008?

It all depends on what functionality/features you are referring to. Collation is storing and sorting of data.

What about when we move (e.g. in 2 years ) from 2008 to SQL 2012? Will I have problems then? Would I at some point be forced to go to Latin1_General_CI_AS?

Cant vouch ! As things might change in and its always good to be inline with Microsoft's suggestion + you need to understand your data and the pitfalls that I mentioned above. Also refer to this and this connect items.

I read that some DBA's script complete the rows of complete databases, and then run the insert script into the database with the new collation - I'm very scared and wary of this - would you recommend doing this?

When you want to change collation, then such scripts are useful. I have found myself changing collation of databases to match server collation many times and I have some scripts that does it pretty neat. Let me know if you need it.

References :

Improve this answer
0
5

In addition to what @Kin detailed in his answer, there are a few more things to be aware of when switching the server's (i.e. instance's) default collation (items above the horizontal line are directly relevant to the two collations mentioned in the Question; items below the horizontal line are relevant to the general):

  • IF YOUR DATABASE'S DEFAULT COLLATION IS NOT CHANGING, then the "implicit conversion" performance issue described in @Kin's answer should not be a problem since string literals and local variables use the Database's default Collation, not the server's. The only impacts for the scenario in which the instance level Collation is changed but not the database level Collation are (both described in detail below):

    • potential collation conflicts with temporary tables (but not table variables).
    • potential broken code if casing of variables and/or cursors does not match their declarations (but this can only happen if moving to an instance with a binary or case-sensitive collation).

  • One difference between these two Collations is in how they sort certain characters for VARCHAR data (this does not affect NVARCHAR data). The non-EBCDIC SQL_ Collations use what is called "String Sort" for VARCHAR data, while all other Collations, and even NVARCHAR data for the non-EBCDIC SQL_ Collations, use what is called "Word Sort". The difference is that in "Word Sort", the dash - and apostrophe ' (and maybe a few other characters?) are given a very low weight and are essentially ignored unless there are no other differences in the strings. To see this behavior in action, run the following:

    DECLARE @Test TABLE (Col1 VARCHAR(10) NOT NULL);
INSERT INTO @Test VALUES ('aa');
INSERT INTO @Test VALUES ('ac');
INSERT INTO @Test VALUES ('ah');
INSERT INTO @Test VALUES ('am');
INSERT INTO @Test VALUES ('aka');
INSERT INTO @Test VALUES ('akc');
INSERT INTO @Test VALUES ('ar');
INSERT INTO @Test VALUES ('a-f');
INSERT INTO @Test VALUES ('a_e');
INSERT INTO @Test VALUES ('a''kb');

SELECT * FROM @Test ORDER BY [Col1] COLLATE SQL_Latin1_General_CP1_CI_AS;
-- "String Sort" puts all punctuation ahead of letters

SELECT * FROM @Test ORDER BY [Col1] COLLATE Latin1_General_100_CI_AS;
-- "Word Sort" mostly ignores dash and apostrophe

    Returns:

    String Sort
-----------
a'kb
a-f
a_e
aa
ac
ah
aka
akc
am
ar

    and:

    Word Sort
---------
a_e
aa
ac
a-f
ah
aka
a'kb
akc
am
ar

    While you will "lose" the "String Sort" behavior, I'm not sure that I would call that a "feature". It is a behavior that has been deemed undesirable (as evidenced by the fact that it wasn't brought forward into any of the Windows collations). However, it is a definite difference of behavior between the two collations (again, just for non-EBCDIC VARCHAR data), and you might have code and/or customer expectations based upon the "String Sort" behavior. This requires testing your code and possibly researching to see if this change in behavior might have any negative impact on users.

  • Another difference between SQL_Latin1_General_CP1_CI_AS and Latin1_General_100_CI_AS is the ability to do Expansions on VARCHAR data (NVARCHAR data can already do these for most SQL_ Collations), such as handling æ as if it were ae:

    IF ('æ' COLLATE SQL_Latin1_General_CP1_CI_AS =
    'ae' COLLATE SQL_Latin1_General_CP1_CI_AS)
BEGIN
  PRINT 'SQL_Latin1_General_CP1_CI_AS';
END;

IF ('æ' COLLATE Latin1_General_100_CI_AS =
    'ae' COLLATE Latin1_General_100_CI_AS)
BEGIN
  PRINT 'Latin1_General_100_CI_AS';
END;

    Returns:

    Latin1_General_100_CI_AS

    The only thing you are "losing" here is not being able to do these expansions. Generally speaking, this is another benefit of moving to a Windows Collation. However, just like with the "String Sort" to "Word Sort" move, the same caution applies: it is a definite difference of behavior between the two collations (again, just for VARCHAR data), and you might have code and/or customer expectations based upon not having these mappings. This requires testing your code and possibly researching to see if this change in behavior might have any negative impact on users.

    (first noted in this S.O. answer by @Zarepheth: Can SQL Server SQL_Latin1_General_CP1_CI_AS be safely converted to Latin1_General_CI_AS?)

  • The server-level collation is used to set the collation of the system databases, which includes [model]. The [model] database is used as a template to create new databases, which includes [tempdb] upon each server startup. But, even with a change of server-level collation changing the collation of [tempdb], there is a somewhat easy way to correct for collation differences between the database that is "current" when CREATE #TempTable is executed and [tempdb]. When creating temporary tables, declare a collation using the COLLATE clause and specify a collation of DATABASE_DEFAULT:

    CREATE TABLE #Temp (Col1 NVARCHAR(40) COLLATE DATABASE_DEFAULT);

  • It is best to use the most recent version of the desired collation, if multiple versions are available. Starting in SQL Server 2005, a "90" series of collations was introduced, and SQL Server 2008 introduced a "100" series of collations. You can find these collations by using the following queries: 

    SELECT * FROM sys.fn_helpcollations() WHERE [name] LIKE N'%[_]90[_]%'; -- 476

SELECT * FROM sys.fn_helpcollations() WHERE [name] LIKE N'%[_]100[_]%'; -- 2686

    Since you are on SQL Server 2008 R2, you should use Latin1_General_100_CI_AS instead of Latin1_General_CI_AS.

  • A difference between the case-sensitive versions of these particular collations (i.e. SQL_Latin1_General_CP1_CS_AS and Latin1_General_100_CS_AS ) is in the order of upper-case and lower-case letters when doing case-sensitive sorting. This also affects single-character class ranges (i.e. [start-end]) that can be used with the LIKE operator and the PATINDEX function. The following three queries show this effect for both sorting and the character range.:

    SELECT tmp.col AS [Upper-case first]
FROM (VALUES ('a'), ('A'), ('b'), ('B'), ('c'), ('C')) tmp(col)
WHERE tmp.col LIKE '%[A-C]%' COLLATE SQL_Latin1_General_CP1_CS_AS
ORDER BY tmp.col COLLATE SQL_Latin1_General_CP1_CS_AS; -- Upper-case first

SELECT tmp.col AS [Lower-case first]
FROM (VALUES ('a'), ('A'), ('b'), ('B'), ('c'), ('C')) tmp(col)
WHERE tmp.col LIKE '%[A-C]%' COLLATE Latin1_General_100_CS_AS
ORDER BY tmp.col COLLATE Latin1_General_100_CS_AS; -- Lower-case first

SELECT tmp.col AS [Lower-case first]
FROM (VALUES (N'a'), (N'A'), (N'b'), (N'B'), (N'c'), (N'C')) tmp(col)
WHERE tmp.col LIKE N'%[A-C]%' COLLATE SQL_Latin1_General_CP1_CS_AS
ORDER BY tmp.col COLLATE SQL_Latin1_General_CP1_CS_AS; -- Lower-case first

    The only way to get upper-case to sort before lower-case (for the same letter) is to use one of the 31 Collations that supports that behavior, which is the Hungarian_Technical_* Collations and a handful of SQL_ Collations (which only support this behavior for VARCHAR data).

  • Less important for this particular change, but still good to know about since it would be impacting if changing the server to a binary or case-sensitive collation, is that the server level collation also affects:

    • local variable names
    • CURSOR names
    • GOTO labels
    • name resolution of the sysname datatype


    Meaning, if you or "the programmer who left recently" who is apparently responsible for all bad code ;-) were not careful about casing and declared a variable as @SomethingID but then referred to it as @somethingId later on, that would break if moving to a case-sensitive or binary collation. Similarly, code that uses the sysname datatype but refers to it as SYSNAME, SysName, or something other than all lower-case will also break if moved to an instance using a case-sensitive or binary collation.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.