Asger Blond
ab-mi at post3.tele.dk
Mon Mar 16 10:32:14 CDT 2009
OK, I see. Regarding the example: Ever heard of nepotism? The HR manager is asked to register the boss' son as a new employee not deciding for the moment which department should have the honour. And of course: Normally a FK constraint should not allow NULLs. But the fact that you have to explicitly prevent this using a NOT NULL constraint makes room for exceptions. Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Francisco Tapia Sendt: 16. marts 2009 15:59 Til: Discussion concerning MS SQL Server Emne: Re: [dba-SQLServer] NULLs I was simply giving an example of how such a join might look. Also, I thought it would be odd for a company to hire an employee w/o already having a position to fill, generally I've never seen this to be true, but possibly does happen, just haven't ran into it myself. -- -Francisco http://sqlthis.blogspot.com | Tsql and More... On Mon, Mar 16, 2009 at 7:55 AM, Asger Blond <ab-mi at post3.tele.dk> wrote: > > Logically the join between the employee and the dept can > > be a many to many which would allow you to have an unassigned employee > > Don't know if I get your point here. Are you implying that an unassigned > employee can't exist in a one to many relationship with referential > integrity? If so you are not right: a FK constraint doesn't forbid NULLs in > the FK column. > > Asger > > -----Oprindelig meddelelse----- > Fra: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Francisco > Tapia > Sendt: 16. marts 2009 14:28 > Til: Discussion concerning MS SQL Server > Cc: Discussion concerning MS SQL Server > Emne: Re: [dba-SQLServer] NULLs > > As with everything the answer is, it depends. How is your database > architected? Logically the join between the employee and the dept can > be a many to many which would allow you to have an unassigned employee > however, I don't think that makes much business sense. Generally, a > company does not hire an employee without knowing what position they > need or want him for. You see? > > As for the argument about nulls, this topic reminds me of the old jc > days on the access list. I still think there are valid places that > require a null in your db. > > I have yet to see a performance hit for nulls in an index but it > stands to reason that generally it doesn't make sense to allow them. I > think this may affect you more depending on your collation you have > chosen. > > > Sent from my mobile > > On Mar 15, 2009, at 4:23 PM, "Asger Blond" <ab-mi at post3.tele.dk> wrote: > > > Coming in late I have a few comments here. > > 1. In SQL Server you can make indexes on NULL allowed columns, and the > > engine sure will use these indexes if they are useful for the queries. > > 2. The column order of NULL allowed columns is of no importance in SQL > > Server as it maintains header information for each row telling if NULL > > allowed columns have a value or not. > > 3. And Arthur: "obviously I forbid them in any FK column". Don't > > agree, > > seems quite allowable to me. Imagine you have a Department and an > > Employee > > table. Your Employee table has a FK referencing the Department > > table. Now > > you just hired a new employee but haven't decided yet the department > > for the > > employee. Isn't it reasonable to make a record for the new employee > > leaving > > the FK column NULL? > > > > Asger > > > > -----Oprindelig meddelelse----- > > Fra: dba-sqlserver-bounces at databaseadvisors.com > > [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arth > > ur > > Fuller > > Sendt: 14. marts 2009 17:13 > > Til: Discussion concerning MS SQL Server > > Emne: [dba-SQLServer] NULLs > > > > Over on the MySQL group, a discussion has been waging about the use of > > NULLs. One guy in particular is adamant about forbidding them > > everywhere. He > > goes so far as to say the existence of NULLs means bad design, pure > > and > > simple. I am not quite so radical about it, but I do look askance at > > any > > column that permits them, and obviously I forbid them in any FK > > column. > > > > I tossed that in just to take the pulse of this group on this > > general topic, > > but my real question concerns something one of the posters wrote: > > > > "It seems that someone got bitten very hard in a soft area by a Null > > one > > day. > > On a more practical level, Oracle recommended to design the table > > with the > > field where NULLs are to be expected at the end of the table, that > > saves a > > little space and two, as the use of indexes is invalidated in case > > of nulls > > its is better to place the important indexed fields toward the > > beginning of > > the table." > > > > I don't know whether this is also true of SQL Server. Do you? > > > > Arthur > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com