Professional Web Applications Themes

What's the right size datatype? - Microsoft SQL / MS SQL Server

Often I'm confronted with a choice as to the size of the datatype of a given column when designing a database. For the sake of discussion let's say I have a status code which may take on any number of distinct numeric values. Perhaps there's a relatively small number of distinct values, so I could choose a tinyint, smallint, or integer. Which choice is the "best"? On the one hand, assuming I'll never get near a couple hundred values, then tinyint would seem to make the most sense - it uses less space. But space is cheap these days, and ...

  1. #1

    Default What's the right size datatype?

    Often I'm confronted with a choice as to the size of the datatype of a given column
    when designing a database. For the sake of discussion let's say I have a status code
    which may take on any number of distinct numeric values. Perhaps there's a
    relatively small number of distinct values, so I could choose a tinyint, smallint, or
    integer.

    Which choice is the "best"?

    On the one hand, assuming I'll never get near a couple hundred values, then tinyint
    would seem to make the most sense - it uses less space. But space is cheap these
    days, and I probably couldn't measure the cost differential between the space used
    for one type versus another on anything but the most massive databases.

    On the other hand. an integer is the "native" datatype for the processors I'm using,
    so that might argue for using that size, because it'll be "faster". But with the
    speed of processors these days, I'd guess the speed differential between 8, 16, and
    32 bit datatypes is miniscule.

    So how do you decide?
    Jeff Guest

  2. #2

    Default Re: What's the right size datatype?

    I always use the smallest datatype necessary. Seems intuitive to have the
    least amount of waste... not only will this save disk space, it will also
    make performance of any index(es) on such columns faster. I don't buy that
    an application will perform any faster using a 16-bit datatype than an 8-bit
    datatype, in fact you'd probably (or I would, anyway, in .NET) probably just
    map a SQL Server TINYINT to an INT16 in a .NET application. So, nothing
    lost, nothing gained really, on the app side... but big difference (1 byte
    vs. 4) in the database and any indexes...




    "Jeff Mason" <net> wrote in message
    news:com... 
    given column 
    status code 

    smallint, or 
    then tinyint 
    cheap these 
    space used 
    I'm using, 
    with the 
    8, 16, and 


    Aaron Guest

  3. Moderated Post

    Default Re: What's the right size datatype?

    Removed by Administrator
    Joe Guest
    Moderated Post

  4. #4

    Default Re: What's the right size datatype?

    > Look at the newbies who constantly use NVARCHAR(n) wher (n) is (a) some 

    This is not completely the fault of a newbie... this is often the result of
    porting from Access to SQL Server (the upsizing wizard automatically creates
    nvarchar or ntext for all char-based columns coming from Access). The 50,
    of course, comes from the default size of a "text" column in Access.

    I doubt what you are ranting about here is likely explained as a "newbie"
    making a conscious decision to use nvarchar(50) for a zip code...


    Aaron Guest

  5. #5

    Default Re: What's the right size datatype?

    >> This is not completely the fault of a newbie... this is often the
    result of porting from Access to SQL Server (the upsizing wizard
    automatically creates nvarchar or ntext for all char-based columns
    coming from Access). The 50,
    of course, comes from the default size of a "text" column in Access. <<

    "A poor workman blames his tools." That wizard is a real stinker, but
    you are supposed to check the port, not just assume that "magic
    happened" instead.
     [/ref]
    "newbie" making a conscious decision to use nvarchar(50) for a zip
    code... <<

    It is more like "semi-conscious" decision. They "cut and paste" code
    from the old tables and never look at what was copied. Then they wonder
    why their NVARCHAR(50) zip code cannot work so well with "zip_code
    CHAR(5) NOT NULL CHECK (zipcode LIKE '[0-9][0-9][0-9][0-9][0-9]')"
    declared columns when they try to join or move data.

    <<insert more ranting here >>

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  6. #6

    Default Re: What's the right size datatype?

    > "A poor workman blames his tools." That wizard is a real stinker, but 

    Well, I think the tools would help newbies along if they *didn't* put in
    defaults for whatever length someone way back when thought a "TEXT" or
    nvarchar column should have. If there is no value there, and the wizard
    prompts you for one, newbie and Celko alike will have to think about . Now,
    they won't necessarily "think alike," or for the same amount of time, but
    they will have to put some thought into it.
     

    Not really a cut and paste either; they just say, "send this Access database
    to SQL Server." Done. They don't look at any "code"...
     

    Heh. Find me a newbie who has an nvarchar(50) for a zip code, *and* is
    actually trying, purposefully, to implement a constraint like that. :-)


    Aaron Guest

  7. #7

    Default Re: What's the right size datatype?

    Hi Joe,
    This table must violate every rule and standard you've been trying to beat
    into newbies for the last "fill in the blank" years.
    :-)

    "Joe Celko" <edu> wrote in message
    news:phx.gbl... [/ref]
    > actually trying, purposefully, to implement a constraint like that. :-)
    > <<
    >
    >
    > Well, I can do half of that ...
    >
    > http://www.developersdex.com/sql/newmessage.asp?p=581&ID=%3COF91qDkYDHA%
    > 2E1280%40tk2msftngp13%2Ephx%2Egbl%3E&r=3307281
    >
    > --CELKO--
    > ===========================
    > Please post DDL, so that people do not have to guess what the keys,
    > constraints, Declarative Referential Integrity, datatypes, etc. in your
    > schema are.
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it![/ref]


    raydan Guest

Similar Threads

  1. I think it's a datatype issue
    By Fox in forum ASP Database
    Replies: 11
    Last Post: January 7th, 09:01 PM
  2. Specifying Numeric datatype in a SP
    By Craig in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 23rd, 01:34 PM
  3. Bit datatype
    By quixote in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 21st, 06:27 PM
  4. how to use userdefined datatype in UDF?
    By Rich in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 15th, 07:24 AM
  5. Check datatype
    By Andrew Durstewitz in forum ASP
    Replies: 2
    Last Post: June 30th, 03:46 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139