Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
If you think you may get into Hekaton in SQL Server 2014, here's something else you may want to consider reading. –  Aaron Bertrand Aug 20 '13 at 17:20

1 Answer 1

up vote 8 down vote accepted

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 :

share|improve this answer
    
Great answer thank you! Going to take some time and read through all the links properly, will reply, thanks again! –  Peter PitLock Aug 21 '13 at 7:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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