Sql-server – Carriage return/line feed stopped working in SQL Server

If I do a Paste Special, I get two options, Text and Unicode, and selecting Text seems to work properly, where as Unicode does not. as per comment by @JonSeigel

The official word from Microsoft:

Some of the columns that contain pre-defined strings (like types, system descriptions, and constants) are always fixed to a specific collation – Latin1_General_CI_AS_KS_WS. This is irrespective of instance/database collation. The reason is that this is system metadata (not user metadata) and basically these strings are treated case insensitive (like keywords, so always Latin).

Other columns in system tables that contain user metadata like object names, column names, index names, login names, etc. take the instance or database collation. The columns are collated to proper collation at the time of installation of SQL Server in case of instance collation & at the time of creation of database in case of database collation.

You asked (emphasis mine):

Why is the collation of these columns statically set?

The reason some columns are statically set is so that queries don't need to worry about server or database collation (more importantly: CaSe SenSiTIviTy) to work correctly. This query will always work regardless of collation:

SELECT * FROM sys.databases WHERE state_desc = N'ONLine';

Whereas if server collation were case sensitive, the query above would return 0 rows, just like this does:

  SELECT * FROM sys.databases 
  WHERE state_desc COLLATE Albanian_BIN = N'ONLine';

For example, if you install an instance of SQL Server with SQL_Estonian_CP1257_CS_AS collation, then run the following:

SELECT name, collation_name 
FROM master.sys.all_columns
WHERE collation_name IS NOT NULL
AND [object_id] = OBJECT_ID(N'sys.databases');

You will see these results (or something similar, depending on your version of SQL Server):

name                            SQL_Estonian_CP1257_CS_AS
collation_name                  SQL_Estonian_CP1257_CS_AS
user_access_desc                Latin1_General_CI_AS_KS_WS
state_desc                      Latin1_General_CI_AS_KS_WS
snapshot_isolation_state_desc   Latin1_General_CI_AS_KS_WS
recovery_model_desc             Latin1_General_CI_AS_KS_WS
page_verify_option_desc         Latin1_General_CI_AS_KS_WS
log_reuse_wait_desc             Latin1_General_CI_AS_KS_WS
default_language_name           SQL_Estonian_CP1257_CS_AS
default_fulltext_language_name  SQL_Estonian_CP1257_CS_AS
containment_desc                Latin1_General_CI_AS_KS_WS
delayed_durability_desc         SQL_Estonian_CP1257_CS_AS

Now, to demonstrate metadata views that inherit database collation, rather than inheriting server collation from the master database:

CREATE DATABASE server_collation;
GO
CREATE DATABASE albanian COLLATE Albanian_BIN;
GO
CREATE DATABASE hungarian COLLATE Hungarian_Technical_100_CS_AI;
GO

SELECT name, collation_name 
  FROM server_collation.sys.all_columns 
  WHERE collation_name IS NOT NULL 
  AND object_id = -391; -- sys.columns

SELECT name, collation_name 
  FROM albanian.sys.all_columns 
  WHERE collation_name IS NOT NULL 
  AND object_id = -391; -- sys.columns

SELECT name, collation_name 
  FROM hungarian.sys.all_columns 
  WHERE collation_name IS NOT NULL 
  AND object_id = -391; -- sys.columns

Results:

server_collation
----------------
name                                 SQL_Estonian_CP1257_CS_AS
collation_name                       SQL_Estonian_CP1257_CS_AS
generated_always_type_desc           Latin1_General_CI_AS_KS_WS
encryption_type_desc                 Latin1_General_CI_AS_KS_WS
encryption_algorithm_name            Latin1_General_CI_AS_KS_WS
column_encryption_key_database_name  SQL_Estonian_CP1257_CS_AS


albanian
----------------
name                                 Albanian_BIN
collation_name                       Albanian_BIN
generated_always_type_desc           Latin1_General_CI_AS_KS_WS
encryption_type_desc                 Latin1_General_CI_AS_KS_WS
encryption_algorithm_name            Latin1_General_CI_AS_KS_WS
column_encryption_key_database_name  Albanian_BIN


hungarian
----------------
name                                 Hungarian_Technical_100_CS_AI
collation_name                       Hungarian_Technical_100_CS_AI
generated_always_type_desc           Latin1_General_CI_AS_KS_WS
encryption_type_desc                 Latin1_General_CI_AS_KS_WS
encryption_algorithm_name            Latin1_General_CI_AS_KS_WS
column_encryption_key_database_name  Hungarian_Technical_100_CS_AI

So you can see that in this case several columns inherit the database collation, while others are fixed to this "generic" Latin1 collation, meaning it is used to insulate certain names and properties from case sensitivity issues as described above.

If you try to perform a UNION, for example:

SELECT name FROM albanian.sys.columns
UNION ALL
SELECT name FROM server_collation.sys.columns;

You get this error:

Msg 451, Level 16, State 1
Cannot resolve collation conflict between "Albanian_BIN" and "SQL_Estonian_CP1257_CS_AS" in UNION ALL operator occurring in SELECT statement column 1.

Similarly, if you try to perform a PIVOT or UNPIVOT, the rules are even stricter (the output types must all match exactly rather than merely be compatible), but the error message is far less helpful, and even misleading:

Msg 8167, Level 16, State 1
The type of column "column name" conflicts with the type of other columns specified in the UNPIVOT list.

You need to work around these errors using explicit COLLATE clauses in your queries. For example, the union above could be:

SELECT name COLLATE Latin1_General_CI_AS_KS_WS
  FROM albanian.sys.columns
UNION ALL
SELECT name COLLATE Latin1_General_CI_AS_KS_WS
  FROM server_collation.sys.columns;

The only time this may cause issues is you'll get confusing output if a collation is forced but doesn't contain the same character representation, or if sorting is used and the forced collation uses a different sort order than the source.