• Solomon Rutzky

    SSCoach

    Points: 15887

    raistlinx - Saturday, June 25, 2011 1:37 AM

    I'm talking about potential performance issues of Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting. And specifically for 2008.I'm not creating any temp tables myself or manually interacting with tempdb in anyway. I'm asking about the performance hit (if any) of the server collation being SQL_Latin1_General_CI_AS and the db collation being the windows Latin1_General_CI_AS while the engine is resolving a query from only the single database in question where it decides to use tempdb (assuming it still would given the different collations). There is no explicit tempdb usage by the person executing the query.

    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:

    1. the contents of string literals and variables / parameters. Neither of those come into play here.
    2. the default collation used when creating / altering columns in a "CREATE TABLE" / "ALTER TABLE" statement when not using the "COLLATE" clause. That is also not happening here. Tables created from a "SELECT ... INTO..." use the collation of the field in the result set that is being used to create the table.
    3. database-level meta-data (e.g. object names, user names, etc). This is also a non-issue for this scenario
    Basically, if you create a table in some other DB that uses some other collation than the DB you are executing the query from (i.e. the active DB), then the most it could impact is column-name resolution (since that is meta-data which is controlled by the DB containing the object) and the collation of computed columns that don't specify the "COLLATE" clause. Below is an example of each of those cases:

    -- 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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR