Recently I was investigating an issue related to ordering data. As a test, I ran the following script:
declare @tbl table ( ProductNumber varchar(25) );
insert into @tbl select ‘BBBB’;
insert into @tbl select ‘AAAA’;
insert into @tbl select ‘A-B’;
insert into @tbl select ‘A123′;
select * from @tbl order by ProductNumber asc; – Sorting data from a table variable
create table #tbl ( ProductNumber varchar(25) );
insert into #tbl select ‘BBBB’;
insert into #tbl select ‘AAAA’;
insert into #tbl select ‘A-B’;
insert into #tbl select ‘A123′;
select * from #tbl order by ProductNumber asc;
drop table #tbl;
And it gave me this result:
As you can see, the order of the data coming from a temporary table is different than the data from a table variable, even though the same data type is used. At first I thought, how on earth is this possible? After spending some time pondering about this problem (and after a colleague reported that when he executed the above script, the result was as expected), it came to me. Don’t tell me it’s a collation problem?! Well, it is. (In case you don’t know collation, in short “Collations specify the rules for how strings of character data are sorted and compared”. More info through that previous link and here.)
When I executed the script, my Management Studio session was connected to AdventureWorks2008, one of my test databases. When I switched to tempdb, the result was normal – both queries returned the same result. Then I had a look at the collations. My tempdb is using Latin1_General_CI_AS while the AdventureWorks2008 database is apparently using SQL_Latin1_General_CP1_CI_AS.
A temporary table is created in the tempdb and thus uses the collation of the tempdb for its string columns. And a table variable uses the same collation as the database to which the session is connected. Which is quite logical because otherwise you would get collation conflicts when using the table variable in combination with a table from the active database in the same query. I will demonstrate that with the following script:
insert into @tbl select ‘BBBB’;
select * from AdventureWorks.Production.Product P
where P.ProductNumber in (select ProductNumber from @tbl);
On my (SQL Server 2008) server I also have the old SQL2005 AdventureWorks database up and running, which is using the Latin1_General_CI_AS collation. Executing the above script (which references that AdventureWorks DB) while being connected to AdventureWorks2008 (or any other DB that uses a different collation from Latin1_General_CI_AS) results in the following error:
Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Changing the active database to any other DB that uses the Latin1_General_CI_AS collation and then executing the query results in a positive execution.
Now, to get back to the initial issue of sorting inconsistency, even though the collations are not exactly the same, they are both Latin1, Case Insensitive, Accent Sensitive and still they don’t sort the data in the same way?? For an explanation on that I found the following page on the Microsoft Support site: Comparing SQL collations to Windows collations. In short: a Windows collation (such as Latin1_General_CI_AS) uses a different comparison algorithm than a SQL collation (SQL_Latin1_General_CP1_CI_AS). A Windows collation uses the same algorithm as for Unicode data, even when the data is non-Unicode.
All that means is that in our test example, in order to get the data in the right order all the time, we could switch to Unicode fields:
declare @tbl table ( ProductNumber nvarchar(25) );
insert into @tbl select ‘BBBB’;
insert into @tbl select ‘AAAA’;
insert into @tbl select ‘A-B’;
insert into @tbl select ‘A123′;
select * from @tbl order by ProductNumber asc; – Sorting data from a table variable
create table #tbl ( ProductNumber nvarchar(25) );
insert into #tbl select ‘BBBB’;
insert into #tbl select ‘AAAA’;
insert into #tbl select ‘A-B’;
insert into #tbl select ‘A123′;
select * from #tbl order by ProductNumber asc;
drop table #tbl;
Executing that returns the following result, no matter what the active database is:
And it also means that you need to be careful when retrieving data from different sources (which is how I came across the issue in the first place). If you need to have your data sorted in a particular, consistent way and you’re doing that by using an ORDER BY in a SELECT statement on the source system, double-check if all sources are using the same collation!
In case the above left you wondering about what collation to use: the SQL collations are there for backward compatibility. For new developments use the Windows collations!
See my Fun With Strings article for another story related to collation.
Additional reference material:
SQL Server Central – Comparing Table Variables with Temporary Tables
