Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 = ''
    DECLARE @IndexInfo TABLE (TableName nvarchar(255), IndexName nvarchar(255), IndexColumnName nvarchar(255));
    INSERT INTO @IndexInfo (TableName, IndexName, IndexColumnName)
    SELECT AS TableName, AS IndexName, 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);
    SELECT TableName, IndexName, 1 AS ExecOrder, ''''DROP INDEX '''' + IndexName + '''' ON '''' + TableName + '''';'''' FROM @IndexInfo I
    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;
        PRINT @cmd;
        FETCH NEXT FROM cur INTO @t1, @t2, @t3, @cmd;
    CLOSE cur;
    EXEC ' + @Server + '.' + @Database + '.sys.sp_executesql @cmd;
PRINT @cmd;
share|improve this question
Oh, I forgot to mention this dynamic SQL is setup to run on a remote server. I can post a version without that added complexity if necessary. – Max Vernon Sep 21 '12 at 22:28
up vote 11 down vote accepted

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.

share|improve this answer
wow - in-depth answer; thanks! I normally use RAISERROR but am getting lazy and PRINT is less typing. – Max Vernon Sep 21 '12 at 23:36
@MaxVernon In case I didn't make it clear, the DDL statements would execute in the order you expect, it's just the buffered PRINT output that is reversed. – Paul White Sep 21 '12 at 23:51

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:


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).

share|improve this answer
interesting - I would have expected ordinals to be more specific, not less! thanks for your answer! – Max Vernon Sep 21 '12 at 23:38
yeah I guess I could just set @cmd equal to the output from a single query instead of looping. My darn procedural habits again... – Max Vernon Sep 21 '12 at 23:43
The @cmd variable is declared three times in different scopes (!). The innermost @cmd is the row-by-row command executed in the cursor on the remote server. The SET @cmd = '' that led you astray is in fact the start of a string assignment with doubled-up single quotes. Ouch. – Paul White Sep 21 '12 at 23:48
@SQLKiwi ouch is right. Hey Max, why not tell us exactly what the code is trying to do? I think it might be better to start from scratch than try to troubleshoot what you have. – Aaron Bertrand Sep 21 '12 at 23:50
There are multiple problems with the code, including the fact that DROP INDEX statements are duplicated for a multi-column index definition, but perhaps 'how to write a query to...' should be a separate question? – Paul White Sep 21 '12 at 23:54

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.