1. Avoid deploying large database changes without first understanding their impact. Out-of-sequence deployments and emergency fixes can create code drift between development, test, staging, and production environments. section 7.4
Use SSDT Schema Compare in Visual Studio to compare a project or database with a target database. The comparison identifies changed objects and displays source and target definitions side by side. section 7.4
A practical comparison workflow is:
- Save the database-object changes without publishing them. section 7.4
- Open Schema Compare from the Marketing project in SQL Server Object Explorer. section 7.4
- Select the target database, run the comparison, and inspect changed objects and highlighted additions or removals. section 7.4
- Use the results to understand affected functionality and, when appropriate, bring the databases into synchronization. section 7.4
Marketing procedure change
Before publishing an updated CalculateCampaignSummary procedure, compare the Marketing project with its database. The result exposes the missing @BudgetDifference alias and the added parameter controlling the second result set. section 7.4
Listing 7.17 Enhancing the CalculateCampaignSummary definition
CREATE PROCEDURE dbo.CalculateCampaignSummary
@CampaignID INT,
@Budget MONEY,
@Detailed BIT
AS
BEGIN
DECLARE @AvgCPM MONEY ;
DECLARE @CampaignCost MONEY ;
DECLARE @NoOfImp INT ;
DECLARE @AvgBidPrice MONEY ;
DECLARE @CostOfBidPerc DECIMAL ;
DECLARE @BudgetDifference MONEY ;
SELECT
@NoOfImp = COUNT(*)
, @CampaignCost = (SUM(CostPerMille) / 1000) * COUNT(*)
FROM marketing.Impressions
WHERE CampaignID = @CampaignID
GROUP BY CampaignID ;
SELECT
@avgcpm = AVG(AvgCostPerMille)
, @avgbidprice = AVG(AvgBidPrice)
FROM reporting.ImpressionAggregates
WHERE CampaignID = @CampaignID ;
SET @CostOfBidPerc = (@AvgBidPrice / @AvgCPM) * 100 ;
SET @BudgetDifference = @budget - @CampaignCost ;
SELECT
@CampaignID CampaignID
, @AvgCPM AvgCPM
, @CampaignCost CampaignCost
, @NoOfImp ImpQty
, @CostOfBidPerc CostToBidPercentage
, @BudgetDifference BudgetDifference$ ;
IF @Detailed = 1
BEGIN
SELECT DISTINCT
ReferralURL
, RenderingID
FROM marketing.Impressions
WHERE CampaignID = @CampaignID ;
END
END
Inspect the stored procedure definition used as the pre-publication comparison example. section 7.4
