Developer Product Briefs

Database Dev a Cinch With DDL

Database Definition Language (DDL) makes maintaining changes between your databases easier.

• By Alex Papadimoulis
• 05/01/2006

Database Dev a Cinch With DDL
Database Definition Language (DDL) makes maintaining changes between your databases easier.
by Alex Papadimoulis

May 1, 2006

Of all the tools that ship with SQL Server, Enterprise Manager is by far the most feature-packed and widely-used. Nearly every SQL Server developer is familiar with Enterprise Manager. They are comfortable using the wizards and GUI to do everything from creating a new table to adding a schedule job. But as a project grows to encompass more developers and environments, Enterprise Manager becomes a detriment to the development process.

Most applications exist in at least two different environments: a development environment and a production environment. Promoting changes to code from a lower level (development) to a higher level (production) is trivial. You just copy the executable code to the desired environment.

Copying doesn't cut it when you make changes to the database. Your changes to the lower-level environment must be made to the higher-level environment as well. If Enterprise Manager is used as the database development tool, you must not only remember the set of wizards and GUI used to change the lower-level environment, but you must also follow the same steps to properly migrate your changes to the higher-level environment. Consider, for example, the steps required to add a new table, and modify an existing table so that it includes a foreign key to the new table:

• Click on the desired database.
• Click on Action, New, then Table.
• Add a column named "Shipper_Id" with a Data Type "char", give it a length of 5, and uncheck the "Allow Nulls" box.
• In the toolbar, click on the "Set Primary Key" icon.
• Then you skip 22 steps.
• In the toolbar, click on the "Manage Relationships…" button.
• Click on the New button, and then select "Shippers" as the Foreign key table.
• Select "Shipper_Id" on the left column and "Shipper_Id" on the right column.
• Skip the remaining steps.

Not only is this process tedious, but you're prone to making errors and omissions when using it. Such errors and omissions leave the higher-level and lower-level databases out of sync.

Fortunately, you can use an easier method to maintain changes between databases: Data Definition Language (DDL). The change described in the previous example can be developed in a lower-level environment and migrated to a higher-level environment with this simple script:

CREATE TABLE Shippers (
  Shipper_Id CHAR(5) NOT NULL 
    CONSTRAINT PK_Shippers PRIMARY KEY,
  Shipper_Name VARCHAR(75) NOT NULL,
  Active_Indicator CHAR(1) NOT NULL 
    CONSTRAINT CK_Shippers_Indicator 
      CHECK (Active_Indicator IN ('Y','N'))
)
ALTER TABLE Orders
  ADD Shipper_Id CHAR(5) NULL,
  ADD CONSTRAINT FK_Orders_Shippers
    FOREIGN KEY (Shipper_Id)
    REFERENCES Shippers(Shipper_Id)

You can manage all the DDL scripts with a variety of different techniques and technologies, ranging from network drives to source control. Once a system is put in place to manage DDL scripts, you can use an automated deployment process to migrate your changes. This process is as simple as clicking the "Deploy Changes" button.

The perceived difficulty of switching changes from Enterprise Manager to DDL scripts is one of the biggest hurdles for developers. The Books Online don't help change this perception. A quick look at the syntax for the CREATE TABLE statement is enough to discourage most developers from using DDL.

Enterprise Manager helps you with this transition. Before making database changes, Enterprise Manager generates its own DDL script to run against the database. With the "Save Change Script" button, you can copy the generated DDL script to disk, instead of running it against the database.

But as with any code generator, your resulting T-SQL script is far from ideal. For example, having Enterprise Manager generate the DDL required for the change described in the example involves six different ill-formatted statements. What do you do now? You can add a bit of refactoring to the generated script, and the result looks almost identical to the example script I showed earlier. After a few more rounds of generating and refactoring, you'll want to transition straight to DDL, and never look back at tiresome database development within Enterprise Manager.

About the Author
Alex Papadimoulis lives in Berea, Ohio. The principal member of Inedo, LLC, he uses his 10 years of IT experience to bring custom software solutions to small- and mid-sized businesses and to help other software development organizations utilize best practices in their products. On the Internet, Alex can usually be found answering questions in various newsgroups and posting some rather interesting real-life examples of how not to program on his Web site TheDailyWTF.com. You can contact Alex directly via email at [email protected].