|
|
Posted by David Portas on 10/31/05 00:19
"serge" <sergea@nospam.ehmail.com> wrote in message
news:KQa9f.3990$u8.202495@weber.videotron.net...
>I am doing a little research on Google about this topic and I ran into
> this thread:
>
> http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/63cce060ff152dcc/1dc13d4ee6758966?lnk=st&q=difference+constraint+sql+defaults&rnum=14#1dc13d4ee6758966
>
> I read SQL Server MVP Louis Davidson's post saying:
>
> "Actually they are more likely to drop the concept of bound defaults.
> Constraints are the standard way to do this, and really should be the way
> you create defaults anyhow."
>
> Even I read in the Microsoft SQL Server Introduction (SQL 7 book
> page 244, however we're using SQL Server 2000):
>
> "Constraints define rules regarding the values allowed in columns and are
> the standard mechanism for enforcing integrity, preferred over triggers,
> rules, and defaults. They are also used by the query optimizer to improve
> performance in selectivity estimation, cost calculations, and query
> rewriting."
>
> Why constraint defaults are better? The second sentence about constraints
> having better optimization, I am guessing they don't mean this about
> Default Constraints, rather the other type of constraints?
> Because I don't see how a Default Constraint have anything to do with
> performance? Isn't default only to do with new records being created?
>
>
> At work we are setting all tables' columns to have constraint defaults
> of 0 or ' ' (space character) in order not to have any column with the
> NULL value. Therefore we have dozens of files containing statements like:
>
> alter table TABLE1 add constraint TABLE1_ID_DF
> DEFAULT(' ') FOR ID
> go
> alter table TABLE1 add constraint TABLE1_QUANTITY_DF
> DEFAULT(0) FOR QUANTITY
> go
>
> First I was thinking to create 3 SQL Defaults called:
> DefaultZero
> DefaultSpace
> DefaultDate
>
> and then bind these defaults to all the columns of all tables excluding
> primary keys. After creating the tables I would enumerate through
> all the columns and bind one of these three Defaults based on their
> datatype:
> number = DefaultZero
> text type = DefaultSpace
> date type = DefaultDate
>
> And then unbind the ones that we specifically need to specify other
> default values.
>
> So my question is should I do this by using sp_binddefault or stick
> with using Default Constraints inside a table/columns loop code?
>
>
> Thank you
>
>
>
Default constraints are closer to standard SQL and in my experience are the
more conventional solution and better understood by most people using SQL
Server today. Here's a quote from SQL Server 2005 Books Online about the
legacy CREATE DEFAULT / sp_binddefault syntax:
"CREATE DEFAULT will be removed in a future version of Microsoft SQL Server.
Avoid using CREATE DEFAULT in new development work, and plan to modify
applications that currently use it. Instead, use default definitions created
using the DEFAULT keyword of ALTER TABLE or CREATE TABLE."
--
David Portas
SQL Server MVP
--
Navigation:
[Reply to this message]
|