SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Canada
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Column arrangement and datatypes

    This has bugged me for some time. Does the datatype column arrangement affect speed or productivity?

    Is there a certain rule to arranging the different datatypes? E.g. intergers first, then dates, then fixed text, then varchars, etc...?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, no real (noticeable) difference, although some people think that you should put all non-null fixed-length columns first, to save a few nanoseconds calculating a column's offset position

    see, if there are null values, they aren't really there (except for the null bit), so they act like an empty varchar, which means that each column position after the fixed portion must be calculated with offsets

    but since this all happens in memory...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Canada
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great, thanks for clearing this up. I was thinking in terms of fixed vs. variable length columns as well.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    most modern DB engines read data from a row in disk order, not the order requested in the field list of a SELECT statement. the columns are either then loaded in to memory in the correct order, or output in the correct order.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    longneck, i know i said "put all non-null fixed-length columns first, but i meant in the CREATE TABLE statement, not the SELECT statement

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i should have started my previous post with "in addition..." and ended with "so the order in a select statement has a negligible effect, too."


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •