I have the following delimma create table a (a varchar(100),aa varchar(100),aaa varchar(100))
insert into a values ('a_','a_','a_'); insert into a values ('_b','_b','_b'); insert into a values ('_a','_a','_a');
select * from a order by 1
In SQL Server 2005 the above select returns _a _a _a _b _b _b a_ a_ a_
In Teradata the above select returns a_ a_ a_ _a _a _a _b _b _b
Clearly the underscore character is represented differently in both the databases. I am using Code Page 1, Case Insensitive , Ascending Sort collation in SQL Server. I need to use the UNICODE character set in Teradata, but what about the collation? Which one will provide me with the default sort which is similar to the one which SQL Server is returning.
The default Teradata MULTINATIONAL collation sequence may do what you want. I don't think it matches SQL Server 100%, but it should sort underscores before letters. If all else fails, and this is an absolute requirement, it's possible for your system administrator to install a user-defined collation sequence.
To change collation for the current session: SET SESSION COLLATION MULTINATIONAL;
To change default collation for future sessions for a user: MODIFY USER myusername AS COLLATION = MULTINATIONAL;