September 27, 2009 at 6:03 am
I am moving a large application to SQL server and have to decide on the best table structure.
I can allow NULLs in most of my fields and I assume this is good for storage space, but it makes my code more complex because I need to test for null entries as well as my existing tests.
select top 1 @cert_key = L.cert_key,
@First_end = IC.first_end
from dbo.insur_lnk_ShellCert as L inner join dbo.insur_Certs as IC
on L.cert_key = IC.cert_key
where L.coi_shell = @COI_Shell
and (IC.mul is null or IC.mul != 'M')
order by IC.first_end DESC
If the char(1) field mul above was an empty string the test just becomes IC.mul != 'M'
Which fields are best set to NULL when records are added?
Thanks
September 28, 2009 at 1:29 am
Making columns nullable actually costs you 1 bit per nullable column, so non_nullable will actually save you some space - albeit only a little - so your premise about making them nullable to save space is the wromg way round.
In general I would work to the principle that columns should nearly always be non-nullable with a nullable column being thr exception rather than the rule.
Mike
September 28, 2009 at 4:25 am
Before making the decision, bear in mind that NULL does have a meaning.
As an example, if you have a Contact table, and as well as name and address, you want to record their Fax Number, how would you differentiate between "they haven't got one" as opposed to "I haven't asked them". In this case a blank string would show that they don't have a number, and NULL would show that you haven't asked.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply