Why does this cursor produce results in the incorrect order?

Question

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 ('''' + IndexColumnName + '''')'''' + '''';'''' FROM @IndexInfo I
    ORDER BY 1,2,3 DESC;
    OPEN cur;
    FETCH NEXT FROM cur INTO @t1, @t2, @t3, @cmd;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @cmd;
        FETCH NEXT FROM cur INTO @t1, @t2, @t3, @cmd;
    END
    CLOSE cur;
    DEALLOCATE cur;
    '';
    EXEC ' + @Server + '.' + @Database + '.sys.sp_executesql @cmd;
';
PRINT @cmd;

Answer 1

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.

Answer 2

Given the new information laid out in the comments, there are at least three solutions to this that don't require this mess of dynamic SQL and manual, after the fact reaction:

use a ruler

Seriously, he's a DBA with job responsibilities at your company. Surely you can implement a policy that tables have clustered indexes except in certain scenarios (and in such case, they must be signed off on). When stated as a policy this becomes a potential quantifiable measure for job performance.

implement a DDL trigger

It is very easy to capture CREATE_TABLE events in DDL triggers, and then check the sys.indexes or sys.partitions catalog views for the presence of a clustered index. No clustered index? Rollback. This will eliminate the possibility of SELECT INTO, since you can't define a clustered index in advance, but maybe that's ok.

make him develop in Azure

You can't create a table in the cloud without a clustered index. If you make him develop there, no rulers or DDL triggers required.

---

Why are you using ordinals? Instead of:

ORDER BY 1,2,3 DESC;

Try referencing the aliases directly:

ORDER BY TableName, IndexName, ExecOrder;

Also you use a cursor to build up @cmd and I'd expect somewhere for you to be appending it. As it stands it appears that you are looping through the cursor but only ever executing the @cmd from the very last iteration in the loop. So what is the purpose of the cursor? And why aren't you using better cursor options if the cursor is, indeed, necessary? (If it isn't, you can change it to a single TOP query).