|
|
Posted by Hugo Kornelis on 10/31/05 00:31
On Sun, 30 Oct 2005 16:22:49 -0500, serge wrote:
(snip)
>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?
Hi Serge,
I'm inclined to agree with you. I don't see how knowledge of the default
values could be of any use for the optimizer.
>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.
Though it's true that NULLs should be used with care, it's also true
that they should be avoided with care. If the nature of your business is
such that you have to deal with missing data, then it's better to use
the token specifically designed for missing date (i.e. NULL) instead of
mucking around with placeholders.
How will you distinguish a bonus of 0 (we thought about it and decided
not to give a bonus) from a bonus of 0 (for any of a whole lot of
possible reasons, no information is known about the bonus at this
moment).
Also, are you aware that all these zeros and spaces will muck up you
aggregate values, and that they can impact the logic of your queries?
Finally - how will you handle datetime variables? Neither 0 nor ' ' can
be stored in them!
> 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
No need for that - you can include the default in the CREATE TABLE
statement.
CREATE TABLE Test
(Col1 int NOT NULL DEFAULT 0,
Col2 varchar(20) NOT NULL DEFAULT ' '
)
INSERT INTO Test DEFAULT VALUES
SELECT * FROM Test
>So my question is should I do this by using sp_binddefault or stick
>with using Default Constraints inside a table/columns loop code?
Use constraints. Bound defaults are proprietary, non-portable; DEFAULT
constraints are defined in the ANSI standard. My guess is that bound
defaults are only retained in SQL Server for backwards compatibility. I
also expect this feature to become deprecated in a future version of SQL
Server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Navigation:
[Reply to this message]
|