Red Gate forums :: View topic - Non-CL Index should DROP NCL First
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Non-CL Index should DROP NCL First

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
annabelledodd



Joined: 11 Jul 2014
Posts: 1

PostPosted: Fri Jul 11, 2014 2:36 am    Post subject: Non-CL Index should DROP NCL First Reply with quote

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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@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/
_________________
belledodd anna
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6647

PostPosted: Mon Jul 14, 2014 2:13 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group