SQL Server Performance

decimal

Discussion in 'General Developer Questions' started by bfarr23, Sep 15, 2003.

  1. bfarr23 New Member

    I am inserting 12.10 in a decimal data type that is 18 precision and 4 scale.

    SQL Server puts in 12.1000

    I only want the original only stored in the db - 12.10

    What can I do?
    (i need 18,4)

  2. Luis Martin Moderator

    How is defined you input variable?

    Luis Martin
  3. gaurav_bindlish New Member

    Reduce the precision of column to 2

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  4. bfarr23 New Member

    i need 18 precision, 4 scale.
  5. bfarr23 New Member

    12.10 and 12.1 are different for my data. 12 feet 10 inches, 12 feet 1 inch.

    If I insert 12.1, I want it to stay as 12.1. If I insert 12.10, I want it to stay as 12.10.
  6. Twan New Member

    <br />you'd need to store it as varchar...<br /><br />feet.inches is not a decimal number, so storing it as such doesn't quite work <img src='/community/emoticons/emotion-5.gif' alt=';-)' /> If you did want it to work then either store 12'1" as 12.01 or store the whole thing in inches ie 145<br /><br />Cheers<br />Twan
  7. bambola New Member

    Or maybe as 2 ints

    Bambola.
  8. gaurav_bindlish New Member

    You can store the entire data in inches as well....

    This idea came from the fact that date is also stored something like this internally....

    HTH.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  9. bfarr23 New Member

    what would be best in terms of performance?
    2 ints or varchar?

    there will have to be some conversion here for the web display. Show feet.inches for some, total area for others, etc.

    (real estate system)
  10. gaurav_bindlish New Member

    bfarr23, I understand this is a personal decision, but is there a reason for not storing the data in inches as int and then converting it into feets?

    I would not recommend storing as varchar as this will need explicit calculations while calculating area etc. as you mentioned.

    Also character comparison is slower than integer comparison.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  11. bambola New Member

    Int ( 2 or maybe better one in inches, like Twan suggested) might be a better choice if you need or ever need to perform some calculations.

    Bambola.
  12. bfarr23 New Member

    this is the problem.
    The data orginates from numerours sources that send varying measure unit formats(all inches, feet.inches, etc). This data then gets loaded by a VB program into our database. I need to keep performacne up and be able to display in the measure unit that the data was originally sent in.

    So if I convert all to inches, I still need to display in the measure unit that data orignated in.

    I am thinking that the best thing is to convert to one common format that will be displayed most frequently. Thus less conversions.

    Still have to store the original meaasure unit though.

    Tricky situation here.
  13. gaurav_bindlish New Member

    Same reply as above... Use a standard to identify the format of the data when it was recieved and store it in databalse along with data. When displaying, show the data based on the format stored in database.,

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  14. bambola New Member

    You could add a field to contain the unit, then use a view to calculate it or maybe a calculated column with an index on it.

    Bambola.
  15. Twan New Member



    If you need to show the way it was submitted and calculate area then I'd suggest:

    store it as varchar, so that you can display it back to the user
    store it as integer converted to inches for your calculations (144 square inches being 1 square foot)

    Cheers
    Twan

Share This Page