A Nice Demo Example on Collations from Microsoft SQL Server 2012 Internals

--This example demonstrates that a column data can be presented with diffrent collations via CTE

USE MyDB
GO

CREATE TABLE #words
    (
      word NVARCHAR(20) NOT NULL ,
      wordno TINYINT PRIMARY KEY CLUSTERED
    );

INSERT  #words
VALUES  ( N'cloud', 1 ),
        ( N'CSAK', 6 ),
        ( N'cukor', 11 ),
        ( N'Oblige', 2 ),
        ( N'Opera', 7 ),
        ( N'?l', 12 ),
        ( N'résumé', 3 ),
        ( N'RESUME', 8 ),
        ( N'R?SUM?', 13 ),
        ( N'resume', 4 ),
        ( N'resumes', 9 ),
        ( N'résumés', 14 ),
        ( N'?IL', 5 ),
        ( N'?il', 10 );

-- Latin1_General_CI_AS
WITH    collatedwords ( collatedword, wordno )
          AS ( SELECT   word COLLATE Latin1_General_CI_AS ,
                        wordno
               FROM     #words
             )
    SELECT  collatedword ,
            rank = DENSE_RANK() OVER ( ORDER BY collatedword ) ,
            wordno
    FROM    collatedwords
    ORDER BY collatedword;

-- Latin1_General_CS_AS
WITH    collatedwords ( collatedword, wordno )
          AS ( SELECT   word COLLATE Latin1_General_CS_AS ,
                        wordno
               FROM     #words
             )
    SELECT  collatedword ,
            rank = DENSE_RANK() OVER ( ORDER BY collatedword ) ,
            wordno
    FROM    collatedwords
    ORDER BY collatedword;

-- Latin1_General_CI_AI
WITH    collatedwords ( collatedword, wordno )
          AS ( SELECT   word COLLATE Latin1_General_CI_AI ,
                        wordno
               FROM     #words
             )
    SELECT  collatedword ,
            rank = DENSE_RANK() OVER ( ORDER BY collatedword ) ,
            wordno
    FROM    collatedwords
    ORDER BY collatedword;

--Hungarian_CI_AI
/* Note: The words CSAK and ?l now sort after cukor and Opera. This is because in the Hungarian alphabet,
  CS and ? are letters on their own. You can also see that in this CI_AI collation, all four forms
  of résumé have the same rank.
*/
WITH    collatedwords ( collatedword, wordno )
          AS ( SELECT   word COLLATE Hungarian_CI_AI ,
                        wordno
               FROM     #words
             )
    SELECT  collatedword ,
            rank = DENSE_RANK() OVER ( ORDER BY collatedword ) ,
            wordno
    FROM    collatedwords
    ORDER BY collatedword;