Datatype for System.Version in sql server

Question

What is the best way to store System.Version in SQL Server?

When I use varchar type, result of order by asc is:

1.0.0.0
11.0.0.0
12.0.0.0
2.0.0.0

Answer 1

you can use a varchar column

you could order like this

SELECT *
FROM t_version 
ORDER BY CAST('/' + vid + '/' AS HIERARCHYID)

SQL fiddle is not working today , other wise I could have showed a demo

Please run this for testing

 SELECT * FROM 
( VALUES 
        ( '1.0.0.0' ),
        ( '11.0.0.0' ),
        ('12.0.0.0'),
        ('2.0.0.0') ) AS vid ( vid ) 
ORDER BY CAST('/' + vid + '/' AS HIERARCHYID)