AllowNulls overrides default?

If I have a table column type smalldate checked to allow NULLs and also have
a default of getdate(), will this column value always be NULL if nothing is
inserted into it upon record insertion of other columns?
It seems to be. Must I delete that column if I want to not allow NULLs?
Thanks,
BrettAllowing null will not override default. See below:
CREATE TABLE #t (c1 int, c2 datetime null default getdate())
INSERT INTO #t(c1, c2) values(1, null)
INSERT INTO #t(c1) values(1)
INSERT INTO #t(c1, c2) values(1, DEFAULT)
SELECT * FROM #t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Brett" <no@.spam.net> wrote in message news:%23fgmZWIKFHA.2880@.TK2MSFTNGP09.phx.gbl...[colo
r=darkred]
> If I have a table column type smalldate checked to allow NULLs and also ha
ve a default of
> getdate(), will this column value always be NULL if nothing is inserted in
to it upon record
> insertion of other columns?
> It seems to be. Must I delete that column if I want to not allow NULLs?
> Thanks,
> Brett
>[/color]|||Well, I always get NULLs in that column rather than the default.
Brett
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e6Bl3cIKFHA.3500@.TK2MSFTNGP14.phx.gbl...
> Allowing null will not override default. See below:
> CREATE TABLE #t (c1 int, c2 datetime null default getdate())
> INSERT INTO #t(c1, c2) values(1, null)
> INSERT INTO #t(c1) values(1)
> INSERT INTO #t(c1, c2) values(1, DEFAULT)
> SELECT * FROM #t
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Brett" <no@.spam.net> wrote in message
> news:%23fgmZWIKFHA.2880@.TK2MSFTNGP09.phx.gbl...
>|||Did you run my script?
What application are you using?
Did you Profiler trace the application to see the INSERT statement?
My guess is that the applications specifies NULL for the column in the INSER
T statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Brett" <no@.spam.net> wrote in message news:uo1DmpIKFHA.3788@.tk2msftngp13.phx.gbl...darkred">
> Well, I always get NULLs in that column rather than the default.
> Brett
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:e6Bl3cIKFHA.3500@.TK2MSFTNGP14.phx.gbl...
>|||Brett wrote:
> Well, I always get NULLs in that column rather than the default.
> Brett
>
Please post your complete insert statement.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:u5elsKKKFHA.2784@.TK2MSFTNGP09.phx.gbl...
> Brett wrote:
> Please post your complete insert statement.
> --
> David Gugick
> Imceda Software
> www.imceda.com
I think this is occuring because I added this column after the table was
created. I had to allow NULLs in order to add the column. Let me research
a little more and I will post my results.
Thanks,
Brett|||Brett wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:u5elsKKKFHA.2784@.TK2MSFTNGP09.phx.gbl...
> I think this is occuring because I added this column after the table
> was created. I had to allow NULLs in order to add the column. Let
> me research a little more and I will post my results.
> Thanks,
> Brett
I just tested with an added column and I get the default value when
inserted. We'll await your post.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ONOXbmKKFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Brett wrote:
> I just tested with an added column and I get the default value when
> inserted. We'll await your post.
>
But if you create a table, add four records then add the new column with a
default and allow nulls, what values does it have? I tried this and the
results are as follows:
Adding new records to the table I mention in this post does get the default.
Those in the table before I added this new column have NULLs. I couldn't
add the new column without allowing NULLs because of the previous records.
Thanks,
Brett|||Brett wrote:
> But if you create a table, add four records then add the new column
> with a default and allow nulls, what values does it have? I tried
> this and the results are as follows:
> Adding new records to the table I mention in this post does get the
> default. Those in the table before I added this new column have
> NULLs. I couldn't add the new column without allowing NULLs because
> of the previous records.
Ok, I think what you are saying is that the existing rows that were in
the table before you added the new column do not pick up the default.
That's expected behavior. Defaults are only applied on inserts when the
column is left out of the insert. There is no way for SQL Server to know
whether those NULLs in the existing columns were intentionally put
there. You'll need to run an update on the table and change the values
you need changed to the default.
David Gugick
Imceda Software
www.imceda.com