raistlinx - Saturday, June 25, 2011 1:37 AM
Hi there. I am going to have to say "no" to the possibility of performance issues in this scenario. Database-level collation is used for:
-- DB collation is Latin1_General_100_BIN2 ; tempdb collation is SQL_Latin1_General_CP1_CI_AS
CREATE TABLE dbo.ColumnNames(col1 INT, Col1 INT, COL1 INT);
DROP TABLE dbo.ColumnNames;
DECLARE @Table TABLE (col1 INT, Col1 INT, COL1 INT);
/*
Msg 2705, Level 16, State 3, Line XXXXX
Column names in each table must be unique.
Column name 'Col1' in table '@Table' is specified more than once.
*/
CREATE TABLE #ColumnNames(col1 INT, Col1 INT, COL1 INT);
/*
Msg 2705, Level 16, State 3, Line XXXXX
Column names in each table must be unique.
Column name 'Col1' in table '#ColumnNames' is specified more than once.
*/
GO
and:
-- DB collation is Latin1_General_100_BIN2 ; tempdb collation is SQL_Latin1_General_CP1_CI_AS
DECLARE @ComputedColumnCollation TABLE
(
[ID] INT NOT NULL,
[SomeChar] AS (NCHAR([ID])),
[String] VARCHAR(10)
);
SELECT *
INTO dbo._comp
FROM @ComputedColumnCollation;
SELECT col.[name], col.[user_type_id], col.[collation_name], col.[is_nullable]
FROM sys.columns col
WHERE col.[object_id] = OBJECT_ID(N'dbo._comp')
ORDER BY col.[column_id];
/*
name user_type_id collation_name is_nullable
ID 56 NULL 0
SomeChar 239 SQL_Latin1_General_CP1_CI_AS 1
String 167 Latin1_General_100_BIN2 1
*/
DROP TABLE dbo._comp;
Both of those cases are functional issues, not performance issues.
What this comes down to is: whatever collation is being used by any string column (minus XML) is the collation that must be used in any work table, version store, trigger / OUTPUT clause table (i.e. INSERTED and DELETED), etc. And this has nothing to do with the collation of either DB or of the instance since every field of a result set can use a different collation. If the collation used for fields in these system-created work tables was different than the collation of the field the column is intended to capture, then you could end up with data loss for VARCHAR data if the code page was different. At the very least you would get the beloved collation mismatch error.
For more details on what areas are impacted by which level of collation, please see:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
For info on working with collations in general, please visit: Collations Info
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR