MSSQL UI code generation

Since the start of my career I've used SQL Management Studio (or Query Analyser in MSSQL 2000) to manually create my MSSQL database objects via code. This is a practice I have vehemently defended a number of times - being told that producing objects with the UI tools is "quicker" and "easier". Subjectively, I have to agree with this statement - any kind of well designed UI generation tool is almost certainly going to be quicker than writing a load of CREATE statements by hand... there is of course, a caveat.

I tend to find that developers who do not fully understand RDBMS' (which is a vast majority, unfortunately), or do not enjoy writing databases always take the quick and easy approach. The problem with allowing a UI to generate code is that a large portion of it is unnecessary, and it's sometimes difficult to read. This is true of most generator engines, including those found in .Net.

In terms of SQL generation, hopefully these code snippets will demonstrate my point. The following code is generated by the SSMS UI when you use the UI tools to create a new table:

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Table_2 SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Table_1
    (
    TestPK int NOT NULL IDENTITY (1, 1),
    TestUnique varchar(50) NOT NULL,
    TestFK int NOT NULL,
    Unique1 varchar(50) NOT NULL,
    Unique2 varchar(50) NOT NULL,
    TextCheck varchar(50) NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    CK_Table_1 CHECK (LEN(TextCheck) > 10)
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    Table1_Unique UNIQUE NONCLUSTERED
    (
    TestUnique
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    Unique2_Table1 UNIQUE NONCLUSTERED
    (
    Unique1,
    Unique2
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    FK_Table_1_Table_2 FOREIGN KEY
    (
    TestFK
    ) REFERENCES dbo.Table_2
    (
    FKID
    ) ON UPDATE  NO ACTION
     ON DELETE  NO ACTION
    
GO
ALTER TABLE dbo.Table_1 SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Superfluous, cluttered, ugly and hard to read. Here's exactly the same code, written by hand:

CREATE TABLE dbo.Table_1 (
    TestPK        INT                NOT NULL    IDENTITY,
    TestUnique    VARCHAR(50)        NOT NULL    UNIQUE,
    TestFK        INT                NOT NULL    FOREIGN KEY REFERENCES Table_2(FKID),
    Unique1        VARCHAR(50)        NOT NULL,
    Unique2        VARCHAR(50)        NOT NULL,
    TextCheck    VARCHAR(50)        NOT NULL    CHECK (LEN(TextCheck) > 10),
    CONSTRAINT Unique2_Table1 UNIQUE NONCLUSTERED(Unique1, Unique2)
)
GO

The difference is quite astounding. First of all, in the generated example, there are a bunch of SET statements at the top of the query, and all constraints and keys (with the exception of the primary key) are created in separate statements. In contrast, the hand-coded example declares keys and constraints directly on the table or column during creation - it makes for much neater code (just imagine for a second that it was indented well ;P) and enables you to quickly identify column or database-level constraints and keys.

Whilst all of the code displayed in the generated example is valid, and can be used if required, most is largely irrelevant when creating a standard, well normalised database. I tend to create a set number of scripts that I run in an order, each encapsulating types of database objects e.g.

00 - Constraint Functions.sql
01 - Create Tables.sql
01.01 - Triggers.sql
02 - Initial Inserts.sql
03 - Functions.sql
04 - Stored Procedures.sql

Ad infinitum... this way it's easy for me to set up new instances of a database (e.g. Dev, UAT and Production) and I can keep a version controlled repository of my SQL code as well as .Net code. Updates to a live system come in the format of "Upgrade Scripts", numbered depending on the software version. They are executed only once (after being tested on a development instance first, of course), but also contain validation to stop the script from ruining a database. It's worth noting that backups are important, regardless of how you develop your databases.

T-SQL is an extremely powerful language... a lot more powerful than most developers give it credit for. You generally find most developers will use stored procedures to execute simple CRUD statements - a preferred practice to executing dynamic SQL, but still not using the full potential of T-SQL. You can go as far as to write a lot of your business logic in to your T-SQL procedures, though I tend to stick to manipulating my data in to formats that my business layers / presentation layers can use with as little network overhead as possible.

I'll write more on this at a later date - I actually intended to provide a script to programmatically drop all tables in a database (something I do quite often when designing databases), but I'll put that in my next post.