How to Convert From Int to Smallint

In a particularly large database, you can save substantial space by converting the integers to the much smaller smallints. However, you must be certain that all the existing and potential data will fit within -32768 and 32767 provided by the smallint data type.

Instructions

1. Open your database client. If you use MySQL, for example, you can do this by opening the terminal and typing:

   mysql

2. Type the following to create an example table:

   CREATE TABLE testT (anInt Integer);

3. Type the following to insert some data into the table:

   INSERT INTO testT (anInt) values (123);

   INSERT INTO testT (anInt) values (432);

   INSERT INTO testT (anInt) values (5344);

   INSERT INTO testT (anInt) values (4324324324);

4. Create the conversion function:

   CREATE FUNCTION convToSmallInt (n INTEGER) RETURNS SMALLINT RETURN n;

5. Call the function:

   SELECT d(anInt) as small from testT;

   Notice in the output that the first three values from the database are faithfully represented, but the last value is outside of the range of SmallInt, so it is truncated down to 32767.

References

• MySQL : Create Function