Post reply

Applying COLLATION Clause to string expressions

  • September 15, 2022 at 6:31 pm

    #4088231

    Hello Experts

    I understand that one can’t “just join” tables that have different collations, because the concept of “equal” depends on whether you care about things like differing case, or not. Fair enough. So, in such cases, you need to add either a COLLATE collation_name clause or a COLLATE DATABASE_DEFAULT clause to the string(s) in the expression, to clarify what you mean. Also fair enough.

    (Acknowledgements to https://www.mssqltips.com/sqlservertip/4395/understanding-the-collate-databasedefault-clause-in-sql-server/ , from which the examples were copied. The article doesn’t seem to answer my question though)

    I am using SQL Server 2019 with a server collation of Latin1_General_CI_AS_KS as the fairly standard case insensitive choice. This will therefore be the collation used in master and tempdb.

    First task is to create a DB with a case sensitive collation and create a case-sensitive table in it with some test data:

    CREATE DATABASE [CaseSensitiveDB]
    COLLATE Latin1_General_CS_AS_KS
    GO

    USE CaseSensitiveDB
    GO
    CREATE TABLE dbo.PermTable
    ( Value VARCHAR(100) COLLATE DATABASE_DEFAULT ) -- picks up collation from latest USE statement (could also use COLLATE Latin1_General_CS_AS_KS)
    GO
    -- Insert two records, differing only in case
    INSERT INTO dbo.PermTable (Value) VALUES ('RECORD 1'),('Record 2')

    Then, create a non-CS table in tempdb for testing my joining behaviour.

    USE master -- switch to server collation as default
    GO
    CREATE TABLE #TableWithoutCollateDB -- in tempdb
    ( Value VARCHAR(100) )
    GO
    -- Insert two identical records
    INSERT INTO #TableWithoutCollateDB (Value) VALUES ('Record 1'),('Record 2')

    Then, run tests:

    -- Now, how many records are returned when we join them?
    USE CaseSensitiveDB -- so database default collation wil be case sensitive.
    GO

    -- Test 1: I would expect this to give a collation conflict as PT and TA have different collations
    SELECT COUNT(*) 
    FROM [CaseSensitiveDB].dbo.PermTable PT
    JOIN #TableWithoutCollateDB TA
    ON PT.Value COLLATE DATABASE_DEFAULT = TA.Value
    GO

    -- Test 2: This returns 1 record as expected as both collations are case sensitive
    SELECT COUNT(*) 
    FROM [CaseSensitiveDB].dbo.PermTable PT
    JOIN #TableWithoutCollateDB TA
    ON PT.Value = TA.Value COLLATE DATABASE_DEFAULT 
    GO

    -- Test 3: Same result as test 2
    SELECT COUNT(*)
    FROM [CaseSensitiveDB].dbo.PermTable PT
    JOIN #TableWithoutCollateDB TA
    ON PT.Value COLLATE DATABASE_DEFAULT = TA.Value COLLATE DATABASE_DEFAULT 
    GO

    So, my question is, as expressed by Test1: Why is there no difference in the effect of the commands used in the tests? I would have have expected am error In Test 1. Conversely, if I had issued a USE master before testing, thereby making the database default collation non-case-sensitive, I would have expected an error on Test 2.

    Please enlighten. Thanks in advance.

    Hopeful Kiwi

    • This topic was modified 2 years, 2 months ago by  Mark Dalley. Reason: Fixed misplaced parenthesis, typos

  • September 15, 2022 at 8:25 pm

    #4088285 

    -- Your
    CREATE TABLE dbo.PermTable
    ( Value VARCHAR(100) ) COLLATE DATABASE_DEFAULT

    -- Should be
    CREATE TABLE dbo.PermTable
    ( Value VARCHAR(100) COLLATE DATABASE_DEFAULT );

    -- or just
    CREATE TABLE dbo.PermTable
    ( Value VARCHAR(100));

  • September 16, 2022 at 5:15 pm

    #4088613

    Thanks Ken, fixed.

    MarkD

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply