I have a char(11) for SSN, and I would like to default it to
123-45-6789 so I can avoid having nulls in this column, and so I can
easily find the rows in which I need to have a 'correct' SSN
entered/updated.

I tried using just 123-45-6789, and SQL2005 doesn't seem to be
defaulting to this value, it seems to be keeping it as
(((123)-(45))-(6789), and not placing it into this column when a new
row is created....

Is there some special way I must specify defaults for a char(11) field
(Yes, I will include the dashes).

Thank you,
Tom

Or am I better off allowing fields like this to be null in the
tables/columns specification and enforcing data entry per se in the
front end????

Thank you, Tom

All you have to do is make sure the default is specified as a string. In
other words, make sure you use single quotes to delimit the value.

Try this:

ALTER TABLE MyTable
ADD CONSTRAINT DF_MyTable_MyColumn
DEFAULT ('123-45-6789')
FOR MyColumn

HTH,
Gert-Jan


tlyczko wrote:
>
> I have a char(11) for SSN, and I would like to default it to
> 123-45-6789 so I can avoid having nulls in this column, and so I can
> easily find the rows in which I need to have a 'correct' SSN
> entered/updated.
>
> I tried using just 123-45-6789, and SQL2005 doesn't seem to be
> defaulting to this value, it seems to be keeping it as
> (((123)-(45))-(6789), and not placing it into this column when a new
> row is created....
>
> Is there some special way I must specify defaults for a char(11) field
> (Yes, I will include the dashes).
>
> Thank you,
> Tom

On 25 Sep 2006 11:22:15 -0700, tlyczko wrote:

>Or am I better off allowing fields like this to be null in the
>tables/columns specification and enforcing data entry per se in the
>front end????

Hi Tom,

Gert-Jan answered your question about how to set the default.

But I'm glad you asked this question - in SQL, the NULL symbol is
specifically designed to signal missing data. Not everyone agrees over
the question of the normal behaviour of NULL in different situations is
really the most appropriate for a "missing value" marker. But I don't
think that you'll find anyone who would argue that '123-45-6789' will
ever behave more appropriate for "missing value" than NULL does.

So short answer - don't use a default to signify missing data, use NULL.
And make sure that you understand three-valued logic and other quirks of
the behaviour of NULL.

--
Hugo Kornelis, SQL Server MVP

Hugo Kornelis wrote:

> So short answer - don't use a default to signify missing data, use NULL.
> And make sure that you understand three-valued logic and other quirks of
> the behaviour of NULL.

Can you suggest any good references on this topic??
The ones I know of are based on Access, and I'm sure SS2005 is quite
different!!!

> really the most appropriate for a "missing value" marker. But I don't
> think that you'll find anyone who would argue that '123-45-6789' will
> ever behave more appropriate for "missing value" than NULL does.

Wouldn't using a default value at least avoid the issues of using NULL
per se??

Thank you, Tom

>> Wouldn't using a default value at least avoid the issues of using NULL per se?? <<

What issues? This is a nominal value, not a numeric value. All your
default would do in sort like any other string.

Back in the COBOL days, we use to use all 9's for missing values. You
just held that key down and let it repeat until you got the the end of
the field of the punch card. The missing values would sort to the end
of the reports. In FORTRAN, we left the fields un-punched, and they
were read as all 0's and woudl sort to the top of the reports.

In fact, if you look at a number of standard codes, such as ICD, they
follow these convention today.

--CELKO-- wrote:
> >> Wouldn't using a default value at least avoid the issues of using NULL per se?? <<
>
> What issues? This is a nominal value, not a numeric value. All your
> default would do in sort like any other string.

I meant the quirks of null, etc. mentioned above.

> Back in the COBOL days, we use to use all 9's for missing values. You
> just held that key down and let it repeat until you got the the end of
> the field of the punch card. The missing values would sort to the end
> of the reports. In FORTRAN, we left the fields un-punched, and they
> were read as all 0's and woudl sort to the top of the reports.
> In fact, if you look at a number of standard codes, such as ICD, they
> follow these convention today.

Thank you also for this suggestion. However, I've concluded that since
the dataset requiring SSN information (and similar items that should
generally not be null) is relatively small, that the required text
entry will be enforced in the front end as strongly as possible. If it
really becomes necessary to use a default value in a text string, I'll
use 0's or 9's.

Thank you, Tom

On 26 Sep 2006 08:18:09 -0700, tlyczko wrote:

>
>Hugo Kornelis wrote:
>
>> So short answer - don't use a default to signify missing data, use NULL.
>> And make sure that you understand three-valued logic and other quirks of
>> the behaviour of NULL.
>
>Can you suggest any good references on this topic??
>The ones I know of are based on Access, and I'm sure SS2005 is quite
>different!!!

Hi Tom,

Any good book on relational databases should do. I would be quite
surprised if there's any book on the market that fails to cover the
behaviour of NULL. (Except for books targetted at an experienced
audience, of course).

I'll give a very short explanation below.

>> really the most appropriate for a "missing value" marker. But I don't
>> think that you'll find anyone who would argue that '123-45-6789' will
>> ever behave more appropriate for "missing value" than NULL does.
>
>Wouldn't using a default value at least avoid the issues of using NULL
>per se??

No, you'd be doubly hit by the issues of getting correct results from a
database with missing values. All issues surrounding NULL are related to
defining how missing values should influence the outcome of expressions
and comparisons - you'd have the same issues with a default value that
represents "missing", plus you no longer have the database engine
handling them correctly by default. Oh, and you run the risk that one
day, you need to use the default value as a real value - then what?


Basic issues with NULL:
======================

1. NULL in expressions.

Since NULL represents a missing value, the DB engine doesn't know what
value should be there, or even IF there should be a value there. So if
asked to calculate 3 + NULL, the DB concludes that it can't computer the
result of adding three to a value that has not been given. The answer to
that question can't be given, so the result of 3 + NULL equates to NULL.

More in general - any expression with NULL as one of its operands will
always result in NULL. That holds true even for expressions that a human
could simplify - e.q. calculate 3 + A - A for A equal to NULL: you see
that the same value is added and subtracted again, so you'd know that
the answer is always three, regardless of the value of A. The DB will
first substitute symbols, ending up with 3 + NULL - NULL, i.e. start
with three, add an undisclosed amount, then subtract an undisclosed
amount. Now, the question is unanswerable - the result is NULL.

Note that it's not relevant if the SQL Server development team is or
isn't able to remove the redundant "+ A - A" before substitution - the
behaviour of NULL is defined in the ANSI and ISO standards for SQL, so
such an optimization would result in results that are incorrect as per
the definitions in the standards for SQL.

2. NULL in comparisons.

Please answer this: "Is Hugo Kornelis older than George W. Bush?" No, I
won't tell you my age or date of birth. <g>

You can't answer that question - and yet, that's the kind of questions
that relational databases have to be able to handle. This is handled by
introducing three-valued logic. Comparisons can not only result in True
or False, but also in Unknown. Any comparison that involves NULL on
either side will always result in Unknown (except the special comparison
predicates IS NULL and IS NOT NULL). This also resulted in expanded
truth tables for AND, OR and NOT: (used a fixed font - T, U, and F stand
for True, Unknowd and False respectively).

AND | T | U | F OR | T | U | F NOT
----+---+---+--- ---+---+---+--- ---+---
T | T | U | F T | T | T | T T | F
U | U | U | F U | T | U | U U | U
F | F | F | F F | T | U | F F | T

Many of the things I wrote for NULL in expressions hold true here as
well. Eg, a human would instantly know that "IF A = B OR A <> B" is
always true, regardless of the values of A and B, but if eiither (or
both) is NULL, the database will evaluate this condition to unknown. And
even "IF A <> A" will evaluate to Unknown if A is NULL.

In a WHERE or HAVING expression, the row or group is only included in
the result set if the expression evaluates to True - both False and
Unknown get rejected. On the other hand, a CHECK constraint will only
raise an exception if the condition evaluates to False; if it's either
True or Unknown, the data is allowed.

3. NOT IN with NULL

This point is actually a logical result of point 2 above, but it's such
a common error that I'll mention it anyway:

WHERE Table1.Column1 NOT IN (SELECT foo FROM bar)

This will never return any rows if at least one of the values for foo in
table bar is NULL. The reason is simple, if you think about it - let's
say that bar has four rows and the collection of foo values is {3, 7,
NULL, 2}. Now, what could you possibly answer to the question: "Is a
given value unequal to any of these four values: 3, 7, 2, and a fourth
value that I won't disclose?". You would know foor sure that it's NOT
unequal to any of the four if the given value is 3, 7, or 2. And for any
othher given value, the best answer you can give is "I don't know" -
since it MIGHT be equal to the fourth, undisclosed value.

So SQL Server will evaluate this to either False (for 3, 7, or 2) or to
Unknown - and in both cases, the row gets rejected.

The solution is to rewrite it with NOT EXISTS:

WHERE NOT EXISTS (SELECT * FROM bar WHERE bar.foo = Table1.Column1)

(Or, if you really want to use NOT IN, exclude NULL values:

WHERE Table1.Column1 NOT IN (SELECT foo FROM bar WHERE foo IS NOT NULL)


--
Hugo Kornelis, SQL Server MVP