Non-CL Index should DROP NCL First

annabelledodd

I could be wrong, but I thought it was more efficient when changing the PK and dropping a non-clustered Index on the same table to drop the non-clustered index first, then drop the PK.

In my case, I swapped the PK sequence of the 3rd and 4th columns along with dropping an obsolete index.

SC drops the PK then the non-clustered index, so perhaps it's doing unnecessary IO. With the table in question comprising over 110MM rows, this is a potential excessive-wait situation in the making...

(Sadly, I inherited the poorly chosen table and column names; they're pervasive, so I'm stuck with them for now, so please no observations on them...!)

Like I said, I could be wrong...

Here's the generated script:
Code:
/*
Run this script on:

xcQA.xcp_rpt - This database will be modified

to synchronize it with:

xcdev2012.xcp_rpt

You are recommended to back up your database before running this script

Script created by SQL Compare version 10.4.8 from Red Gate Software Ltd at 4/3/2014 3:20:41 PM

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
USE [xcp_rpt]
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping constraints from [dbo].[t_meter_agg]'
GO
ALTER TABLE [dbo].[t_meter_agg] DROP CONSTRAINT [PK_t_meter_agg]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping index [ix_ma_EndTime] from [dbo].[t_meter_agg]'
GO
DROP INDEX [ix_ma_EndTime] ON [dbo].[t_meter_agg]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_t_meter_agg] on [dbo].[t_meter_agg]'
GO
ALTER TABLE [dbo].[t_meter_agg] ADD CONSTRAINT [PK_t_meter_agg] PRIMARY KEY CLUSTERED ([agg_site_id], [agg_tag_id], [agg_interval_code], [agg_start_time], [agg_input_id]) WITH (FILLFACTOR=100, DATA_COMPRESSION = PAGE) ON [PRIMARY]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
http://www.coininfifa.com/

Brian Donahue

Thanks for reporting this. It's something that has already been noticed and logged as enhancement request SC-7066. At this time, though, the team are working on other things and are going to look into this at a later date.