Tutorial :SQL Data Type to store build versions



Question:

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


Solution:1

I would consider storing each part of the number in a separate TINYINT/SMALLINT field.


Solution:2

Perhaps have three or four numeric fields to the version table:

Major, Minor, Revision, Build  


Solution:3

Storing in separate numeric fields is a good idea. Storing as a string in a single field will break sorting when one of the parts reaches 1000. For example, 1.2.999 will appear before (or shown as newer than) 1.2.1000 when it should appear after.


Solution:4

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »