Mudando do SQL 2005 [SQL_Latin1_General_CP1_CI_AS] para 2008 - perderei todos os recursos usando 'compatibilidade com versões anteriores'

Em primeiro lugar, peço desculpas por uma resposta tão longa, pois sinto que ainda há muita confusão quando as pessoas falam sobre termos como agrupamento, ordem de classificação, página de código etc.

De BOL :

Os agrupamentos no SQL Server fornecem regras de classificação, maiúsculas e minúsculas e propriedades de diferenciação de acentos para seus dados . Os agrupamentos usados ​​com tipos de dados de caractere, como char e varchar, determinam a página de código e os caracteres correspondentes que podem ser representados para esse tipo de dados. Esteja você instalando uma nova instância do SQL Server, restaurando um backup de banco de dados ou conectando o servidor a bancos de dados do cliente, é importante entender os requisitos de localidade, ordem de classificação e distinção entre maiúsculas e minúsculas e acentos dos dados com os quais você trabalhará .

Isso significa que Collation é muito importante, pois especifica regras sobre como as cadeias de caracteres dos dados são classificadas e comparadas.

Nota: Mais informações sobre COLLATIONPROPERTY

Agora vamos primeiro entender as diferenças ......

Executando abaixo do 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

Os resultados seriam:

Olhando para os resultados acima, a única diferença é a ordem de classificação entre os 2 agrupamentos. Mas isso não é verdade, o que você pode ver abaixo:

Teste 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

Resultados do Teste 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.

Nos resultados acima, podemos ver que não podemos comparar diretamente os valores em colunas com agrupamentos diferentes, você deve usar COLLATEpara comparar os valores da coluna.

TESTE 2:

A principal diferença é o desempenho, como aponta Erland Sommarskog nesta discussão no 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')

--- Criar índices em ambas as tabelas

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)

--- Execute as consultas

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

--- Isto terá Conversão IMPLÍCITA

--- Execute as consultas

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

--- Isso NÃO terá Conversão IMPLÍCITA

O motivo da conversão implícita é porque eu tenho meu agrupamento de banco de dados e servidor como SQL_Latin1_General_CP1_CI_ASe a tabela Table_Latin1_General_CI_AS tem comentários de coluna definidos como VARCHAR(50)COLLATE Latin1_General_CI_AS , portanto, durante a pesquisa, o SQL Server precisa fazer uma conversão IMPLICIT.

Teste 3:

Com a mesma configuração, agora vamos comparar as colunas varchar com os valores nvarchar para ver as mudanças nos planos de execução.

-- executar a consulta

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

-- executar a consulta

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

Observe que a primeira consulta é capaz de fazer busca de índice, mas tem que fazer conversão implícita, enquanto a segunda faz uma varredura de índice que se mostra ineficiente em termos de desempenho quando varre tabelas grandes.

Conclusão:

• Todos os testes acima mostram que ter o agrupamento correto é muito importante para a instância do servidor de banco de dados.
• 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 :

• Storage and Performance Effects of Unicode
• Working with Collations
• Collation and Unicode Support
• Code Page Architecture

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.