Asger Blond
ab-mi at post3.tele.dk
Mon Mar 16 09:55:55 CDT 2009
> 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