TRUNCATE TABLE master..sysdatabases

How to make your databases better despite Microsoft's Best Practices (AND REDUCE TYPING!)

I added the parenthetical title because no one really cares if they can make their databases better. :)

Based on some recent tweets (one and two) and blogs I've been reading/writing, I thought I'd create an example of some excellent but underutilized features in SQL Server: defaults and user-defined types.

User-defined types (UDTs) are a way of renaming a standard SQL type and additionally providing a NULL or NOT NULL constraint on that type:

CREATE TYPE [USERNAME] AS varchar(64) NULL;

This can be used in a table definition like so:

CREATE TABLE [MyTable] [UserCreated] USERNAME;

It saves some typing, and I get an automatic NULL constraint on the column, which I can override with NOT NULL.  (Normally I do the reverse; allowing nulls is not a "constraint")

Defaults are a deprecated feature that will be removed from SQL Server at a later date.  Why?  Because someone hates this:

CREATE DEFAULT [whoami] AS SUSER_SNAME();

They also hate this:

EXEC sp_bindefault 'whoami', 'USERNAME';

Which allows me to reduce this:

ALTER TABLE [dbo].[MyTable]
    ADD [UserCreated]  VARCHAR (64) CONSTRAINT [DF_MyTable_UserCreated] DEFAULT (suser_sname()) NULL,
        [UserModified] VARCHAR (64) CONSTRAINT [DF_MyTable_UserModified] DEFAULT (suser_sname()) NULL;

to this:

ALTER TABLE [dbo].[MyTable]
    ADD [UserCreated]  USERNAME,
        [UserModified] USERNAME;

Yeah I know, when you add up the default and UDT definitions and bindings, you...still save a decent amount of typing, but not all that much...

...unless you have several hundred tables that use the exact same definition.  To rehash from my blog post:

ALTER TABLE [dbo].[MyTable]
    ADD [DateCreated]  DATETIME     CONSTRAINT [DF_MyTable_DateCreated] DEFAULT (getdate()) NULL,
        [DateModified] DATETIME     CONSTRAINT [DF_MyTable_DateModified] DEFAULT (getdate()) NULL,
        [UserCreated]  VARCHAR (64) CONSTRAINT [DF_MyTable_UserCreated] DEFAULT (suser_sname()) NULL,
        [UserModified] VARCHAR (64) CONSTRAINT [DF_MyTable_UserModified] DEFAULT (suser_sname()) NULL;

With a quick mod to my Tweet:

CREATE TYPE [MYDATE] AS DATETIME;
GO
CREATE DEFAULT [now] AS GETDATE();
GO
EXEC sp_bindefault 'now', 'MYDATE';

I can really beat this horse to death:

ALTER TABLE [dbo].[MyTable]
    ADD [DateCreated]  MYDATE,
        [DateModified] MYDATE,
        [UserCreated]  USERNAME,
        [UserModified] USERNAME;

And reduce my typing by over 60%.  By the way, did you like how the datetime UDT, default definition, and binding all fit in a single 140-character tweet?  I sure did!

I'll leave it up to you to decide if consistent, reusable data types with shorter names and identical defaults are worthwhile.  And in case you're asking, yes, I've had developers argue AGAINST this very sentence.

Rules are another non-ANSI feature that SQL Server inherited from Sybase, and Microsoft also feels are A Bad Thing™ that should be deprecated. I don't have a good example right now but I should have one in a future blog soon. In the meantime you can read some of my thoughts in this forum post.

I can understand Microsoft wanting to remove Sybase cruft, especially if it's non-ANSI standard, but so far I've not heard of any new features to replace them, such as CREATE DOMAIN, which is ANSI standard and neatly combines types, defaults, and rules into one package.

If you feel this is a useful feature to have, and a bad one to lose, please visit Microsoft's Connect site and vote on this item. It shouldn't be that hard for them to implement, since they can just borrow the code from the open-source PostGreSQL project. ;)

Print | posted on Tuesday, August 17, 2010 4:58 PM | Filed Under [ Coolness ]