ORDER BY changes String Concatenation SQL

Question

I was trying to build a script that would drop all foreign keys in a particular schema (I'm working on creating a new database which will need to be scripted to several other locations, a test environment and a production one). The easiest way to have these three separate environments is to work with my "CREATE" script on "development", and then stage that through our other environments at a later date. This requires (as one possible solution) that the beginning of the "CREATE" script is a sort of If Exists(...) DROP....

I have solved the problem I was having, but was hoping that someone could explain the behavior I was seeing (see below).

This code works (I get a statement that has all records as their own SQL statements):

DECLARE @SQL VARCHAR(MAX) = ''
DECLARE @Schema VARCHAR(100) = 'SchemaName'
SELECT 
    @SQL = @SQL
         + 'ALTER TABLE ' + tpa.name + CHAR(13) + CHAR(10)
         + CHAR(9) + 'DROP CONSTRAINT ' + fk.name +';' + CHAR(13) + CHAR(10)
FROM 
    sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc 
    ON fkc.constraint_object_id = fk.object_id
JOIN sys.tables AS tpa
    ON fk.parent_object_id = tpa.object_id
JOIN sys.columns AS cpa 
    ON tpa.object_id = cpa.object_id AND 
       fkc.parent_column_id = cpa.column_id
JOIN sys.tables AS tref
    ON fkc.parent_object_id = tref.object_id
JOIN sys.columns AS cref 
    ON tref.object_id = cref.object_id AND 
       fkc.referenced_column_id = cref.column_id
WHERE 
    SCHEMA_NAME(fk.schema_id) = @Schema

PRINT @SQL

This code doesn't work (it only returns the last line of the recordset):

DECLARE @SQL VARCHAR(MAX) = ''
DECLARE @Schema VARCHAR(100) = 'SchemaName'
SELECT 
    @SQL = @SQL
         + 'ALTER TABLE ' + tpa.name + CHAR(13) + CHAR(10)
         + CHAR(9) + 'DROP CONSTRAINT ' + fk.name +';' + CHAR(13) + CHAR(10)
FROM 
    sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc 
    ON fkc.constraint_object_id = fk.object_id
JOIN sys.tables AS tpa
    ON fk.parent_object_id = tpa.object_id
JOIN sys.columns AS cpa 
    ON tpa.object_id = cpa.object_id AND 
       fkc.parent_column_id = cpa.column_id
JOIN sys.tables AS tref
    ON fkc.parent_object_id = tref.object_id
JOIN sys.columns AS cref 
    ON tref.object_id = cref.object_id AND 
       fkc.referenced_column_id = cref.column_id
WHERE 
    SCHEMA_NAME(fk.schema_id) = @Schema
ORDER BY 
     OBJECT_NAME(fk.parent_object_id)
    ,OBJECT_NAME(fk.referenced_object_id)
    ,fk.name
PRINT @SQL

The offending line seems to be the OBJECT_NAME(fk.referenced_object_id) in the ORDER BY as commenting that out resolves the issue, but I don't understand why it's failing. My suspicion is that something is doing a group by inherently or something.

Even including all columns from the ORDER BY doesn't seem to help:

DECLARE @SQL VARCHAR(MAX) = ''
DECLARE @Schema VARCHAR(100) = 'UserMgmt'
SELECT 
    @SQL = @SQL
         + 'ALTER TABLE ' + tpa.name + CHAR(13) + CHAR(10)
         + CHAR(9) + 'DROP CONSTRAINT ' + fk.name +';' + CHAR(13) + CHAR(10)
         + '--' + OBJECT_NAME(fk.referenced_object_id) + ', ' + OBJECT_NAME(fk.parent_object_id) + CHAR(13) + CHAR(10)
FROM 
    sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc 
    ON fkc.constraint_object_id = fk.object_id
JOIN sys.tables AS tpa
    ON fk.parent_object_id = tpa.object_id
JOIN sys.columns AS cpa 
    ON tpa.object_id = cpa.object_id AND 
       fkc.parent_column_id = cpa.column_id
JOIN sys.tables AS tref
    ON fkc.parent_object_id = tref.object_id
JOIN sys.columns AS cref 
    ON tref.object_id = cref.object_id AND 
       fkc.referenced_column_id = cref.column_id
WHERE 
    SCHEMA_NAME(fk.schema_id) = @Schema
ORDER BY 
     OBJECT_NAME(fk.parent_object_id)
    ,OBJECT_NAME(fk.referenced_object_id)
    ,fk.name
PRINT @SQL

However, changing the JOIN statement for the referenced table does make this work:

DECLARE @SQL VARCHAR(MAX) = ''
DECLARE @Schema VARCHAR(100) = 'UserMgmt'
SELECT 
    @SQL = @SQL
         + 'ALTER TABLE ' + tpa.name + CHAR(13) + CHAR(10)
         + CHAR(9) + 'DROP CONSTRAINT ' + fk.name +';' + CHAR(13) + CHAR(10)
         + '--' + OBJECT_NAME(fk.referenced_object_id) + ', ' + OBJECT_NAME(fk.parent_object_id) + CHAR(13) + CHAR(10)
FROM 
    sys.foreign_keys AS fk
JOIN sys.foreign_key_columns AS fkc 
    ON fkc.constraint_object_id = fk.object_id
JOIN sys.tables AS tpa
    ON fk.parent_object_id = tpa.object_id
JOIN sys.columns AS cpa 
    ON tpa.object_id = cpa.object_id AND 
       fkc.parent_column_id = cpa.column_id
JOIN sys.tables AS tref
    ON fk.referenced_object_id = tref.object_id
JOIN sys.columns AS cref 
    ON tref.object_id = cref.object_id AND 
       fkc.referenced_column_id = cref.column_id
WHERE 
    SCHEMA_NAME(fk.schema_id) = @Schema
ORDER BY 
     OBJECT_NAME(fk.parent_object_id)
    ,OBJECT_NAME(fk.referenced_object_id)
    ,fk.name
PRINT @SQL

Can anyone explain this behavior?

possible duplicate of [Why SQL Server Ignores vaules in string concatenation when ORDER BY clause specified](http://stackoverflow.com/questions/5538187/why-sql-server-ignores-vaules-in-string-concatenation-when-order-by-clause-speci) — Mikael Eriksson, Oct 08 '13 at 15:02
The order by here still causes it to fail, even when the columns are specified in the select statement. However, changing the JOIN clause does seem to allow for the ORDER BY to work. See my edit. — Jon, Oct 08 '13 at 15:21

score 1 · Accepted Answer · edited May 23 '17 at 12:28

The reason is as Martin Smith pointed out: there is no defined behavior with aggregated string concatenation.

The solution is to use a cursor instead of pretending you're not. You should also (a) not use VARCHAR for metadata - it's all stored as Unicode so you should use SYSNAME/NVARCHAR (b) use QUOTENAME around entity names in case they are reserved words or otherwise invalid identifiers (c) obtain the ID for the schema once instead of inline.

DECLARE @schemaID INT = SCHEMA_ID(N'SchemaName');

DECLARE @sql NVARCHAR(MAX) = N'', @tpaname SYSNAME, @fkname SYSNAME;

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
SELECT tpa.name, fk.name FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.tables AS tpa ON fk.parent_object_id = tpa.object_id
INNER JOIN sys.columns AS cpa ON tpa.object_id = cpa.object_id AND 
       fkc.parent_column_id = cpa.column_id
INNER JOIN sys.tables AS tref ON fkc.parent_object_id = tref.object_id
INNER JOIN sys.columns AS cref ON tref.object_id = cref.object_id AND 
       fkc.referenced_column_id = cref.column_id
WHERE fk.schema_id = @SchemaID
ORDER BY 
     OBJECT_NAME(fk.parent_object_id)
    ,OBJECT_NAME(fk.referenced_object_id)
    ,fk.name;

OPEN c;

FETCH c INTO @tpaname, @fkname;

WHILE @@FETCH_STATUS <> -1;
BEGIN
  SET @sql += N'ALTER TABLE ' + QUOTENAME(@tpaname) + CHAR(13) + CHAR(10)
         + CHAR(9) + 'DROP CONSTRAINT ' + QUOTENAME(@fkname) +';' + CHAR(13) + CHAR(10)
  FETCH c INTO @tpaname, @fkname;
END

CLOSE c; DEALLOCATE c;

PRINT @sql;

ORDER BY changes String Concatenation SQL

1 Answers1