Menu
What data type should i use for a SQL column to store Product version eg.
Version
0.1
0.1.1
0.2
1.1
1.1.647
2.0
.....
In query i should be able to sort them based on version number and i want an optimal query to find highest number.
Thanks
552
There is no generally-accepted place to store a version number in a database schema. Databases don't have version numbers by convention. There is no standard way of putting in this or any other information about an application into the database.
SQL data types can be broadly divided into following categories. Numeric data types such as int, tinyint, bigint, float, real, etc. Date and Time data types such as Date, Time, Datetime, etc. Character and String data types such as char, varchar, text, etc.
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.
Numbers in SQL can be either exact (NUMERIC, DECIMAL, INTEGER, BIGINT, and SMALL INT) or approximate (DOUBLE PRECISION, FLOAT, and REAL). The integer data type is used to store whole numbers (numbers without the decimal point or simply nondecimal numbers).
A good solution would be to use an integer building the value to store like so:
MAJOR * 10000 + MINOR * 100 + Revision
Assuming each one can range from 0..99. If you want to go 0..999 use
MAJOR * 1000000 + MINOR * 1000 + Revision
This will sort properly, will query easily, is compact (1 int column), is easily decomposed and can even be decomposed visually.
69
I would consider storing each part of the number in a separate TINYINT/SMALLINT field.
31
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
