Implement real domains - by Erland Sommarskog

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


21
0
Sign in
to vote
ID 739783 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 4/29/2012 1:55:06 PM
Access Restriction Public

Description

In ANSI SQL there exists a concept of domains. It may not be widely implemented, but it is is in PostgreSQL, see http://www.postgresql.org/docs/8.1/static/sql-createdomain.html

The syntax graph on this link is:

CREATE DOMAIN name [AS] data_type
    [ DEFAULT expression ]
    [ constraint [ ... ] ]

where constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

This is not too different than from you can to with CREATE TYPE today. But in PostgresSQL you would say:

   CREATE DOMIAIN update_user AS nvarchar(128)
   DEFAULT SYSTEM_USER
   NOT NULL
   VALUE = SYSTEM_USER

In SQL Server:

   CREATE TYPE update_user FROM nvarchar(128)
   go
   CREATE DEFAULT update_user_def AS SYSTEM_USER
   go 
   EXEC sp_bindefault 'update_user_def', 'update_user'
   go
   CREATE RULE update_user_rule AS @x = SYSTEM_USER
   go 
   EXEC sp_bindrule 'update_user_rule', 'update_user'
   go

There is a big problem here though: CREATE RULE and CREATE DEFAULT as well as the sp_bind procedures are deprecated. In fact, for a while they were slated for removal in 2012. In consequence, there are restrictions where you cannot use types with bound rules or defaults. You cannot use such a type in a table variable, and you cannot use a table with such a column as the target in MERGE.

Another restriction is that a bound rule is never trusted, since a rule cen be unbound, defined and rebound at any time. (Which could be considered to be a feature.)

Yet a problem is that you can also bind a rule or a default directly to a table column, which is completely meaningless since the introduction of constraints in SQL 6.0 and this has some impact how data is stored.

I like to add that an Oracle feature that is often called for is the ability to declare a variable from a table column. I maintain that with a proper use of domains, that should not be necessary.

Sign in to post a comment.
Posted by Microsoft on 4/12/2013 at 2:26 PM
Hello Erland,
After carefully evaluating all of the suggestion items in our pipeline, we are closing items that we will not implement in the near future due to current higher priority items. We will re-evaluate the closed suggestions again in the future based on the product roadmap.

Thanks again for providing the product suggestion and continued support for our product.

--
Umachandar, SQL Programmability Team
Posted by SAinCA on 11/27/2012 at 12:59 PM
The Oracle feature cited by Erland, "I like to add that an Oracle feature that is often called for is the ability to declare a variable from a table column. I maintain that with a proper use of domains, that should not be necessary" is a most EXCELLENT, time-saver, error-preventive, maintenance-enhancer that really *should* be in SQL Server. Having relied upon it for years with Oracle, coming to SQL Server and having to look up the domain of a column just to get the right definition for a local variable, or more importantly a column in a table variable, is time-consuming and simply multiplies maintenance effort needed should the original column domain need to change...

Hate having to say, "Oracle is better", but in this regard it is unequivocally better!
Posted by Gallaeci on 11/26/2012 at 8:11 AM
This issue extends issue: 124645
Posted by Microsoft on 6/14/2012 at 6:25 PM
Hello Erland,
Thanks for your feedback. We will take a look at this request along with others in our pipeline and see how to prioritize it.

--
Umachandar, SQL Programmablity Team