[SQL2005] Binding defaults without using sp_bindefault

The SQL2005 documentation states the sp_bindefault will be gone in the next
version and the DEFAULT keyword should be used with CREATE TABLE or ALTER
TABLE statements. But I seem to have run into a situation where that only th
e
stored procedure approach works.
script 1:
CREATE DEFAULT abc AS 1
script 2:
CREATE TABLE xyz(
id int IDENTITY(1,1) NOT NULL,
thingy int DEFAULT abc)
This will result in the following error: "The name "abc" is not permitted in
this context. Valid expressions are constants, constant expressions, and (in
some contexts) variables. Column names are not permitted."
It works perfectly when I use the sp_bindefault procedure instead, so I do
have a solution, but can it be done without? I am probably missing something
very simple, so any help is welcome. TIA!Check documentation for CREATE DEFAULT and you will see that this will also
disappear. What BOL is
trying to say is that default objects will disappear, while default constrai
nts is the way to go.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
news:F6F545C2-D304-46BA-A5D4-DC07660BAECE@.microsoft.com...
> The SQL2005 documentation states the sp_bindefault will be gone in the nex
t
> version and the DEFAULT keyword should be used with CREATE TABLE or ALTER
> TABLE statements. But I seem to have run into a situation where that only
the
> stored procedure approach works.
> script 1:
> CREATE DEFAULT abc AS 1
> script 2:
> CREATE TABLE xyz(
> id int IDENTITY(1,1) NOT NULL,
> thingy int DEFAULT abc)
> This will result in the following error: "The name "abc" is not permitted
in
> this context. Valid expressions are constants, constant expressions, and (
in
> some contexts) variables. Column names are not permitted."
> It works perfectly when I use the sp_bindefault procedure instead, so I do
> have a solution, but can it be done without? I am probably missing somethi
ng
> very simple, so any help is welcome. TIA!
>
>
>|||Don't bind defaults, ADD them:
alter table <table name>
add constraint abc
default (1)
for <column name>
ML
http://milambda.blogspot.com/|||I could do that, but the whole idea for me was to define a centralized
default, so when I change that, the changes will be reflected in every colum
n
binded to that default.
"ML" wrote:

> Don't bind defaults, ADD them:
> alter table <table name>
> add constraint abc
> default (1)
> for <column name>
>
> ML
> --
> http://milambda.blogspot.com/|||Is it possible to bind a single default constraint to multiple columns in
multiple tables? I.e. I have a date column in every table which should be
filled with a default value if none is provided.
At the moment I made a default for it and linked the name of the default to
the date column. In my database design tool, I only have to change the
default value in one place if I ever decide the current one is not right
anymore. Let the tool create a script for me and it is changed.
With constraints I have to change the default value in every single table
(200+), while I could do it with just one simple change when using
sp_bindefault was allowed. Or can it be done more easily?
"Tibor Karaszi" wrote:

> Check documentation for CREATE DEFAULT and you will see that this will als
o disappear. What BOL is
> trying to say is that default objects will disappear, while default constr
aints is the way to go.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
> news:F6F545C2-D304-46BA-A5D4-DC07660BAECE@.microsoft.com...
>|||As Tibor states in his post the default as a SQL object is on its last breat
h
(so to speak), as for the default as a constraint it cannot be reused as if
it were an object.
A constraint is a declaration, and if your requirement really is
object-oriented (rather than declarative) then maybe you could get what you
need by using a user-defined CLR type. But only for a default? I wouldn't go
down that road.
While being aware of the benefits a default provides as an object (e.g.
reusability), I still think a default is merely an attribute of the
individual column, and should be treated as such - kept in the meta world.
ML
http://milambda.blogspot.com/|||Ah hold on, I know what to do ;-)
Currently I have a UDDT for a mandatory date column and bind a default to it
when I need to. The solution is to create two UDDTs, one with and one withou
t
the constraint. It's gonna be some work to convert 200+ tables, but it's a
solution ...
"PaulSand" wrote:
> Is it possible to bind a single default constraint to multiple columns in
> multiple tables? I.e. I have a date column in every table which should be
> filled with a default value if none is provided.
> At the moment I made a default for it and linked the name of the default t
o
> the date column. In my database design tool, I only have to change the
> default value in one place if I ever decide the current one is not right
> anymore. Let the tool create a script for me and it is changed.
> With constraints I have to change the default value in every single table
> (200+), while I could do it with just one simple change when using
> sp_bindefault was allowed. Or can it be done more easily?
>
> "Tibor Karaszi" wrote:
>|||Oops, sorry for making a mess here, but that isn't a solution either ...
"PaulSand" wrote:
> Ah hold on, I know what to do ;-)
> Currently I have a UDDT for a mandatory date column and bind a default to
it
> when I need to. The solution is to create two UDDTs, one with and one with
out
> the constraint. It's gonna be some work to convert 200+ tables, but it's a
> solution ...
> "PaulSand" wrote:
>|||Mess? Aren't we learning anymore? :)
ML
http://milambda.blogspot.com/|||> Is it possible to bind a single default constraint to multiple columns in
> multiple tables?
No. In short:
Default objects are deprecated. Those does what you want to do.
Default constraints are supported and recommended way in the future. But you
create them at the
column level, so they cannot be defined-one-used-many.
You might want to post a wish to [url]http://lab.msdn.microsoft.com/productfeedback/.[/
url] The ANSI SQL
feature you are looking for is the "DOMAIN" (CREATE/ALTER DOMAIN), but that
is not implemented in
SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"PaulSand" <PaulSand@.discussions.microsoft.com> wrote in message
news:6D87E279-5C8F-4BB6-9394-28454BD4B89D@.microsoft.com...
> Is it possible to bind a single default constraint to multiple columns in
> multiple tables? I.e. I have a date column in every table which should be
> filled with a default value if none is provided.
> At the moment I made a default for it and linked the name of the default t
o
> the date column. In my database design tool, I only have to change the
> default value in one place if I ever decide the current one is not right
> anymore. Let the tool create a script for me and it is changed.
> With constraints I have to change the default value in every single table
> (200+), while I could do it with just one simple change when using
> sp_bindefault was allowed. Or can it be done more easily?
>
> "Tibor Karaszi" wrote:
>