Can column order affect performace

[talenx]

Hi all, sorry but I think I have to ask a dumb question. I haven't seen any documentation on SQL concerning this issue, yet I have seen some on Oracle.
Does column order storage affect performance?
Example:
If I have a two tables comprised of the same columns (the only difference is the column ordering.)

TABLE1 table has all columns in alphabetical order base on column name
TABLE2 table has the columns ordered by data type only.

My test have been inconclusive in determining if one has a better performance set then the other or if there is another combination of column ordering.
Any thoughts
Thanks
TalenX

 

[mrdenny]

Nope. The data for the record is stored within the same physical page on the disk. No matter what order the columns are in the data is stored in the same page. When the page is read the entire page is loaded into memory, which means that the entire record is loaded into memory.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[johndweidauer]

I thought if you say ordered on text as opposed to a number format, that it would reduce the performance on the server?

 

[talenx]

Fantastic!!
Basically I am creating a some what template for our users (we create numerous db per client and need a standard.) I was just wondering if I needed to take column order storage in to consideration.

Thanks Talenx

 

[Catadmin]

TalenX,

The only thing you need to take into consideration on storage is the actual bytes used by each datatype on each column in the row and the # of rows per table plus any possible indices. Other than that, you should be fine.

John, unless I'm mistaken, you're talking about sorting order in a query, not the actual physical order of the columns in the table. These are two seperate things. I believe the later is what TalenX is asking about.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[talenx]

Catadmin, That sounds perfect!

Once again thank you for ALL of your insight!

*** CATADMIN ***

Thanks Again
TalenX

 

[talenx]

Oh... Geez sorry I almost forgot...

Thank you MrDenny, it's MVP's like you and CatAdmin that make us lesser mortals better.

Thanks
TalenX

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

Hey, we gotta stick together. We're all new at this stuff at one point or another. Just ask MRDenny & SQLBill how many times they've bailed me out or pointed out that I've made a mistaken assumption. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"