Look the given script what output you are expecting.
declare @myTable Table (fname nvarchar(100),lname nvarchar(100))
insert into @myTable values('test1','test2')
insert into @myTable values('aaa','test2')
insert into @myTable values('aa a','test2')
insert into @myTable values('a','test2')
insert into @myTable values('aa','test2')
insert into @myTable values('b','test2')
insert into @myTable values('vv','test2')
insert into @myTable values('aakash','test2')
insert into @myTable values('ab','test2')
insert into @myTable values('å','test2')
insert into @myTable values('ss','test2')
select * from @myTable where fname like '[a-z]%'
I was expecting all the records. But I couldn’t get all,
after that I was searching something related to collation and found that client
was created this db with collation Danish_Norwegian_CS_AS.
Then I tried to change the collation in sql itself and got
the result what I was expecting.
select * from @myTable where fname like '[a-z]%' COLLATE
SQL_Latin1_General_Cp437_BIN
The problem is facing only with aa, the reason for this is in Norwegian collation ‘aa’ will consider as a single character and will give the different
result, while sorting also we will get something different output