ALTER TABLE dbo.Employees
    ADD Gender bit NOT NULL
GO

Figure: Add a column (Easy)

ALTER TABLE dbo.Employees
    DROP COLUMN TitleOfCourtesy
GO 

Figure: Delete a column (Easy)

EXECUTE sp_rename N'dbo.Employees.HireDate', 
                  N'Tmp_StartDate_1', 'COLUMN'
GO
EXECUTE sp_rename N'dbo.Employees.Tmp_StartDate_1', 
                  N'StartDate', 'COLUMN'
GO 

Figure: Rename a column (Medium)

CREATE TABLE dbo.Tmp_Employees
(
    ...
    Gender char(2) NULL,
    ...
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
...
IF EXISTS(SELECT * FROM dbo.Employees)
    EXEC('INSERT INTO dbo.Tmp_Employees (..., Gender,...)
              SELECT ...,Gender ,... 
              FROM dbo.Employees WITH (HOLDLOCK TABLOCKX)
              ') 
...
GO
DROP TABLE dbo.Employees
GO
EXECUTE sp_rename N'dbo.Tmp_Employees', 
                  N'Employees', 'OBJECT'
GO 

Figure: Change data type (Hard) e.g. Bit to Integer. The above is abbreviated, see the full .SQL file

CREATE TABLE dbo.Tmp_Employees
(
    ...
    Gender int NULL,
    ...
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
...
IF EXISTS(SELECT * FROM dbo.Employees)
    EXEC('INSERT INTO dbo.Tmp_Employees (..., Gender,...)
          SELECT ...,CASE Gender WHEN ''F'' THEN ''0'' 
                                 WHEN ''M'' THEN ''1''
                                 WHEN ''NA'' THEN ''2''
                                 WHEN ''U'' THEN ''3''
                                 ELSE ''-1''
                     END AS Gender ,... 
          FROM dbo.Employees WITH 
          (HOLDLOCK TABLOCKX)
          ')
...
GO
DROP TABLE dbo.Employees
GO
EXECUTE sp_rename N'dbo.Tmp_Employees', 
                  N'Employees', 'OBJECT'
GO 

Figure: Change data type (Very Hard) e.g. Text to Integer. Text to Integer and data conversion requires "Data Motion Scripts". The above is abbreviated, see the full .SQL file

Figure: The applications database should have a table storing the version info (the table is called _zsDataVersion). See an example of this in SSW Link Auditor

Figure: The user can clearly see the Database version is 62 after clicking "Configure..." button in wizard "Storage Mechanism". See an example of this in SSW Link Auditor

Figure: The Application keeps all the scripts in a folder called SQLScripts (this allows the application to upgrade itself and give the Reconciliation functionality)

1. When you have an error you can see exactly which script introduced it
2. You don't have to use a compare tool like Red-Gate SQL Compare at the end of your development cycle
3. Your application can automatically make schema changes
4. The application can have a "Create" database button when installed for the first time
5. The application can have an "Upgrade" button and work out itself if this new version needs scripts to be run
6. The application can tell if it is an old version (as a newer version may have upgraded the schema), so you only use the latest clients
7. The application can have a "Reconcile" feature that compares the current schema to what it should be



 

Figure: A list of change SQL scripts, each file name is in the correct format


Is there a file naming convention to follow?
The script file naming convention should be XXXXX_ObjectType_ObjectName_ColumnName_Description_SchemaMasterInitials.sql

eg.  00089_Table_Employee_Gender_ChangeFromBitToChar_AC.sql

Figure: You can use SQL Compare to make two databases the same

Figure: SQL Compare clearly shows some tables are missing

So if you want to compare 2 databases SQL Compare (or Data Dudes Compare) is great tools. They even let you synchronize sweetly between these 2 databases. However, if you are doing this at the end of your release cycle, you have a problem.  Your schema deployment process is broken.

What you should be doing is seeing your Schema Master each time you have a new .sql file. You do this during the development process, not at the end in the package and deployment process.

Figure: Give your SQL scripts to 'Schema Master' who will, check them into TFS, then run them

Figure: One person should be the 'Schema Master', on an average sized project (of 5-10 devs)

If your project has a database, you need to select a "Schema Master". This is the one person who is allowed to make modifications to the database. These include:

• Creating, Modifying and Deleting tables and columns
• Relationships
• Modify Controlled Lookup Data

Figure: The Applications Database stores version info in a table called _zsVersion

Figure: Only a "Schema Master" checks in the .sql files

Figure: The db_owner role is granted for one person only – the "Schema Master"

Figure: And here is the "Schema Master" at SSW

STOP! STOP! STOP!
It would be much better to just say:

Mary, click the "Reconcile" button and it will tell us what is wrong

Bottom line is the customers' database schema should always be correct, should be managed automatically by the app and if it is not, it is their problem.

Therefore, you should deliver an application with the buttons "Create", Upgrade" and "Reconcile", accessible via "Tools - Options" and a "Database" tab. We do this by using SSW SQL Deploy and throwing on the inherited user-control from the SSW.SQLDeploy.Options project.

For more information see Best Tools for SQL Server

Figure: Adam makes all his new developers swear in and repeat this

Dear Mr Northwind, 

Before installing your application, you need to 
run this script by 
first opening up SQL Management Studio. 
Open the attached script, point it to Northwind and 
execute the script. 

Let me know if you have any issues... 
We worked very hard on this release. 

I hope you’re happy with it. 

Regards, 
Eric Phan 

Figure: Bad example - run SQL scripts manually

Hi Mr. Northwind, 

Please run the attached Northwind_v5.exe. 

Click Run when the prompt appears. 

Regards,
Eric Phan 

Figure: Better example - run SQL scripts using another package

Dear Mr Northwind, 

When you run the Northwind v1.0 (Rich Client) it will 
automatically upgrade the database for you. 

Just make sure you have dbo permissions: 
Let me know if you run into any issues, 
otherwise have a great day. 

Regards, 
Eric Phan

Figure: Best example - run SQL scripts in the application

Figure: Deploy SQL scripts by the application itself

Figure: How do I make sure these 4 records never go missing?

CREATE PROCEDURE procValidate_Region 
AS

    IF EXISTS(SELECT TOP 1 * FROM dbo.[Region]
              WHERE RegionDescription = 'Eastern')
        PRINT 'Eastern is there'
    ELSE
        RAISERROR(N'Lack of Eastern', 10, 1)
    IF EXISTS(SELECT TOP 1 * FROM dbo.[Region]
              WHERE RegionDescription = 'Western')
        PRINT Western is there'
    ELSE
        RAISERROR(N'Lack of Western', 10, 1)
    IF EXISTS(SELECT TOP 1 * FROM dbo.[Region]
              WHERE RegionDescription = 'Northern')
        PRINT 'Northern is there'
    ELSE
        RAISERROR(N'Lack of Northern', 10, 1)
    IF EXISTS(SELECT TOP 1 * FROM dbo.[Region]
              WHERE RegionDescription = 'Southern')
        PRINT 'Southern is there'
    ELSE
        RAISERROR(N'Lack of Southern', 10, 1)

Figure: Implement a stored procedure to check the 'Controlled Lookup Data' does not go missing

1) First understanding that although most of the time there is a clear separation between data and schema, there is an exception for Controlled Lookup Data. This is when data (aka Controlled Lookup Data) is tightly coupled to the application, meaning that you have an application that cannot function correctly without that data.

2) You need to deploy that 'Controlled Lookup Data'

3) You then need to add a check for it so that it does not disappear.

Let's look at an example:

Figure: This combo looks innocent. However if it is "Billable" then the calendar goes yellow

Figure: Billable days are shown in yellow

if (drDay.NotBillableCount == 0 && 
    drDay.BillableCount > 0)
{
    //Yellow Background
    cell.BackColor = Color.FromArgb(255, 255, 140);
    cell.BackColor2 = Color.FromArgb(255, 255, 140);
}
else if (drDay.BillableCount > 0)
{
    cell.BackColor = Color.FromArgb(255, 255, 140);
    cell.BackColor2 = Color.LightGray;
}
else
{
    cell.BackColor = Color.LightGray;
    cell.BackColor2 = Color.LightGray;
}

Figure: I think we have "Controlled Lookup Data" here, because if the "BillableCount" is greater than 0, the color shown will be yellow

INSERT INTO dbo.[EmpTimeBillable] 
    ([CategoryID], [CategoryName], [DateCreated], 
    [DateUpdated], [EmpUpdated], [Note], [rowguid], 
    [Colour]) 
VALUES 
    ('ALL', '', '09/13/2009 00:00:00', 
    '09/13/2009 00:00:00', 
    'SSW-AdamCogan', 
    'Used for reports - 
     Excluded in Timesheets and Tasklist data entry', 
    '{A9A009A9-4E19-4FD3-B86A-B9260067D0EF}', 
    'White')
GO
INSERT INTO dbo.[EmpTimeBillable] 
    ([CategoryID], [CategoryName], [DateCreated], 
    [DateUpdated],[EmpUpdated], [Note], [rowguid], 
    [Colour]) 
VALUES 
    ('B', 'Billable', '07/01/2009 00:00:00', 
    '07/01/2009 00:00:00', 
    'SSW-AdamCogan', 
    'DON’T CHANGE - These are hard coded', 
    '{F410C25D-1F1A-4340-B7A4-7A4AAE037708}', 
    'Yellow')
GO
INSERT INTO dbo.[EmpTimeBillable] 
    ([CategoryID], [CategoryName], [DateCreated], 
    [DateUpdated], [EmpUpdated], [Note], [rowguid], 
    [Colour]) 
VALUES 
    ('BPP', 'Prepaid Billable', '02/28/2009 15:30:19', 
    '02/28/2009 00:00:00', 
    'SSW-AdamCogan', 
    'DON’T CHANGE - These are hard coded', 
    '{608AA6FF-B3C5-47BE-AC9A-29553E89643D}', 
    'LightYellow')
GO
INSERT INTO dbo.[EmpTimeBillable] 
    ([CategoryID], [CategoryName], [DateCreated], 
    [DateUpdated], [EmpUpdated], [Note], [rowguid], 
    [Colour]) 
VALUES 
    ('U', 'Unknown', '07/01/2009 00:00:00', 
    '07/01/2009 00:00:00', 
    'SSW-AdamCogan', 
    'DON’T CHANGE - These are hard coded', 
    '{74937D60-D2B2-4A4D-96AD-7F5B1941B244}', 
    'White')
GO
INSERT INTO dbo.[EmpTimeBillable] 
    ([CategoryID], [CategoryName], [DateCreated], 
    [DateUpdated], [EmpUpdated], [Note], [rowguid], 
    [Colour]) 
VALUES 
    ('W', 'W/Off', '07/01/2009 00:00:00', 
    '07/01/2009 00:00:00', 
    'SSW-AdamCogan', 
    'DON’T CHANGE - These are hard coded', 
    '{D51513CE-8A1D-41E4-93C4-3E827FF7522B}', 
    'LavenderBlue')
GO

Figure: This data must be deployed, just like we deploy schema

 We have a 'Gender' column (that is a Boolean) storing 0's and 1's. All works well for a while.

Figure: Anything wrong this Gender column? 

Figure: Caster Semenya has taught us a thing or two about the right data type for Gender

1. Rename 'Gender' to 'ztGender' *
2. Add a new column 'Gender' with type char(2)
3. Insert the existing data from 'ztGender' to 'Gender' (map 0 to 'F' and 1 to 'M')
4. Delete the column ztGender*

Figure: Changing the data type and data required a "Data Motion Script"

Visual Studio does not automatically support this scenario, as data type changes are not part of the refactoring tools. However, if you add pre and post scripting events to handle the data type change the rest of the changes are automatically handled for you.

A denormalized field can mean that all SELECT queries in the database are simpler. Power users find it easier to use for reporting purposes - without the need for a cube. In our example, we would not need a large view to retrieve the data (as below).

SELECT 
    Customer.CustomerID, 
    SUM (SalesOrderDetail.OrderQty * 
             (SalesOrderDetail.UnitPrice - 
              SalesOrderDetail.UnitPriceDiscount)
    ) AS DetailTotal, 
    Customer.SalesPersonID, Customer.TerritoryID,
    Customer.AccountNumber, 
    Customer.CustomerType, 
    Customer.ModifiedDate, Customer.rowguid
FROM Customer 
INNER JOIN SalesOrderHeader 
    ON Customer.CustomerID = SalesOrderHeader.CustomerID
INNER JOIN SalesOrderDetail 
    ON SalesOrderHeader.SalesOrderID = 
       SalesOrderDetail.SalesOrderID
GROUP BY Customer.CustomerID, Customer.SalesPersonID, 
    Customer.TerritoryID, Customer.AccountNumber,
    Customer.CustomerType, 
    Customer.ModifiedDate,Customer.rowguid 
ORDER BY Customer.CustomerID

Figure: A view to get customer totals when no denormalized fields are used

SELECT 
    Customer.CustomerID, 
    Customer.OrderTotal AS DetailTotal 
FROM Customer 
ORDER BY Customer.CustomerID

Figure: Queries are much simpler with denormalized fields

Note that this is not a particularly complicated example. However, you can see why it can simplify development greatly when working with a large number of tables

Performance is better for read-intensive reports
Particularly when reporting on data with a cube.

When there a multiple tables in a SQL Server view They cannot be updated in one hit - they must be updated one table at a time.  

It is a built-in validation device For example, if records are accidentally deleted directly in the database, there is still a validation check for the correct totals. The value of this is mitigated when there is a full audit log on the database

However, there are reasons against using denormalized fields

This can makes them unreliable - particularly if several applications are incorrectly updating the denormalized fields. UPDATE, INSERT, DELETEs are more complicated as they have to update the denormalized fields

All in all, we choose to still use denormalized fields because they can save development time. We do this with some provisos. In particular, they must be validated correctly to ensure the integrity of the data.

Here is how we ensure that this data is validated:

1. Change the description on any denormalized fields to include "Denormalized" in the description - "Denormalized: Sum(OrderTotal) FROM Orders" in description in Enterprise Manager
2. Create a view that lists all the denormalized fields in the database - based on the description field.

   CREATE VIEW dbo.vwValidateDenormalizedFields
   AS
       SELECT OBJECT_NAME(id) AS TableName, 
           COL_NAME(id, smallid) AS ColumnName,
           CAST([value] AS VARCHAR(8000)) AS Description,
           'procValidate_' + OBJECT_NAME(id) + 
           '_' + COL_NAME(id, smallid) as
           ValidationProcedureName
       FROM dbo.sysproperties
       WHERE (name = 'MS_Description') AND 
                    (CAST([value] AS VARCHAR(8000))
                     LIKE '%Denormalized:%')
   

   Figure: Standard view for validation of a denormalized field

3. Create a stored procedure (based on the above view) that validates whether all denormalized fields have a stored procedure that validates the data within them

   CREATE PROCEDURE procValidateDenormalizedFieldValidators
   AS
       SELECT 
           ValidationProcedureName AS
           MissingValidationProcedureName 
       FROM vwValidateDenormalizedFields
       WHERE ValidationProcedureName NOT IN
       (
           SELECT ValidationProcedureName
           FROM vwValidateDenormalizedFields AS vw
           LEFT JOIN sysobjects 
           ON 
               vw.ValidationProcedureName = 
               OBJECT_NAME(sysobjects.id)
           WHERE id IS NOT NULL
       )
   

   Figure: Standard stored procedure for validation of a denormalized field

Database scripts should be run in order (into separate sequential files), as per the rule Do you script out all changes?

Therefore developers should not worry about idempotency, as the script will run in the order it was created. Actually, if they are doing this, then *they want to see the errors*. It means that the database is not in the state that they expect.

IF EXISTS (SELECT 1 FROM 
               INFORMATION_SCHEMA.TABLES 
           WHERE 
               TABLE_TYPE='BASE TABLE' AND 
               TABLE_NAME='Employees'
           ) 
    ALTER TABLE [dbo].[Employees]( …… ) ON [PRIMARY] 
ELSE 
    CREATE TABLE [dbo].[Employees]( …… ) ON [PRIMARY]

CREATE TABLE [dbo].[Employees](
    ……
) ON [PRIMARY]

Figure: Viagra isn't the cure to your Idempotency problems