none
Dynamic Sort Collation Puzzle: CS vs CI

    Question

  • Why is there no difference in results for CS (case sensitive) and CI (case insensitve) sorting?

    USE tempdb;
    SELECT ProductID, ProductName=Name, ListPrice, Color, New_ID=NEWID()
    INTO Product
    FROM AdventureWorks2008.Production.Product
    WHERE ProductID < 700
    UNION ALL
    SELECT ProductID, ProductName=lower(Name), ListPrice, Color, New_ID=NEWID()
    FROM AdventureWorks2008.Production.Product
    WHERE ProductID >= 700
    ORDER BY New_ID
    
    DECLARE @SQL nvarchar(max)='SELECT ProductID, ProductName, ListPrice,
     Color FROM Product
     ORDER BY ProductName '
    SET @SQL=@SQL + 'COLLATE SQL_Latin1_General_CP1250_CS_AS'
    PRINT @SQL
    EXEC sp_executeSQL @SQL 
    /*
    1	Adjustable Race	0.00
    879	all-purpose bike stand	159.00
    712	awc logo cap	8.99
    3	BB Ball Bearing	0.00
    2	Bearing Ball	0.00
    877	bike wash - dissolver	7.95
    316	Blade	0.00....
    */
    
    SET @SQL='SELECT ProductID, ProductName, ListPrice,
     Color FROM Product
     ORDER BY ProductName '
    SET @SQL=@SQL + 'COLLATE SQL_Latin1_General_CP1250_CI_AS'
    PRINT @SQL
    EXEC sp_executeSQL @SQL 
    GO
    /* ProductID	ProductName	ListPrice	Color
    1	Adjustable Race	0.00
    879	all-purpose bike stand	159.00
    712	awc logo cap	8.99
    3	BB Ball Bearing	0.00
    2	Bearing Ball	0.00
    877	bike wash - dissolver	7.95
    316	Blade	0.00.....
    */
    DROP TABLE tempdb.dbo.Product
    
    
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Sunday, July 25, 2010 8:07 AM

Answers

  • Just plaing with it

    CaseSensitivity can be either CI (case insensitive) or CS (case sensitive)

    AccentSensitivity can be either AI (accent insensitive) or AS (accent sensitive)

    BIN means that binary sort order is used, instead of text order

    CREATE TABLE T1
    (
      c1 VARCHAR(1) COLLATE Latin1_General_CI_AS,
      c2 VARCHAR(1) COLLATE Latin1_General_CS_AS
    )

    INSERT T1 VALUES('b', 'b')
    INSERT T1 VALUES('B', 'B')
    INSERT T1 VALUES('b', 'b')
    INSERT T1 VALUES('B', 'B')
    INSERT T1 VALUES('a', 'a')
    INSERT T1 VALUES('A', 'A')
    INSERT T1 VALUES('a', 'a')
    INSERT T1 VALUES('A', 'A')

    SELECT * FROM T1 ORDER BY c2

    c1   c2
    ---- ----
    a    a
    a    a
    A    A
    A    A
    b    b
    b    b
    B    B
    B    B

    SELECT * FROM T1 ORDER BY c1

    c1   c2
    ---- ----
    a    a
    A    A
    a    a
    A    A
    b    b
    B    B
    b    b
    B    B

    SELECT * FROM T1 ORDER BY c1 COLLATE Latin1_General_CS_AS

    c1   c2
    ---- ----
    a    a
    a    a
    A    A
    A    A
    b    b
    b    b
    B    B
    B    B


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 25, 2010 9:15 AM
  • Binary collation yields indeed different results.  But how about CS vs CI? Shouldn't they be different for the given sample?
    Kalman Toth
    Why? Bike still come before Blade no matter what case you use.
    If there were two Bikes, {BIKE, bike}, "bike" would come before "BIKE" if using case sensitive collation.


    DECLARE	@Sample TABLE
    	(
    		RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    		Data VARCHAR(100) NOT NULL
    	)
    
    INSERT @Sample VALUES ('Blade')
    INSERT @Sample VALUES ('bike')
    INSERT @Sample VALUES ('BIKE')
    
    SELECT Data FROM @Sample ORDER BY Data COLLATE SQL_Latin1_General_CP1250_CI_AS
    SELECT Data FROM @Sample ORDER BY Data COLLATE SQL_Latin1_General_CP1250_CS_AS
    
    Sunday, July 25, 2010 9:17 AM

All replies

  • Specify BINARY collation, see if that helps

    DECLARE

     

    @SQL nvarchar(max)

    SET

     

    @SQL='SELECT ProductID, ProductName, ListPrice,

    Color FROM Product

    ORDER BY ProductName '

    SET

     

    @SQL=@SQL + 'COLLATE Latin1_General_BIN'

    --SQL_Latin1_General_CP1250_CS_AS

    PRINT

     

    @SQL

    EXEC

     

    sp_executeSQL @SQL


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 25, 2010 8:48 AM
  • Binary collation yields indeed different results.  But how about CS vs CI? Shouldn't they be different for the given sample?
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Sunday, July 25, 2010 9:08 AM
  • Just plaing with it

    CaseSensitivity can be either CI (case insensitive) or CS (case sensitive)

    AccentSensitivity can be either AI (accent insensitive) or AS (accent sensitive)

    BIN means that binary sort order is used, instead of text order

    CREATE TABLE T1
    (
      c1 VARCHAR(1) COLLATE Latin1_General_CI_AS,
      c2 VARCHAR(1) COLLATE Latin1_General_CS_AS
    )

    INSERT T1 VALUES('b', 'b')
    INSERT T1 VALUES('B', 'B')
    INSERT T1 VALUES('b', 'b')
    INSERT T1 VALUES('B', 'B')
    INSERT T1 VALUES('a', 'a')
    INSERT T1 VALUES('A', 'A')
    INSERT T1 VALUES('a', 'a')
    INSERT T1 VALUES('A', 'A')

    SELECT * FROM T1 ORDER BY c2

    c1   c2
    ---- ----
    a    a
    a    a
    A    A
    A    A
    b    b
    b    b
    B    B
    B    B

    SELECT * FROM T1 ORDER BY c1

    c1   c2
    ---- ----
    a    a
    A    A
    a    a
    A    A
    b    b
    B    B
    b    b
    B    B

    SELECT * FROM T1 ORDER BY c1 COLLATE Latin1_General_CS_AS

    c1   c2
    ---- ----
    a    a
    a    a
    A    A
    A    A
    b    b
    b    b
    B    B
    B    B


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, July 25, 2010 9:15 AM
  • Binary collation yields indeed different results.  But how about CS vs CI? Shouldn't they be different for the given sample?
    Kalman Toth
    Why? Bike still come before Blade no matter what case you use.
    If there were two Bikes, {BIKE, bike}, "bike" would come before "BIKE" if using case sensitive collation.


    DECLARE	@Sample TABLE
    	(
    		RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    		Data VARCHAR(100) NOT NULL
    	)
    
    INSERT @Sample VALUES ('Blade')
    INSERT @Sample VALUES ('bike')
    INSERT @Sample VALUES ('BIKE')
    
    SELECT Data FROM @Sample ORDER BY Data COLLATE SQL_Latin1_General_CP1250_CI_AS
    SELECT Data FROM @Sample ORDER BY Data COLLATE SQL_Latin1_General_CP1250_CS_AS
    
    Sunday, July 25, 2010 9:17 AM
  • Thanks Uri & Peso. Following sample shows the difference in CS, CI and BIN sorting:

    -- Create test table with SELECT INTO
    USE tempdb;
    SELECT ProductID, ProductName=Name, ListPrice, Color, New_ID=NEWID()
    INTO Product
    FROM AdventureWorks2008.Production.Product
    UNION ALL
    SELECT ProductID, ProductName=lower(Name), ListPrice, Color, New_ID=NEWID()
    FROM AdventureWorks2008.Production.Product
    UNION ALL
    SELECT ProductID, ProductName=lower(Name), ListPrice, Color, New_ID=NEWID()
    FROM AdventureWorks2008.Production.Product
    ORDER BY New_ID
    /****** CASE SENSITIVE COLLATION - CS ******/
    DECLARE @SQL nvarchar(max)='SELECT ProductID, ProductName, ListPrice,
     Color FROM Product
     ORDER BY ProductName '
    SET @SQL=@SQL + 'COLLATE SQL_Latin1_General_CP1250_CS_AS'
    PRINT @SQL
    EXEC sp_executeSQL @SQL 
    /*
    ProductID	ProductName
    1	adjustable race
    1	adjustable race
    1	Adjustable Race
    879	all-purpose bike stand
    879	all-purpose bike stand
    879	All-Purpose Bike Stand....
    */
    /****** CASE INSENSITIVE COLLATION - CI ******/
    SET @SQL='SELECT ProductID, ProductName, ListPrice,
     Color FROM Product
     ORDER BY ProductName '
    SET @SQL=@SQL + 'COLLATE SQL_Latin1_General_CP1250_CI_AS'
    PRINT @SQL
    EXEC sp_executeSQL @SQL 
    /* ProductID	ProductName
    1	adjustable race
    1	Adjustable Race
    1	adjustable race
    879	All-Purpose Bike Stand
    879	all-purpose bike stand
    879	all-purpose bike stand
    .....
    */
    /****** BINARY COLLATION ******/
    SET @SQL ='SELECT ProductID, ProductName, ListPrice,
     Color FROM Product
     ORDER BY ProductName '
    SET @SQL=@SQL + 'COLLATE Latin1_General_100_BIN'
    PRINT @SQL
    EXEC sp_executeSQL @SQL 
    GO
    /* ProductID	ProductName
    712	AWC Logo Cap
    1	Adjustable Race
    879	All-Purpose Bike Stand
    3	BB Ball Bearing
    2	Bearing Ball
    */
    DROP TABLE tempdb.dbo.Product
    
    
    

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Sunday, July 25, 2010 12:24 PM