Post reply

Using collate: When to use and when not to use

  • October 15, 2014 at 4:25 am

    #291018

    In one of my application we had tsql like shown below

    SELECT DISTINCT [Dblist].[DbName] AS [DbName] FROM [Common].dbo.[Dblist] WHERE dbname not in (SELECT [name] FROM master.dbo.sysdatabases )

    In one of my customer it gave error related to collattion as the database Common(part of my application) which is having default collation sql_latin1_general_cp1_ci_as where as master db has collation latin1_general_ci_as , So then i found solution of using collate and it worked.And I also want the solution to work on all collations.

    SELECT DISTINCT [Dblist].[DbName] AS [DbName] FROM [Common].dbo.[Dblist] WHERE dbname not in (SELECT [name] COLLATE DATABASE_DEFAULT FROM master.dbo.sysdatabases )

    Now I am confused like should I use COLLATE in all the queries which involves system databases? When to use collate and when not to use?

    Also the above way of using collate is proper(like COLLATE DATABASE_DEFAULT)? Here DATABASE_DEFAULT is latin1_general_ci_as itself as it is collation of master database. Then how the solution worked as they again don't match with Common database?I want solution which will work on all collations.

  • October 15, 2014 at 7:11 am

    #1752519

    COLLATE DATABASE_DEFAULT is the only way to make it work with all possible collations. The other way is an explicit collation name, but the effect is the same as DATABASE_DEFAULT.

    Other than that, you would have to make sure the system databases have the same collation as your database (or, more easily, the other way around), which is not always possible.

    -- Gianluca Sartori

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

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