SQL Server Performance

Index on NUMERIC(18, 0) col

Discussion in 'General Developer Questions' started by amitm79, Jul 19, 2004.

  1. amitm79 New Member

    Hi All,
    Has anybody been through a practical scenario wherein a table's primary key has been changed to NUMERIC(18, 0) from an INT? Basically in my case due to more data, i need to change my primary key to hold numbers upto 10 bytes rather then 4. I found out that the only way to go about is to use NUMERIC(18,0) (correct me if i am wrong). Now before actually implementing it i wanted to know its impact on the index speed. Any article/suggestion/link/experience is welcome.

  2. satya Moderator

    I don't think this will have massive difference in the space after creating index on this changed datatype and if you do not have issues of disk space then you can easily deploy it during less hours traffic on the database in order to complete the operation without any interruption.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. derrickleggett New Member

    You're telling me you can't use bigint??????

    You need a number larger than this? 9,223,372,036,854,775,807



    When life gives you a lemon, fire the DBA.
  4. FrankKalis Moderator

    AFAIK, DECIMAL(18,0) will not store numbers up to 10 bytes. Maximum will be 9 bytes.<br />However, the larger the index, the less index rows fit on one index page, requiring more pages, thus requiring more reads. How much this will affect performance one can only guess as it depends on too many factors. You should do try this within a test environment first. This one might also be interesting, although it's in German I'm sure you'll get the meaning<a target="_blank" href=http://www.insidesql.de/content/view/223/>http://www.insidesql.de/content/view/223/</a><br />Basically it says, when you know in advance that this table is going to hold that many rows, how about creating it with a negative starting value for the IDENTITY property. ...don't take the link too serious. It's more of a fun contribution.<br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  5. amitm79 New Member

    Hi Derrick,
    You are right, i need a number bigger then 9,223,372,036,854,775,807. I can have this because i am going to store IP, IPX and MAC addresses as numbers in this column. I am not yet talking about IPV6 which can go even bigger.

  6. FrankKalis Moderator

    Aha, we're getting closer to the point. I'd use binary(16) to store this kind of data and do the formatting at the client.

  7. amitm79 New Member

    Hi Frank,
    I think an index on binary is not recommended. Can anybody please confirm it according to there knowledge and experience?

  8. FrankKalis Moderator

  9. amitm79 New Member

    Hi Frank,
    Do you think it is a good idea to have an index on binary(16) or probably varbinary(16)? I mean i want to know your exp with it, if you have used it. Others, could you please give your inputs on it? I need to decide the design of the table.

  10. FrankKalis Moderator

  11. amitm79 New Member

    Hi Frank,
    I really appreciate you efforts that you went back and found the query posted some time back. yeah, the problem is more or less related. I will evaluate this option and find the results. But considering that i am working on some legacy design and people are not willing to accept more changes i might have to find a workaround.

  12. FrankKalis Moderator

    No problem, there are not soo many people asking how to store IPv6, so I remembered this one ...after a while. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] <br />For an inhouse app I actually chose binary(16) and don't have any problems with it. To be honest, I haven't thought about storing in a numeric column, but that wouldn't be an alternative for me. My only alternative was a <b>varchar</b> or <b>char</b>, for that matter. My website stores such data in varchar. Unfortunately MySQL does not seem to support binary.<br />I haven't done extensive testing how both compare to each other, but I guess binary will perform better than char. But after all, it's not all about performance and binary is more intuitive to me.<br />Why don't you build a small test scenario with two tables having the primary key of each alternative in question, fill them with some sample data and compare the results?<br /><br /><br /><br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  13. amitm79 New Member

    Hi Frank,
    I am in the process of testing it for varbinary(17) (IP, IPX, MAC, IPV6 prepended with type). Will let you know the results. Do you have any good links about performance, indexing and other implications of using varbinary?

  14. FrankKalis Moderator

Share This Page