[dba-SQLServer] NULLs

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






More information about the dba-SQLServer mailing list