SQL Server 2005 CREATE DOMAIN 2

[unclejimbob]

I am using SQL Server 2005 beta - Microsoft SQL Server Management Studio version 9.00.1116.00

I've read the SQL Server doco and looked around a fair bit but can't really find anything that helps me.

Here is the question:

'What is the recommended approach to implementing a domain in SQL Server that allows me to define a default value ?'

By that I mean that I would like to use something like this:

CREATE DOMAIN "ADDRESSLINE" AS VARCHAR(200)
DEFAULT '' NOT NULL;

SQL Server allows me to do everything but declare a default value and sp_bindefault usage is to be deprecated.

What are my options ?

thanks all

 

[mrdenny]

Where did you find this code? I can't find anything in the 2005 BOL about domains. From what I've read all the defaults are specified within the CREATE TABLE and ALTER TABLE statements.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[unclejimbob]

Where did you find this code?"
I wrote it for my current non-SQL Server 2005 database (i.e. InterBase). This is of course a standard ANSI SQL construct; I know SQL Server 2005 doesn't support it, I just wondered what its equivalent was.

"I can't find anything in the 2005 BOL about domains."
Yes.

"From what I've read all the defaults are specified within the CREATE TABLE and ALTER TABLE statements."
Thats what I have read as well. There _must_ be a better way to do something as simple as this though.

thanks

 

[mrdenny]

Defaults are applied via the create table or alter table statements unfornitually. You could force it via a trigger if you wanted to I supose.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLDenis]

Are you trying to create a user defined data type?


EXEC sp_addtype ADDRESSLINE, 'varchar(200)', 'NOT NULL default '''

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[unclejimbob]

Defaults are applied via the create table or alter table statements unfornitually. You could force it via a trigger if you wanted to I supose."
Hmm, yes, this is definitely a candidate, although what I am after is to be able to define a consistent data type so I'm pretty reluctant to create triggers for all tables that use addresses.

"Are you trying to create a user defined data type?

EXEC sp_addtype ADDRESSLINE, 'varchar(200)', 'NOT NULL default '''"

Yeah, pretty much spot on, I already have umpteen of these already but went with this sort of declaration...

CREATE TYPE AddressLine FROM nvarchar(200) NOT NULL
CREATE TYPE Description FROM nvarchar(MAX) NOT NULL
CREATE TYPE IsModifiable FROM bit NOT NULL
CREATE TYPE IsVisible FROM bit NOT NULL

(note the absence of default values ;-)

...because BOL says in part "sp_addtype will be removed in a future version of Microsoft SQL Server 2005. Avoid using sp_addtype, and plan to modify applications that currently use it. Use CREATE TYPE instead."

Looks like I'm stuck

Hey guys, thanks for all the time you've spent on this, much appreciated, I have decided that I am now down to two options:

1) Triggers
2) Using my code auditing tool to apply the appropriate defaults to known columns i.e. regexp "When I see a column based upon the 'AddressLine' UDT I will apply a default of '' (empty string)"

The main issue for me is that I want to be absolutely sure that, for example, if some hairy chested developer (no offence intended) decides to declare another address line and then tries to concatenate all the address lines together that it doesn't evaluate to a NULL because they forgot to appy a default of '' to the column definition. This is a trivial example - there are other reasons to do with my penchant for removing any possibility of carrying NULLs in columns that don;t need them.

Have a star each on me.