Single Profile Script vs Multiple Profile Scripts?

Author Message
 Posted 11/12/2013 10:32:16 AM
StrataFrame Novice

StrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame Novice

Group: StrataFrame Users
Last Login: 12/8/2014 4:29:38 AM
Posts: 80, Visits: 239
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
Post #32276
Add to Twitter Add to Facebook
 Posted 11/12/2013 11:43:39 AM
StrataFrame VIP

StrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIP

Group: StrataFrame Users
Last Login: 4/9/2021 12:42:36 PM
Posts: 2,436, Visits: 24,184
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. BigGrin


Edhy Rijo
(Using VB.Net 2019/SQL Server 2017)
Post #32277
Add to Twitter Add to Facebook
 Posted 11/13/2013 5:38:07 AM
StrataFrame Novice

StrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame Novice

Group: StrataFrame Users
Last Login: 12/8/2014 4:29:38 AM
Posts: 80, Visits: 239
Thanks Edhy.

Off bat, it seems to me that a way to enable/disable individual scripts would be handy for development/testing.
Post #32283
Add to Twitter Add to Facebook
 Posted 11/13/2013 7:02:50 AM
StrataFrame Novice

StrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame Novice

Group: StrataFrame Users
Last Login: 12/8/2014 4:29:38 AM
Posts: 80, Visits: 239
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



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)



Edited: 11/13/2013 7:07:28 AM by William Fields
Post #32287
Add to Twitter Add to Facebook
 Posted 11/13/2013 12:00:22 PM
StrataFrame VIP

StrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIPStrataFrame VIP

Group: StrataFrame Users
Last Login: 4/9/2021 12:42:36 PM
Posts: 2,436, Visits: 24,184
Hi William,

William Fields (11/13/2013)
The intent is to drop/create each CONSTRAINT whether it exists or not (i.e. I want to be lazy).

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...


/****** 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]
;



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:

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


This is my version of same script I use in the DDT:

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]
;


Edhy Rijo
(Using VB.Net 2019/SQL Server 2017)
Post #32294
Add to Twitter Add to Facebook
 Posted 11/14/2013 3:06:08 AM
StrataFrame Novice

StrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame Novice

Group: StrataFrame Users
Last Login: 12/8/2014 4:29:38 AM
Posts: 80, Visits: 239
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:



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...



Then, I created a generic SP thusly:



-- 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



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
Post #32296
Add to Twitter Add to Facebook
 Posted 11/14/2013 3:23:07 AM
StrataFrame Developer

StrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame Developer

Group: StrataFrame Developers
Last Login: 11/20/2019 8:57:25 AM
Posts: 6,610, Visits: 6,776
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.
Post #32297
Add to Twitter Add to Facebook
 Posted 11/14/2013 6:31:05 AM
StrataFrame Novice

StrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame NoviceStrataFrame Novice

Group: StrataFrame Users
Last Login: 12/8/2014 4:29:38 AM
Posts: 80, Visits: 239
OK, that's easy enough (and thanks for the info).

Here's the result:



-- 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

Post #32298
Add to Twitter Add to Facebook
 Posted 11/14/2013 7:30:44 AM
StrataFrame Developer

StrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame DeveloperStrataFrame Developer

Group: StrataFrame Developers
Last Login: 11/20/2019 8:57:25 AM
Posts: 6,610, Visits: 6,776
Looks good!  That will serve you much better in the long run.
Post #32302
Add to Twitter Add to Facebook


Similar Topics

Expand / Collapse

Reading This Topic

Expand / Collapse

Site Map - Home - My Account - Forum - About Us - Contact Us - Try It - Buy It

Microsoft, Visual Studio, and the Visual Studio logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.