StrataFrame Forum » StrataFrame Database Deployment Toolkit » Database Deployment Toolkit (How do I?) » Single Profile Script vs Multiple Profile Scripts?

Single Profile Script vs Multiple Profile Scripts?

William Fields — Thu, 14 Nov 2013 13:30:44 GMT

Hello,

I know, probably a simple question with an obvious answer... "it depends..."

But, I have a large number of check constraints that I need to add to a DDT profile as deployment scripts and I was wondering what the benefit would be to create separate scripts for each table, or even each constraint, vs a single script for the entire database.

I can see there is a priority level that can be assigned, which would give me flexibility in the order the scripts are applied, but can anyone else chime in on other (not obvious) benefits of the separate vs combined approach?

Thanks.

Bill

RE: Single Profile Script vs Multiple Profile Scripts?

Trent L. Taylor — Thu, 14 Nov 2013 13:30:44 GMT

Looks good! That will serve you much better in the long run.

RE: Single Profile Script vs Multiple Profile Scripts?

William Fields — Thu, 14 Nov 2013 12:31:05 GMT

OK, that's easy enough (and thanks for the info).

Here's the result:

[codesnippet]

-- Add the parameters for the stored procedure here

@p_cTableName VARCHAR(100) = '',

@p_cConstraintName VARCHAR(4000) = '',

@p_cConstraintExp VARCHAR(4000) = ''

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(@p_cConstraintName) AND parent_object_id = OBJECT_ID(@p_cTableName))

BEGIN

EXEC ('ALTER TABLE ' + @p_cTableName + ' DROP CONSTRAINT ' + @p_cConstraintName)

END

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(@p_cConstraintName) AND parent_object_id = OBJECT_ID(@p_cTableName))

BEGIN

EXEC ('ALTER TABLE ' + @p_cTableName + ' WITH NOCHECK ADD CONSTRAINT ' + @p_cConstraintName + ' CHECK (' + @p_cConstraintExp + ')')

END

IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(@p_cConstraintName) AND parent_object_id = OBJECT_ID(@p_cTableName))

BEGIN

EXEC ('ALTER TABLE ' + @p_cTableName + ' CHECK CONSTRAINT ' + @p_cConstraintName)

END

END

GO

[/codesnippet]

RE: Single Profile Script vs Multiple Profile Scripts?

Trent L. Taylor — Thu, 14 Nov 2013 09:23:07 GMT

Actually TRY/CATCH is always slower than testing logical code. A TRY/CATCH is going to go through more layers than a quick query will when server side. This is also the same when coding on the .NET side as well, TRY/CATCH is something that you almost can't live without these days, but you always want to test for failure points when possible instead of relying on TRY/CATCH.

So I would recommend using the EXISTS logic instead of the TRY/CATCH. It is cleaner code and is less prone for long term performance issues as your database grows.

RE: Single Profile Script vs Multiple Profile Scripts?

William Fields — Thu, 14 Nov 2013 09:06:08 GMT

Thanks for the tip on "Check for object existence" I didn't know about that before. I settled on a single script for all CHECK constraints for TABLE/COLUMN rules with a generic stored proc that drops/creates each.

Here's the format of the main script:

[codesnippet]

DECLARE @l_cTableName NVARCHAR(100)

DECLARE @l_cConstName NVARCHAR(4000)

DECLARE @l_cConstExpr NVARCHAR(4000)

SET @l_cTableName = '[dbo].[csAtty]'

    SET @l_cConstName = '[Hearing ID cannot be empty.]'

    SET @l_cConstExpr = 'isnull([at_nhearing_id], 0)<>0'

    EXEC [dbo].[CreateConstraint] @l_cTableName, @l_cConstName, @l_cConstExpr

    SET @l_cConstName = '[Matter ID cannot be empty.]'

    SET @l_cConstExpr = 'isnull([at_nmatter_id], 0)<>0'

    EXEC [dbo].[CreateConstraint] @l_cTableName, @l_cConstName, @l_cConstExpr

       Etc....

SET @l_cTableName = '[dbo].[csBlockTemplate]'

    SET @l_cConstName = '[Description cannot be empty.]'

    SET @l_cConstExpr = 'isnull(BT_MEVENT, '')<>'''''

    EXEC [dbo].[CreateConstraint] @l_cTableName, @l_cConstName, @l_cConstExpr

    Etc...

[/codesnippet]

Then, I created a generic SP thusly:

[codesnippet]

-- Add the parameters for the stored procedure here

@p_cTableName VARCHAR(100) = '',

@p_cConstraintName VARCHAR(4000) = '',

@p_cConstraintExp VARCHAR(4000) = ''

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

BEGIN TRY

    EXEC ('ALTER TABLE ' + @p_cTableName + ' DROP CONSTRAINT ' + @p_cConstraintName)

END TRY

BEGIN CATCH

END CATCH

EXEC ('ALTER TABLE ' + @p_cTableName + ' WITH NOCHECK ADD CONSTRAINT ' + @p_cConstraintName + ' CHECK (' + @p_cConstraintExp + ')')

EXEC ('ALTER TABLE ' + @p_cTableName + ' CHECK CONSTRAINT ' + @p_cConstraintName)

END

GO

[/codesnippet]

This cuts down on repetitive code in my post deploy script and makes for better readability. I don't have any evidence to back this, but my gut tells me that using the TRY/CATCH rather than checking for existence may perform better...?

Thanks again.

Bill

RE: Single Profile Script vs Multiple Profile Scripts?

Edhy Rijo — Wed, 13 Nov 2013 18:00:22 GMT

Hi William,

[quote][b]William Fields (11/13/2013)[/b][hr]The intent is to drop/create each CONSTRAINT whether it exists or not (i.e. I want to be lazy).[/quote]
I do the same, but I use a lighted modified version of the script generated by the MS SQL Server Management Studio, see sample below...
[codesnippet]

/****** Drop TransactionItems2TransactionItemsStock constraint ******/
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[TransactionItems2TransactionItemsStock]') AND parent_object_id = OBJECT_ID(N'[dbo].[TransactionItemsStock]'))
ALTER TABLE [dbo].[TransactionItemsStock] DROP CONSTRAINT [TransactionItems2TransactionItemsStock]
;

/****** Object: Index [spItemsLookup_View.idx] Script Date: 10/07/2012 16:38:16 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Items]') AND name = N'spItemsLookup_View.idx')
DROP INDEX [spItemsLookup_View.idx] ON [dbo].[Items] WITH ( ONLINE = OFF )
;
CREATE NONCLUSTERED INDEX [spItemsLookup_View.idx] ON [dbo].[Items]
(
[ItemIsInactive] ASC
)
INCLUDE ( [PK_Items],
[CardLotNo],
[ItemCode],
[ItemName],
[ItemPrice],
[ActivationCost],
[DefaultDiscountPrice],
[DefaultDiscountPercentage],
[QB_RefID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
;

[/codesnippet]

To generate this code simply right click your table object (constraint, index, etc.) and choose "Drop And Create To" from the "Script Constraint as" menu. Also if you don't get the code for checking if the object exist, from the Option's menu, go down to "SQL Server Object Explorer->Scripting" and set the "Check for object existence = True.
This is the generated code from Management Studio:
[codesnippet]
USE [CardTrackingSystem_Data]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF_Transaction_ActivationCSVBatchNo]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Transaction] DROP CONSTRAINT [DF_Transaction_ActivationCSVBatchNo]
END

GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF_Transaction_ActivationCSVBatchNo]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Transaction] ADD CONSTRAINT [DF_Transaction_ActivationCSVBatchNo] DEFAULT ((0)) FOR [ActivationCSVBatchNo]
END

GO
[/codesnippet]

This is my version of same script I use in the DDT:
[codesnippet]
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF_Transaction_ActivationCSVBatchNo]') AND type = 'D')
ALTER TABLE [dbo].[Transaction] DROP CONSTRAINT [DF_Transaction_ActivationCSVBatchNo]
;

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF_Transaction_ActivationCSVBatchNo]') AND type = 'D')
ALTER TABLE [dbo].[Transaction] ADD CONSTRAINT [DF_Transaction_ActivationCSVBatchNo] DEFAULT ((0)) FOR [ActivationCSVBatchNo]
;
[/codesnippet]

RE: Single Profile Script vs Multiple Profile Scripts?

William Fields — Wed, 13 Nov 2013 13:02:50 GMT

OK, so I'm leaning towards a script per table... and since I'm still a T-SQL newbie, I would appreciate comments and suggestions on my code structure.

The intent is to drop/create each CONSTRAINT whether it exists or not (i.e. I want to be lazy). Each table could have dozens of TABLE/COLUMN constraints and my initial thought is something like the following.

Let me have it!

Thanks.

Bill

[codesnippet]

DECLARE @TABLENAME NVARCHAR(100)
DECLARE @CONSTNAME NVARCHAR(4000)
DECLARE @CONSTEXPR NVARCHAR(4000)

SET @TABLENAME = '[dbo].[csEvents]'

SET @CONSTNAME = '[Event description cannot be empty]'
SET @CONSTEXPR = '[ev_mevent]<>'''''

BEGIN TRY
EXEC ('ALTER TABLE ' + @TABLENAME + ' DROP CONSTRAINT ' + @CONSTNAME)
END TRY
BEGIN CATCH
END CATCH

EXEC ('ALTER TABLE ' + @TABLENAME + ' WITH NOCHECK ADD CONSTRAINT ' + @CONSTNAME + ' CHECK (' + @CONSTEXPR + ')')
EXEC ('ALTER TABLE ' + @TABLENAME + ' CHECK CONSTRAINT ' + @CONSTNAME)

SET @CONSTNAME = '[Start datetime cannot be later than end datetime]'
SET @CONSTEXPR = '[ev_tdtstart]<=[ev_tdtend]'

BEGIN TRY
EXEC ('ALTER TABLE ' + @TABLENAME + ' DROP CONSTRAINT ' + @CONSTNAME)
END TRY
BEGIN CATCH
END CATCH

EXEC ('ALTER TABLE ' + @TABLENAME + ' WITH NOCHECK ADD CONSTRAINT ' + @CONSTNAME + ' CHECK (' + @CONSTEXPR + ')')
EXEC ('ALTER TABLE ' + @TABLENAME + ' CHECK CONSTRAINT ' + @CONSTNAME)

[/codesnippet]

RE: Single Profile Script vs Multiple Profile Scripts?

William Fields — Wed, 13 Nov 2013 11:38:07 GMT

Thanks Edhy.

Off bat, it seems to me that a way to enable/disable individual scripts would be handy for development/testing.

RE: Single Profile Script vs Multiple Profile Scripts?

Edhy Rijo — Tue, 12 Nov 2013 17:43:39 GMT

Hi William,

For me it would make no difference since DDT will treat it the same. With a single script you control the order of your process, and with an individual script per table, you could end up with many post deployment scripts.

In my biggest project, I don't have too many of these post scripts so I have a single file for the same process like creating indexes and other scripts for some maintenance that are run at a lower priority after everything else has been completed.

I wish the new DDT in SF 2.0 will allow the use of INCLUDE on index creations, I hate having to manage a table objects on several places. :D