Why does this cursor produce results in the incorrect order?

I am writing some dynamic SQL to identify and, perhaps if I'm feeling crazy enough, automatically convert my NONCLUSTERED indexes into CLUSTERED indexes.

The line ORDER BY 1,2,3 DESC; in the SQL below is designed to output DROP INDEX... statements before ALTER TABLE... statements in order to DROP the NONCLUSTERED index first and then add a CLUSTERED index. I had to add the DESC after column 3 to get the DROP first followed by the ALTER. This is backwards, unless I'm losing it!

```
DECLARE @Server nvarchar(max);
DECLARE @Database nvarchar(max);
DECLARE @cmd nvarchar(max);
DECLARE @IndexType int;

SET @IndexType = 2; / 1 is CLUSTERED, 2 is NONCLUSTERED /
SET @Server = 'MyServer';
SET @Database = 'MyDatabase';

SET @cmd = '
DECLARE @cmd nvarchar(max);
SET @cmd = ''
SET NOCOUNT ON;
DECLARE @IndexInfo TABLE (TableName nvarchar(255), IndexName nvarchar(255), IndexColumnName nvarchar(255));
INSERT INTO @IndexInfo (TableName, IndexName, IndexColumnName)
SELECT t.name AS TableName, i.name AS IndexName, c.name AS IndexColumnName /, t.create_date, ic., c. /
FROM sys.tables t
LEFT JOIN sys.indexes i ON t.object_id = i.object_id
LEFT JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
LEFT JOIN sys.columns c ON i.object_id = c.object_id and ic.column_id = c.column_id
WHERE i.is_primary_key = 1
AND i.type = ' + CAST(@IndexType as nvarchar(max)) + '
ORDER BY t.create_date desc;
DECLARE @t1 nvarchar(max);
DECLARE @t2 nvarchar(max);
DECLARE @t3 nvarchar(max);
DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR FOR
SELECT TableName, IndexName, 1 AS ExecOrder, ''''DROP INDEX '''' + IndexName + '''' ON '''' + TableName + '''';'''' FROM @IndexInfo I
UNION ALL
SELECT TableName, IndexName, 2 AS ExecOrder, ''''ALTER TABLE '''' + TableName + '''' ADD CONSTRAINT PK_'''' + TableName + ''''_'''' + IndexColumnName + '''' PRIMARY KEY CLUSTERED

The PRINT statement inside the WHILE loop does execute in the order you expect, but the output is buffered before sys.sp_executesql returns. Implementation details mean the buffered output is reversed.

Using RAISERROR (@cmd, 0, 1) WITH NOWAIT; instead of PRINT forces the buffer to flush after each call, giving you the results in the order you expect. IIRC the NOWAIT trick only works per-row for the first 500 rows. In any case, all this is undocumented stuff that could change at any time, so please don't rely on it - I only mention it to explain what you see.

The reversal does not occur if you replace the sp_executesql call with EXEC (@cmd) AT ' + @Server + ' though that does require a USE database command prefixed to @cmd and also the linked server needs to be enabled for RPC. This is not a recommendation either, just showing the output reversal is a quirk of sp_executesql.

StackExchange Database Administrators Q#24714, answer score: 11