Menu
If I know that the value of a column should always be in teh range allowed by the smallint data type, from a data integrity maintenance point of view, it would seem to behoove me to store the data in a smallint column rather than an int column.
However I am wondering whether there are any performance hits that may be paid by using less bytes?
556
If you know that some columns in a table that will have millions of row or even an little table that will FK'ed to multi-million-row that doesn't need an 4 bytes integer to store their data, but an 2 byte would suffice - use SMALLINT. If values in the range 0-255 is enough, TINYINT.
The int data type is the primary integer data type in SQL Server. The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type. bigint fits between smallmoney and int in the data type precedence chart.
The SMALLINT data type stores small whole numbers that range from –32,767 to 32,767. The maximum negative number, –32,768, is a reserved value and cannot be used. The SMALLINT value is stored as a signed binary integer.
Smaller types = less storage on disk, which leads to more efficient indexes. However, those performance gains will be minimal unless dealing with a large volume of data; furthermore, in order to avoid implicit conversions (which may offset any performance gains you see), you'll need to be sure that any time you reference that column that you use the correct type (including comparisons to parameters, etc).
Probably a wash either way.
106
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
