A developer has just gave me a few tables table to put
into production, with all character columns defined as
varchar(255), null.
As I am absolutely positive that the majority of these
columns will not be storing this amount of data in each
of the columns, as some of them are code columns, types,
and one description field.
After asking him to review all of the columns and the
maximum estimated data stored in each one, he said that
it shouldn't make any difference because A) they're
varchar and B) they're defined as null.
What can I say to him to make him do the needful?
Thanks,
MarcusMarcus
Well, variablle-length characters ( as in your case) is stored 1 byte per
character.Declared but unused characters don't consume storage.
On the other hand I'll be conserned about using NULL's when you will be
querieng the tables.
Using IS NULL/NOT NULL clause will not allow Query Optimizer to use an index
and respectively your query will suffer from perfomance hit.
Also consider using DEFAULT values in those columns.
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
> A developer has just gave me a few tables table to put
> into production, with all character columns defined as
> varchar(255), null.
> As I am absolutely positive that the majority of these
> columns will not be storing this amount of data in each
> of the columns, as some of them are code columns, types,
> and one description field.
> After asking him to review all of the columns and the
> maximum estimated data stored in each one, he said that
> it shouldn't make any difference because A) they're
> varchar and B) they're defined as null.
> What can I say to him to make him do the needful?
> Thanks,
> Marcus|||Pick up one of Joe Celko's post from here regarding how important it is to
get the database design right and where he state that he almost never have
to use NULL. If he question Joe's credibility, search for instance Amazon of
the books he has been writing (or go to www.celko.com).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
> A developer has just gave me a few tables table to put
> into production, with all character columns defined as
> varchar(255), null.
> As I am absolutely positive that the majority of these
> columns will not be storing this amount of data in each
> of the columns, as some of them are code columns, types,
> and one description field.
> After asking him to review all of the columns and the
> maximum estimated data stored in each one, he said that
> it shouldn't make any difference because A) they're
> varchar and B) they're defined as null.
> What can I say to him to make him do the needful?
> Thanks,
> Marcus|||Right. I've brought that up also and he's come back and
said that the table is for importing data only and it
shouldn't matter.
You're thoughts?
Thanks!
Marcus
>--Original Message--
>Marcus
>Well, variablle-length characters ( as in your case) is
stored 1 byte per
>character.Declared but unused characters don't consume
storage.
>On the other hand I'll be conserned about using NULL's
when you will be
>querieng the tables.
>Using IS NULL/NOT NULL clause will not allow Query
Optimizer to use an index
>and respectively your query will suffer from perfomance
hit.
>Also consider using DEFAULT values in those columns.
>
>"Marcus" <anonymous@.discussions.microsoft.com> wrote in
message
>news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
types,
>
>.
>|||Marcus
Look, we can discuss a lot on this topic.
In outer-join operation you should carefully account for NULL's values that
are generated to preserve rows that don't have a match in the table being
joined.
Also dealing with NULL's adding complexity to the storage engine because SQL
Server keeps a special bitmap in every row to indicate which nullable
columns actually are NULL. SQL Server must decode the bitmap for every row
accessed.
Again I'd recommed you/your opponent to make all nullable columns as NOT
NULL and DEFAULT values.
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8c2301c404f2$255e24a0$a501280a@.phx.gbl...
> Right. I've brought that up also and he's come back and
> said that the table is for importing data only and it
> shouldn't matter.
> You're thoughts?
> Thanks!
> Marcus
> stored 1 byte per
> storage.
> when you will be
> Optimizer to use an index
> hit.
> message
> types,|||"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:8b7101c404e1$04e07c70$a401280a@.phx.gbl...
> A developer has just gave me a few tables table to put
> into production, with all character columns defined as
> varchar(255), null.
Two aspects :
varchar(255), there is nothing wrong defining a varchar
type with a number of characters. Varchar(20) and varchar(255)
both occupie the same number of bytes if the strings are the same.
255 doesn't look like a 'natural' number, it is typical a number
which comes out of the head of a 'computer' oriented mind.
(80, 100, 200, 400 are more natural). But if the data comes
from another 'computer' system 255 could be the right number.
Null,
Never prefered and should be avoided. But for only importing
data (on the way to another table) and for text holding fields
on which no selection and no join is done a 'logical' choice.
If the text is human generated and a 'non' text is one of
the posibilities, I think that null is ok.
(When indexed, joined, used in a where clause one should
avoid a null).
Also concatenating with <null> strings should be avoided,
because this is handled differently in different databases.
(One could choose as wel for an empty string instead of
a null, this is supported in SQL-server, but be aware there
are RDBMSses which consider an empty string equal to
a Null string.)
I would not know of a default in a text string which is a
good replacement for a null string.
(Considering other factors as usage by programs, utilities
and other databases. Empty string is not supported
in all databases.
Using a single character as a default might conflict
with a 'real' single character even if it is a space.)
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
vvvvvvvvvv
Does anybody know of a good 'default' for character strings
instead of using a null ?
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ben brugman
> As I am absolutely positive that the majority of these
> columns will not be storing this amount of data in each
> of the columns, as some of them are code columns, types,
> and one description field.
> After asking him to review all of the columns and the
> maximum estimated data stored in each one, he said that
> it shouldn't make any difference because A) they're
> varchar and B) they're defined as null.
> What can I say to him to make him do the needful?
> Thanks,
> Marcus
