|
William Fields
|
|
|
Group: StrataFrame Users
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
|
|
|
|
|
Edhy Rijo
|
|
|
Group: StrataFrame Users
Posts: 2.4K,
Visits: 23K
|
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. 
Edhy Rijo
|
|
|
|
|
William Fields
|
|
|
Group: StrataFrame Users
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.
|
|
|
|
|
William Fields
|
|
|
Group: StrataFrame Users
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)
|
|
|
|
|
Edhy Rijo
|
|
|
Group: StrataFrame Users
Posts: 2.4K,
Visits: 23K
|
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
|
|
|
|
|
William Fields
|
|
|
Group: StrataFrame Users
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
|
|
|
|
|
Trent Taylor
|
|
|
Group: StrataFrame Developers
Posts: 6.6K,
Visits: 6.9K
|
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.
|
|
|
|
|
William Fields
|
|
|
Group: StrataFrame Users
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
|
|
|
|
|
Trent Taylor
|
|
|
Group: StrataFrame Developers
Posts: 6.6K,
Visits: 6.9K
|
Looks good! That will serve you much better in the long run.
|
|
|
|