What are the challenges you face when working across database platforms? Take the survey
Options

Unneccesary steps in the deploy script when dropping a table

Hello everybody,

When dropping a table with a foreign key constraint, SQL Compare generates a deploy script which first drops the constraint, then the index (on the column where FK was defined) and at last the table itself. The first two steps are to my opinion a total waste of resources. Or maybe I'm missing something?

The latest and greatest SQLCompare 13.0.1.5078, SQL Server 2014 SP2.

Below is the create script for the table and the deploy script which SQL Compare generates.

--================CREATE TABLE ================
CREATE TABLE positions.trades

(

trades_set_id INT NOT NULL

,dueday INT NOT NULL

,position_id VARCHAR(255) NOT NULL

) ON PS_dueday (dueday)

WITH (DATA_COMPRESSION = PAGE);

GO

CREATE CLUSTERED INDEX IDX__positions_trades_trades_set_id

ON positions.trades(trades_set_id ASC)

WITH(DATA_COMPRESSION = PAGE)

ON PS_dueday(dueday);

GO

ALTER TABLE positions.trades WITH CHECK ADD

CONSTRAINT FK__positions_trades_trades_set_id FOREIGN KEY(trades_set_id)REFERENCES positions.trades_set(id);

GO

ALTER TABLE positions.trades CHECK CONSTRAINT FK__positions_trades_trades_set_id;

GO



--================GENERATED DEPLOY SCRIPT-================

SET NUMERIC_ROUNDABORT OFF
GO

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

PRINT N'Dropping foreign keys from [positions].[trades]'
GO

ALTER TABLE [positions].[trades] DROP CONSTRAINT [FK__positions_trades_trades_set_id]

GO

PRINT N'Dropping index [IDX__positions_trades_trades_set_id] from [positions].[trades]'

GO

DROP INDEX [IDX__positions_trades_trades_set_id] ON [positions].[trades]

GO

PRINT N'Dropping [positions].[trades]'

GO

DROP TABLE [positions].[trades]

GO
Tagged:

Answers

  • Options
    Hi Andrej,

    We typically drop foreign key constraints towards the start of a deployment script, to enable the dropping of the keys they reference. In this case however, I think you are correct that the whole table could have been dropped in one operation.
    Matthew Chandler
    Software Developer on SQL Compare and SQL Data Compare
  • Options
    The extra drops are only needed if you drop multiple tables that reference each other - I agree they're not doing much with only a single table involved.

    While this makes the script unnecessarily verbose, my understanding is that it won't take any longer to run the script on SQL Server. Please correct me if I'm wrong - if it's making your deployment take a long time, we should investigate the performance impact.
    Software Developer
    Redgate Software
  • Options
    AndrejAndrej Posts: 3 New member
    Thanks for quick answers, guys.

    In my situation it's indeed the verbosity what hurts most. When preparing the deployment, I usually take the generated script as a first “prototype” and adjust it manually because SQL Compare obviously can’t know everything (for example, how I want to do some specific data migration steps). The more unnecessary stuff I have in this “prototype”, more time I need to spend reading the script.

    I can also imagine a situation when this SQL Compare behavior can present a real performance problem. This requires SQL Server 2016/2017 which I unfortunately don’t have at the customer site, so just a mental exercise on my part:
    As you see, SQL Compare also drops the clustered index which contained the column with the FK constraint. Now imagine my table had a clustered columnstore index (and a FK. This works up from SQL2016). If SQL Compare would also drop the clustered columnstore index like in the example above, that could be quite a problem. Not only this operation is very time consuming (unpacking all the columnstore data and persisting it in a heap), but it also requires humongous amounts of data, because clustered columnstore compression is usually quite good (between 10x to 20x) and would fail if not enough space is available. Quite anecdotal if you try to free some space by dropping tables which are not needed anymore :)

    I would really appreciate if you could confirm or correct me at this point.
  • Options
    AndrejAndrej Posts: 3 New member
    Got my hands on a SQL Server 2016 machine, and SQL Compare 13 would also first drop the clustered columnstore index before dropping the table (see my scenario above). This could be a HUGE problem for a table with a lot of data (and table with a clustered columnstore index usually have a lot of data).
    So be extremelly careful, study the generated script and test your deployment script on a database and a server similar to your production (yeah, Captain Obvious ;) ). It would be even better if Red Gate would correct/optimize this.
  • Options
    teodimacheteodimache Posts: 1 New member
    I've also noticed on Sql Server 2016 with SQL Compare 13 that if you drop a column from a table that has a clustered columnstore index, the script generated would first drop the index, drop the columns then re-create the index, which is a HUGE problem like Andrej mentioned earlier.
Sign In or Register to comment.