July 1, 2010 at 9:05 am
I'm hoping that someone versed in row layout, and who'se familiar with SQL Server internals can help me find information not easily indexed in books on the internals for this one.
I'm in an environment where people love varchar by default and feel there isn't really any tradeoff to using them vs. char. Before just going along with that, I want to research to see if there are any tradeoffs.
For this topic, I'm wondering if anyone knows whether the ordinal position of the column has any impact on storage space and/or space clean-up.
In other words, if a table has four columns - two char and two varchar, is there any difference in space and clean-up if the ordinal positions are char, char, varchar, varchar vs. varchar, varchar, char, char?
Is there any implication in the table itself or indexes on the table?
In a previous post, the comment was made:
When you used ALTER TABLE to refactor TableB, the char column became varchar, which is stored in a different portion of the row (the varying length part) - so the space previously used for this column is now unused. from: http://www.sqlservercentral.com/Forums/Topic731965-360-1.aspx
That comment seems to indicate that there are two parts to a physical row layout - one part for fixed and one part for variable storage. If the logical layout including ordinal position does not affect or change the physical layout, then I suppose the answer to my question is that there is no difference between the two layouts I compared above - at least for the table itself.
There's also the question of whether the logical layout (ordinal position) affects storage/clean-up for indexes. It seems to me that logical layout does affect indexes.
What think ye? Thanks, Bill
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
July 1, 2010 at 9:10 am
No matter what order you specify the columns in the table, in the page the fixed width columns are stored before the variable-length columns.
Try Paul Randal's blog for info. Do you have SQL 2008 Internals? Likely in there too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2010 at 9:30 am
I don't see it in BOL, but it should be in Inside SQL Server (Delaney) and show that all fixed length data comes first, then offsets to var columns on the page.
July 1, 2010 at 9:37 am
Thanks Gail and Steve! I think that covers the table part of the equation.
Per your references, the physical layout of a row is always fixed storage first and then variable storage. So ordinal position or logical layout of columns makes no difference.
Any thoughts on how the ordinal position of char vs varchar might affect indexes? Or, does the physical layout of the indexes work the same way as a table...?
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
July 1, 2010 at 9:55 am
Index order is driven by sargability. You wouldn't move a column up in an index because of the data type. You place them based on what is most likely to be useful in queries.
July 1, 2010 at 10:04 am
Bill Nicolich (7/1/2010)
Any thoughts on how the ordinal position of char vs varchar might affect indexes?
Ordinal position in the index or in the base table?
For ordinal position in the index http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/ and http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
For ordinal position in the table, see previous post on this thread
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply