August 22, 2013 at 10:40 am
Hello to all.
Question:
Consider the physical placement of a row of a table on disk: Is column ORDINAL_POSITION the same as column physical position?
My Reason for Asking:
My boss wants me to add a new column to a table "before" the "last" column which is TIMESTAMP.
ALTER TABLE ADD (column name) will add the column "after" the "last" column.
Rebuilding the table from scratch and copying the old data into the new table does allow me to control ORDINAL_POSITION.
But what really happens on the physical disk? Is ORDINAL_POSITION honored as physical placement? If not, then the only value I can see, for rebuilding the table in this way, is for the pretty look in a GUI tool such as SSMS (SQL Server Management Studio).
Is this true?
PS: I understand that in Set Theory and RDBMS Theory column order has no meaning. My question refers to Microsoft SQL Server's data base engine behaviour.
August 22, 2013 at 11:58 am
Ordinal position is the position within the create table statement and in the metadata, hence the default position when doing a select *. That's it. It has no bearing on the position of the column in the page on disk.
Your boss's request is a common one, but pointless. There's no reason to have a column in a particular position in the table. If you want it in a position in the select, specify the column names and put it where you want.
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
August 23, 2013 at 5:14 am
Thank you Gail. I appreciate your expertise and extensive knowledge.
- from Tom at US Bank
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply