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
Temporal tables (also known as system-versioned temporal tables) are a database feature that brings built-in support for providing information about data stored in the table at any point in time, rather than only the data that is correct at the current moment in time.
Finding the SQL Server version with query We can use the @@VERSION function to find out all version details of the SQL Server instance. The @@VERSION function returns a one-line string output and this output also provides all the necessary information about the SQL Server.
Data types in SQL Server are organized into the following categories: Exact numerics. Unicode character strings. Approximate numerics. Binary strings.
To be able to enable System-Versioned in a table, the table should contain a primary key, two not-nullable datetime2 period columns that are defined as GENERATED ALWAYS AS ROW START or END, and passed as parameters in the PERIOD FOR SYSTEM_TIME during the table creation process.
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)
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