Database design & NULL Values - looking for DBA opinion
Jeremy
indiferent?
What are the top 10+ things that indicate a poor database design?
TIA,
Jeremy
Vishal Parkar
char, nchar takes more disk space to store this is because
it takes up the entire size of the column.
2) Null value require additional i/o to process the
request, hence it gives additional overhead on processor
as well.
3) The overhead for handling null values are more in SQL
Server 2000 as compared to other versions.
4) When you specify IS NULL/IS NOT NULL clause in the
query, indexes on the field will be suppressed and result
will be full table scan(performance degradation).
Ideally, NULL should be avoided while designing SQL Server
databases.
--Vishal
>.
>
Kalen Delaney
full width, regardless of whether you allow NULLs or not. I don't think this
is an argument against the use of nulls.
2) Not true.
3) Not true. It was more processing in SQL 7 when columns allowing NULLs
were treated as variable length; all variable length column require a bit of
extra processing.
4) This is true. Any time a column is used inside a function in the WHERE
clause, the optimizer cannot take advantage of an index on that column.
The biggest reason for NOT using NULLs is that they are so meaningless. You
have no idea what it means if someone enters a NULL. Does it mean the value
isn't known now, the value can never be known, the value isn't relevant, or
something else.
Earlier today there was a post where someone was adding numbers and wanted
NULL to be considered 0. Someone used the example of a bank account balance,
and said a 0 would really mean no money, but NULL would mean no bank
account. That is not completely true. NULL could also mean that you are just
so disorganized you haven't balanced your statement in years, and you have
absolutely no idea what your balance is. It also could mean you haven't
looked in the last day and just don't know right now.
NULL means unknown, and there are so many reasons a value could be unknown
that I like to avoid it if possible. Plus the fact that NULLs are never
equal to anything, but they are also never NOT equal to anything, so you end
up with tri-valued logic. This makes your coding much more complex.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vishal Parkar" <vgpa...@hotmail.com> wrote in message
news:083f01c2dec9$09ab9df0$3301...@phx.gbl...
BP Margolin
NULLs have their place, just like most anything thing else.
I think the important thing to remember about NULLs is that they are very different from a zero value or an empty string. Logically a NULL value is supposed to represent something more like "unknown" or "not applicable". For example, say that you are creating a human resource system, and as part of the system you have to track retired personnel as well as currently employees. You very probably will need columns something like "Termination_Date" and "Death_Date". In this instance, a NULL is either column probably means "not applicable" ... in other words, the individual is either still employeed and/or still living. No other value logically represents that, and so a NULL is not only a reasonable choice, but is the logically correct choice for these columns.
The proper handling of NULLs in queries can be difficult for those not well-versed in three-valued logic, so if it is possible to avoid a NULL in a column, then that is almost certainly the better choice. But when NULL is the logically correct entry, then, IMHO, it simply makes the logic even more difficult by trying to create a "dummy" value to represent "unknown" or "not applicable".
> What are the top 10+ things that indicate a poor database design?
I can't answer that one, but I'll tell you the top 1 thing that indicates a poor question ... not providing sufficient contextual information. For example, what is the database supposed to do ... handle an OLTP application or an DSS / OLAP application? Depending upon the type of application, one either should tend to a normalized or a star schema ... but until one knows the type of application, everything else is moot.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Jeremy" <thevis...@hotmail.com> wrote in message news:gHx7a.54602$0n2.1...@twister.tampabay.rr.com...
BP Margolin
I have to say that I disagree with just about everything you've posted ... even when factually correct, the answers, IMHO, are extremely misleading.
However, #4 is just completely factually wrong :-(
create table Vishal
(
c1 int NULL unique,
c2 char (8000) NOT NULL
)
go
insert into Vishal values (1, 'a')
insert into Vishal values (2, 'b')
insert into Vishal values (3, 'c')
insert into Vishal values (4, 'd')
insert into Vishal values (5, 'e')
insert into Vishal values (6, 'f')
insert into Vishal values (7, 'g')
insert into Vishal values (8, 'h')
insert into Vishal values (9, 'i')
insert into Vishal values (10, 'j')
insert into Vishal values (11, 'k')
insert into Vishal values (NULL, 'l')
go
select *
from Vishal
where c1 is null
go
The estimated execution plan shows that the SQL Server uses an INDEX SEEK on the query.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"Vishal Parkar" <vgpa...@hotmail.com> wrote in message news:083f01c2dec9$09ab9df0$3301...@phx.gbl...
Kalen Delaney
the ISNULL( ) function.
So, #4 is also not true.
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"BP Margolin" <bpm...@attglobal.net> wrote in message
news:uTHvBst3...@TK2MSFTNGP11.phx.gbl...
Nigel Rivett
Mean unknown or n/a - not sure it really matters which.
Anyone writing sql really ought to get used to manipulating expressions
with null - not knowing how is a reason to learn not to avoid them.
Another question is what about foreign keys to a lookup table? Do you
put in null or put an entry in the lookup for unknown.
What are the top 10+ things that indicate a poor database
design?
Impossible to say as most things can be applicable in some situations.
Pet hates though
Use of cursors - this can indicate bad design or lack of understanding.
I always ban them as it forces people to think about set based
processing.
Now DTS - fine product but it gets used for everything and you end up
with lots of processing being controlled from packages. People seem to
be asking how can I use dts for this rather than what's the best way to
do this.
Embedded sql in applications.
Trying to change structure / security entities on-line.
Spending ages over hardware efficiency when improving the database
design/coding would have vastly greater effect.
Cold fizzy beer - has no place in database development.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jeremy
"Jeremy" <thevis...@hotmail.com> wrote in message
news:gHx7a.54602$0n2.1...@twister.tampabay.rr.com...