Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2007
    Posts
    6

    Question Unanswered: Data type suggestion (easy one hopefully)

    I'm creating a database which will house millions of rows of data. The problem i'm having is i have a field which is to store a number of 35 characters. Obviously INT and BIG INT are too small. I am reluctant to use VARCHAR( 35) as I want to search the database via a query number BETWEEN X and Y and am concerned about speed of the query if i use a VARCHAR.

    Any suggestions?


    Thanks in advance

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I reckon decimal might be worth a look.
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2007
    Posts
    6
    The numeric values will be whole numbers only

  4. #4
    Join Date
    Aug 2007
    Posts
    6
    Actually it seems to output ok with test data

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Would Numeric(35,0) do the trick then?
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by nooch32
    I am reluctant to use VARCHAR( 35) as I want to search the database via a query number BETWEEN X and Y and am concerned about speed of the query if i use a VARCHAR.
    right-justify your numbers in a CHAR(35) column with leading zeroes (or spaces if you insist)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2007
    Posts
    6
    not liking the idea of corrupting the data with leading zeros. Also, with them being numbers and i did a query to get a range of numbers, wouldn't they get converted if they were stored as CHARs? Got to remember this is a huge database i'm working on, so speed is essential.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    leading zeroes do not actually corrupt numbers

    by suggesting that, you are actually admitting that they really are strings, not numbers

    so use leading spaces

    and converted to what? no, they wouldn't get converted, they'd be CHAR strings
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2007
    Posts
    6
    Although i like the idea of DECIMAL, i've just realised that there won't be any calculations as such on these numbers. Also, just found out the number needs a leading 0 (yep, phone numbers). Also PHP won't handle the number of 35 characters very well, ie we'd have to convert it over to a string. Think it might just be easier to store as VARCHAR(35) as before

  10. #10
    Join Date
    Aug 2007
    Posts
    6
    r937, think you've got it, essentially i should look at them as strings

Posting Permissions

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