Field Type Order in Tables
-
Does the order of field types in a table make a difference? I heard that you should always put bit and blob fields at the end of the table, but what about other field types? Is there any advantages to certain orders?
-
There is a *flaw* in ADO, in that you should place binary columns at the last position in a SELECT statement. Can't find the relevant Knowledgebase article right now. Apart from this there is no advantage in a specific ordering. At least I hope so
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for the reply Frank. I've been searching high and low for an answer to this, but haven't been able to find anything. I know that there is a difference in Oracle pertaining to column type placement in the table, but I can't find any statements by Microsoft or anyone else pertaining to this in SQL Server 2000.
-
I don't know Oracle, but if I haven't totally misunderstood the theoretical approach behind DBMS there shouldn't be a difference in positioning columns in a table. Also ordering of rows has less or no meaning. I only have heard that Oracle treats things sometimes differently.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Just to add a small note with regards to ADO.NET.
I am retrieving two binary fields into a dataset by using a stored procedure - so there is no inline ADO.NET SQL statement - and in the fields are respectively the first and the last in the dataset. It's working fine so does that mean the problem no longer occurs in ADO.NET? Or does it still occur if you use in-line SQL queries?
Mauro
-
Here is the article I had in mind
http://support.microsoft.com:80/support/kb/articles/Q175/2/39.ASP&NoWebContent=1
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hello Frank,
sorry but the link does not seem to be working. Is it still live on the MS site?
Mauro
-
Strange, it is working for me.
This one I bet will be splitted by forum software. You'll have to manually copy and paste
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
With fixed length types like char, int, and so on it makes no difference as they are physically stored first in the record on the file. For non-fixed such as varchar then place all your non-null columns first and you least likely to be null to most likely next. The reason for this is non-fixed types are stored at the end of the record on the file and have a 2 byte overhead to note there offset to first character in the record. But if they are null the offset will be there if any columns are after it. However, if there are non after a null column then the offset isn't recorded.
So if you have 10 varchar columns and all are NULL except the last one in order of table DDL the 9 offsets for the NULL columns are recorded and a lose of 18 bytes on the page occurrs. If you reverse and put the last at the first and all the rest is null then only the offset for the non-null column is recorded.
By doing this you may save space on the filesystem.
Also somthing that can be a space saving fact is this.
If you have a column that will stored data that can be either one of three lengths but they only differ by a factor of 3 range (such as 5,6, and 7 length or 13,14,15 length) don't use varchar.
The reason is the extra 2 bytes for varchar offsets the savings.
For example if you have a filed that can be 5 or 6 or 7 characters long use char(7), the reason is varchar(7) with a value length of 5 is still 5 + 2 or 7, 6 would be 8 and 7 = 9.
Right off that is all I can think of.
And as for text I was always told that was in your output (SELECT field, field, text FROm tbl) not your storage or table DDL, the pointer for text is fixed in 7 and can be variable in 2000 if you are using the "text in row" option so weigh that as well.
Hoep this helps a bit.
-
Yup, found the text in Inside SQL Server. So far for the storage engine.
And then there is the relational engine and both communicate with each other.
Btw, Antares, did you notice any issues apart from wasting space?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Found the answer to my own question in the same chapter....
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
Yup, found the text in Inside SQL Server. So far for the storage engine.And then there is the relational engine and both communicate with each other.
Btw, Antares, did you notice any issues apart from wasting space?
Frank
No nothing more than space waste.
I thought this was interesting on the KB Article thou
quote:
This behavior is by design. However, it does not occur when using Mdac 2.1sp2 or later with the 3.7 driver or later for SQL Server.So from that then the position of the columns should no longer matter unless you have not kept your server current on patching or mdac.
-
thank you for that reply. It is something solid to go on!
quote:
With fixed length types like char, int, and so on it makes no difference as they are physically stored first in the record on the file. For non-fixed such as varchar then place all your non-null columns first and you least likely to be null to most likely next. The reason for this is non-fixed types are stored at the end of the record on the file and have a 2 byte overhead to note there offset to first character in the record. But if they are null the offset will be there if any columns are after it. However, if there are non after a null column then the offset isn't recorded.So if you have 10 varchar columns and all are NULL except the last one in order of table DDL the 9 offsets for the NULL columns are recorded and a lose of 18 bytes on the page occurrs. If you reverse and put the last at the first and all the rest is null then only the offset for the non-null column is recorded.
By doing this you may save space on the filesystem.
Also somthing that can be a space saving fact is this.
If you have a column that will stored data that can be either one of three lengths but they only differ by a factor of 3 range (such as 5,6, and 7 length or 13,14,15 length) don't use varchar.
The reason is the extra 2 bytes for varchar offsets the savings.
For example if you have a filed that can be 5 or 6 or 7 characters long use char(7), the reason is varchar(7) with a value length of 5 is still 5 + 2 or 7, 6 would be 8 and 7 = 9.
Right off that is all I can think of.
And as for text I was always told that was in your output (SELECT field, field, text FROm tbl) not your storage or table DDL, the pointer for text is fixed in 7 and can be variable in 2000 if you are using the "text in row" option so weigh that as well.
Hoep this helps a bit.
-
quote:
No nothing more than space waste.I thought this was interesting on the KB Article thou
as always, it is!
Another explanation why my estimations of row size are not precisely enough.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply