Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Datatype for System.Version in sql server

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 
like image 322
Omid Mafakher Avatar asked Oct 17 '12 11:10

Omid Mafakher


People also ask

What is system Version table in SQL Server?

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.

How do I determine SQL Server version?

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.

What are SQL Server data types?

Data types in SQL Server are organized into the following categories: Exact numerics. Unicode character strings. Approximate numerics. Binary strings.

How do I enable system versioning in SQL Server?

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.


1 Answers

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) 
like image 58
Joe G Joseph Avatar answered Sep 22 '22 01:09

Joe G Joseph