none
Collate Database_Default

    Question

  • im trying to figure out what a particular peice of code does, but im not sure what Collate Database_Default does.

    Ive looked it up on the msdn site, but I cant make any sense of it. Can anyone explain what that particular statement does?

     

     


    Declare
    @ReportPath VarChar(256)
    Select @ReportPath = GS.KeyValue From General.Settings GS Where GS.KeyName = 'Star Report Folder'
            Insert into Security.Report (Name)
                  Select C.Path From SSRS.Catalog C
            Where C.Path Collate Database_Default Like @ReportPath + '/%'
                  And C.Path Collate Database_Default Not In (Select Name From Security.Report R)
            Delete From Security.Report
                  Where Name Not In (Select C.Path Collate Database_Default From SSRS.Catalog C Where c.Path Like @ReportPath + '%')

    Friday, April 17, 2009 8:36 PM

Answers

  • Using collate in a TSQL query overides the existing collation and sets the column = to a different collation, usually for comparison.  For example, say your column has a case sensitive collation and the database does not.  If you want to query the data in a case insensitve manner, you will need to change the collation for the column, or render the column to a case insensitive collation at execution time.

    Often collations do not play nice together, inwhich case you will need to make the two collations equal, so the db engine can render the data the same.  Collation affects the way data sorts and compares.

    Here is a sample.

    declare @t table(
    
    id int,
    
    c1 CHAR(1) collate SQL_Latin1_General_CP1_CS_AS --make the column case sensitive
    
    );
    
    
    
    INSERT INTO @T VALUES(1,'A');
    
    INSERT INTO @T VALUES(2,'B');
    
    INSERT INTO @T VALUES(3,'C');
    
    INSERT INTO @T VALUES(4,'D');
    
    INSERT INTO @T VALUES(5,'E');
    
    INSERT INTO @T VALUES(6,'F');
    
    
    
    select *
    
    from @t
    
    where c1  = 'a'
    
    
    
    --the above yields no results because the column is in a case sensitive collation
    
    
    
    select *
    
    from @t
    
    where c1  collate database_default = 'a'
    
    
    
    --this yields results because my default database collation is SQL_Latin1_General_CP1_CI_AS, which is not case sensitive.
    
    
    Friday, April 17, 2009 8:55 PM
    Moderator

All replies

  • Using collate in a TSQL query overides the existing collation and sets the column = to a different collation, usually for comparison.  For example, say your column has a case sensitive collation and the database does not.  If you want to query the data in a case insensitve manner, you will need to change the collation for the column, or render the column to a case insensitive collation at execution time.

    Often collations do not play nice together, inwhich case you will need to make the two collations equal, so the db engine can render the data the same.  Collation affects the way data sorts and compares.

    Here is a sample.

    declare @t table(
    
    id int,
    
    c1 CHAR(1) collate SQL_Latin1_General_CP1_CS_AS --make the column case sensitive
    
    );
    
    
    
    INSERT INTO @T VALUES(1,'A');
    
    INSERT INTO @T VALUES(2,'B');
    
    INSERT INTO @T VALUES(3,'C');
    
    INSERT INTO @T VALUES(4,'D');
    
    INSERT INTO @T VALUES(5,'E');
    
    INSERT INTO @T VALUES(6,'F');
    
    
    
    select *
    
    from @t
    
    where c1  = 'a'
    
    
    
    --the above yields no results because the column is in a case sensitive collation
    
    
    
    select *
    
    from @t
    
    where c1  collate database_default = 'a'
    
    
    
    --this yields results because my default database collation is SQL_Latin1_General_CP1_CI_AS, which is not case sensitive.
    
    
    Friday, April 17, 2009 8:55 PM
    Moderator
  • You see, why couldnt msdn  explain it like that?

    Makes a lot more sense.

    Cheers Adam!
    Friday, April 17, 2009 9:37 PM
  • Thanks Adam very clear now 
    Thursday, September 13, 2012 1:30 PM