TRUNCATE TABLE master..sysdatabases

...and other neat SQL Server tricks
posts - 51, comments - 60, trackbacks - 14

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 ]

Feedback

Gravatar

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

Like you I saw great benefit in creating database wide Types & Defaults. They streamlined the creation. Made it easier to understand that 1 column in 1 table held the same type of information as that in another. eg: LastNames in all tables were always the same max length.

The down side arose when doing maintenance.
If you want to change a UDT, you must not be using it. Thus ALL tables needed to be copied, emptied, & altered. Sometimes Dropped & Recreated, with flow on impact of changing DRI constraints, security & on it went.


I do not understand your last statement. What exactly would they "Borrow" from PostGreSQL?
How to parse for the CREATE DOMAIN syntax (which is trivial)?
OR
does it also incorporate how to do all the cost based optimisation, instrmentation & all the other work required to integrate this feature? (In my experience most of the time SQL Server runs the same queries much faster than a similar schema PostGreSQL system. So I'd be nervous about them copying optimiser code from PostGreSQL folks)

Of course once they've done that I guess SQL Server would be covered by the Open-Source licensing making it free to all. But given that it looks like the PostGreSQL folks have not yet competed this feature (see the caution in their documentatio), perhaps Microsoft should hold off for a while before handing over SQL to the public domain.
8/25/2010 10:04 PM | David Lean
Gravatar

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

Sir David, Poet of the Far Horizon-

The last remark had a little emoticon ;) to indicate a wink, signifying jest in this instance. I'm not seriously suggesting that they'll put SQL Server into the public domain nor that they'll simply copy the code from PostGreSQL.

As far as incorporating CREATE DOMAIN, it would be trivial to include the syntax but certainly more difficult to have the engine properly implement all of its facets. Though in my opinion, with the existing rules, defaults, constraints and user-defined types sub-systems already in the product, they are probably quite close. And that it's foolish to remove rules and bound defaults without providing a replacement for them.

I agree about the headaches of maintenance for UDTs, but I believe 1) it should be a pain, because it should not be done often, and 2) it's not an issue if the database design is properly reviewed with room for growth considered. e.g. make LastName nvarchar(128) even though most people will never get close to 64 characters...except this guy: en.wikipedia.org/wiki/Sim%C3%B3n_Bol%C3%ADvar . And if you choose a good naming scheme, or use different schemas, you can create new versions of a UDT and deploy them with the same overhead as changing native types would.
8/26/2010 3:10 PM | Rob Volk
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET