When creating new table does column order matter if different data types?

Question

When creating new table does column order matter if different data types?

sql_er

SSCarpal Tunnel

Points: 4135
More actions
August 21, 2010 at 7:47 pm

#98891

Hi,
I work with MSSQL 2000. When creating tables I never paid attention to the order and type of columns. Last week a co-worker mentioned that it is best to place fixed length data types first, followed by variable length columns
For example, if I have a table with have a few integer/date columns and a few varchar, he suggested to place varchar last.
Is it really better this way? If so, could someone explain in detail why?
Thank you!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 1

sql_er (8/21/2010)
Hi,
I work with MSSQL 2000. When creating tables I never paid attention to the order and type of columns. Last week a co-worker mentioned that it is best to place fixed length data types first, followed by variable length columns
For example, if I have a table with have a few integer/date columns and a few varchar, he suggested to place varchar last.
Is it really better this way? If so, could someone explain in detail why?
Thank you!

Not as far as I am aware - I believe that was true in the 6.0/6.5 days, but hasn't been that way since then.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

sql_er (8/21/2010)
Last week a co-worker mentioned that it is best to place fixed length data types first, followed by variable length columns

No, not at all. Order of columns in the table is utterly irrelevant. The way SQL stores them on the page is fixed data types first, then variable lengths, but that's done no matter what the 'order' they are defined in looks like.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 3

That is what I assumed an intelligent system would do - thanks for the clarification!

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 4

GilaMonster (8/22/2010)
sql_er (8/21/2010)
Last week a co-worker mentioned that it is best to place fixed length data types first, followed by variable length columns
No, not at all. Order of columns in the table is utterly irrelevant. The way SQL stores them on the page is fixed data types first, then variable lengths, but that's done no matter what the 'order' they are defined in looks like.

There's one I didn't know. Have a got a Paul or MS link for that? I'd like to read up on it. Thanks, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Jeff Moden (8/22/2010)
There's one I didn't know. Have a got a Paul or MS link for that?

Got a Paul link (or three).

http://sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-record.aspx

http://sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx

http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-27-How-does-the-storage-engine-find-variable-length-columns.aspx

Everything in his category 'On disk structures' is worth a read or two.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 6

GilaMonster (8/23/2010)
Jeff Moden (8/22/2010)
There's one I didn't know. Have a got a Paul or MS link for that?
Got a Paul link (or three).
http://sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-record.aspx
http://sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx
http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-27-How-does-the-storage-engine-find-variable-length-columns.aspx
Everything in his category 'On disk structures' is worth a read or two.

Outstanding. Thanks, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

Here's why I specifically wanted a "Paul" reference... I knew he'd get into...

Using DBCC IND and DBCC PAGE to examine a row in detail

It's too bad the comments are closed on those articles. I'd love to post a "thanks". Guess I'll have to do it in-person at PASS this year.

Thanks again, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 8

Rick.Cornell.SME

SSCrazy Eights

Points: 8597

August 23, 2010 at 7:51 am

#1210447

Excellent, thanks for the links as well!!!