CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2008
Saleh
Saleh

Reputation: 3032

Sql Server Performance And Order Of Fields

does the order of fields creation in a table effect on the performance of commands on the table? If the answer is yes, can anyone discuss it?

For example i have create a table like this

create table Software(int id,alpha datetime,beta datetime,title nvarchar(100),stable datetime,description nvarchar(200) )

if i change it to

create table Software(int id,alpha datetime,beta datetime,stable datetime,description nvarchar(200),title nvarchar(100) )

Is there any performance effect ?

Is it clear?

Upvotes: 6

Views: 238

Answers (3)

gbn
gbn

Reputation: 432271

The field order makes no difference whatsoever (if the fields are always the same of course)

The on-disk structure will remain the same pretty much regardless. Simply:

  • header
  • fixed length columns
  • null bitmap
  • variable length columns

All you're doing above is rearranging some columns inside the "fixed length" and "variable length" sections. However, the same processing is required to retrieve them no matter which order they are in.

See Paul Randal's article

Upvotes: 4

Phil Helmer
Phil Helmer

Reputation: 1240

The field order can have a very subtle effect on performance and how often maintenance operations will have to take place. In the grand scheme of things, it would be a significantly better use of your time to:

  • Build/Test indexes
  • Restructure queries
  • Partition tables (horizontally & vertically)
  • Alter the filegroup architecture

That is not an exhaustive list.

You didn't mention if you've already tuned any of those things and are still needing to squeeze some extra ms out of the process.

Upvotes: -3

Henry
Henry

Reputation: 6620

No. This will not affect performance.

Upvotes: 3

Related Questions