Between & Collation

If you run the script as follows on SQL 2012, what results you will get?

create table #test(name varchar(10) COLLATE Latin1_General_CS_AS);
Go
insert #test ( name ) values ( 'B');
insert #test ( name ) values ( 'b');
insert #test ( name ) values ( '.B');
insert #test ( name ) values ( 'b.');
insert #test ( name ) values ( 'C');
insert #test ( name ) values ( 'c');
insert #test ( name ) values ( 'D');
insert #test ( name ) values ( 'd');

select name from #test where name between 'B' and 'd' order by 1;

drop table #test;

Choose your answer:

B, b., c, C, d B, b., C, c, d B, b, b., C, c, D, d b, b., c, C, D, d B, b, b., c, C, d

Correct answer:

B, b., c, C, d

Explanation:

Per MSDN: Distinguishes between uppercase and lowercase letters. If selected, lowercase letters sort ahead of their uppercase versions. If this option is not selected, the collation will be case-insensitive. That is, SQL Server considers the uppercase and lowercase versions of letters to be identical for sorting purposes. You can explicitly select case insensitivity by specifying _CI. Ref: Collation and Unicode Support - http://msdn.microsoft.com/en-us/library/ms143726(v=sql.110).aspx