February 28, 2005 at 1:59 am
I read somewhere that there is a benefit in putting fields in a specific order within a table based on whether they were fixed length and/or nullable.
I know fixed length comes before variable length but I am not sure whether not nullable takes precedence over fixed/variable length
For example is the order
Fixed length not nullable
Fixed length nullable
Variable length not nullable
Variable length nullable
OR
Fixed length not nullable
Variable length not nullable
Fixed length nullable
Variable length nullable
February 28, 2005 at 4:37 am
Fixed length --not nullable
--Fixed length nullable
Variable length not nullable
Variable length nullable
IIRC the storage engine will rearrange this for you anyway if you didn't specify it this way. At a cost of some extra bytes per row. The NULLability is tracked in the NULL bitmap.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 1, 2005 at 1:27 am
Hi there,
about the benefits mentioned, I don't know how M$$QL treats this issue, but on ORACLE you can save some space if you order the columns as Frank wrote. For example, if all columns at the bottom of the physical table structure are NULL in a row, ORACLE does not use any space for them in the physical block. Everything after the last non-null column value is assumed to be NULL.
Regards,
Goce.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply