Unneccesary steps in the deploy script when dropping a table
Andrej
Posts: 3 New member
in SQL Compare
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
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
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.
Software Developer on SQL Compare and SQL Data Compare
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.
Redgate Software
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.
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