Sort Data for the Merge and Merge Join Transformations

Question

How could you sort the string values N'a-' and N'-a-' using T-SQL so as to be N'a-' less than N'-a-' ??

When I use Sort transformation in my system, N'a-' is sorted as less than N'-a-'.
When I use T-SQL, I cannot find any collation that makes N'a-' to be sorted as less than N'-a-'.

Let me express the same question in a second more obvious way:

Seems that the following https://learn.microsoft.com/en-us/sql/integration-services/data-flow/transformations/sort-data-for-the-merge-and-merge-join-transformations?view=sql-server-ver16&source=docs#to-provide-string-values-that-are-sorted-by-using-windows-collation is not correct for the case above:

Thank you !

Answer 1

Hi @Georgios Polychronakos,

SSIS uses the Windows collation (Case-Sensitive) and SQL Server uses SQL collation (Case-Insensitive, by default). You can either adjust the SQL sort to SSIS or the SSIS sort to SQL.

So it shows the different result when you compare N'a-' and N'-a-' using T-SQL and SSIS.

As said in Sort Transformation.

You may also refer to Sorting in SQL vs sorting in SSIS

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

DECLARE
 @ConcatenatedTsqlStatement [varchar](MAX)
 ,@TemplateString [varchar](MAX) =
  N'SELECT @ConcatenatedCollationNames+= CASE WHEN N''a-'' COLLATE %s < N''-a-'' COLLATE %s THEN ''%s'' ELSE '''' END;'
 ,@CrLf [char](2) = CHAR(13) + CHAR(10);

SET @ConcatenatedTsqlStatement =
  'DECLARE @ConcatenatedCollationNames [varchar](32) = '''';' + @CrLf
  + (SELECT STRING_AGG(REPLACE(@TemplateString, N'%s', [name]), @CrLf) FROM [sys].[fn_helpcollations]()) + @CrLf
  + ';SELECT @ConcatenatedCollationNames';

PRINT @ConcatenatedTsqlStatement;

EXEC(@ConcatenatedTsqlStatement);