Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question of the Day for 04 Oct 2005 Expand / Collapse
Author
Message
Posted Monday, October 3, 2005 6:05 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the Question of the Day for 04 Oct 2005 posted at http://www.sqlservercentral.com/testcenter/qod.asp?QuestionID=610.
Post #225674
Posted Tuesday, October 4, 2005 2:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, January 20, 2006 1:35 AM
Points: 386, Visits: 1
I don't think this is the complete story: if the field is defined NOT NULL then the existing rows WILL get the default value. So the really, the correct answer depends on the SET ANSI_NULL_DFLT_ON or ANSI_NULL_DFLT_OFF setting  (or whatever EXEC sp_dboption does in this respect).



Post #225728
Posted Tuesday, October 4, 2005 2:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 8, 2010 4:54 PM
Points: 23, Visits: 3

Just to make the lesson a bit more useful you should add that you can use WITH VALUES or NOT NULL to get the new column populated with the default values.

Post #225729
Posted Tuesday, October 4, 2005 8:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 24, 2010 8:44 AM
Points: 254, Visits: 18
I agree.  I got the question right, but it works with the default settings only.  I thought the question would be about the ANSI NULL settings, obviously that affects the NULLability of the created columns.


Dylan Peters
SQL Server DBA
Post #225854
Posted Thursday, January 19, 2006 10:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:12 PM
Points: 215, Visits: 26
I got the answer correct - but strictly speaking the explanation should be not that "...the existing rows are 'populated' with NULL..." BUT rather that "...new colums on existing rows are NOT POPULATED AT ALL".
i.e. a NULL value is a lack or absence of a value.
Post #252093
Posted Monday, November 19, 2012 3:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:09 AM
Points: 1,916, Visits: 2,337
good discussion as the explanation is not correct properly...


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1386264
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse