PG,
Disclaimer - most of this is from Sybase and while it is probably applicable because Sybase and MS Sql Server have the same roots, it is not necessarily accurate.
> Table design should always have nullable columns towards the end.
I hadn't heard that before - I had heard that varchar should be at the end. I believe that the reason was so that if you are selecting columns ( or selecting based on columns) that are not varchar, they could be read more quickly. I believe that you retrieve a page of data, and if you know the layout of the records and know that what you want is a fixed length, then you do not need to read the up to the column you want to look at - for example, if you have:
table mytable ( a char(10), b char(10), c char(10), d int)
and you want to look at d then the DB engine can just look at bytes 31 - 35.
On the other hand if you have:
table mytable ( a varchar(10), b varchar(10), c varchar(10), d int)
and you want to look at d then the DB engine looks at byte 1 and 2 to see how long a is, it offsets a to look to see how long b is, it offsets b to look at c to see how long c is and if offsets d to look at d. - A lot more machine instructions. When the machine was 50 Mhz or less, it made more of a difference than now when the machine is 2+ Ghz.
I would expect that the suggestion about nullable columns towards the end of the table is related.
> Nullable Char columns occupy higher number of bytes than Non Nullable
> char column. This is basically because of some overhead involved in Nullable
> columns.
The first byte tells the DB if this column is null or not. There is not other way to represent the data if it is nullable because null is not ascii 0 - it is undefined. So - if you had an int column, and it was nullable, there is nothing you could put in it which would represent null and not a value - hence an additional byte to indicate if it is null or not. (Because MS allows varchar of up to 8000 bytes, it is possible that they use 2 bytes to show if null but I don't know.) Similarly, if you had a char column, even an empty string is not the same as a null string.
Hope this helps.
. . . Tom
Tom Zeblisky
Reuters
-----------------------------------------------------------------
Visit our Internet site at http://www.reuters.com
Get closer to the financial markets with Reuters Messaging - for more
information and to register, visit http://www.reuters.com/messaging
Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
MS Sql Server LazyDBA home page