Column Placement in Table Definitions? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Column Placement in Table Definitions?

I believe in older versions of SQL Server (and DB2), the order of frequently updated columns in a table’s definition impacted performance. For best performance the most frequently updated columns should be at the beginning of the record. Does anyone know if this holds true for SQL Server 2000? Dave
Why do you want to change the column order?
It’s a common misconception that column order affects performance. But keeping columns that are referenced in the same query near each other doesn’t produce faster results. In fact, the visible order of the columns in the table doesn’t reflect the physical order of the columns stored in the database. You won’t see any performance gain by changing the order of the columns in your table. You will, however, see a performance difference by changing the order of columns in an index, but the difference might not be for the better.
Pay attention to the order of columns when creating multicolumn indexes. The columns with the less unique data should be listed first in the index to ensure that the data is further sorted as it moves across the index. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I know with DB2 there are a couple of white papers that address column order as it pertains to frequently updated columns. In older versions of SQL Server (4.2 and 6.0) I thought column order was stressed due to the physical location of the data on disk. You mentioned the visible order of columns in a table doesn’t reflect the physical order of columns in a database. Are you referring to the order in syscolumns? Dave
Yes my reply intended to the order in syscolumns.
And still the performance is affected on the basis of physical order of columns, when you’re talking about 4.2 & 6.0/5 SQL versions. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>