在SQL Server中，System.Version的数据类型是什么？

Question

如何最佳地将System.Version存储在SQL Server中？

当我使用varchar类型时，按升序排序的结果为：

1.0.0.0
11.0.0.0
12.0.0.0
2.0.0.0

- Omid Mafakher

---

+1 针对这个好问题。 - Viral Shah

你有其他相关的列可以帮助排序版本吗？ - V4Vendetta

@V4Vendetta 不，我没有它。 - Omid Mafakher

Answer 1

您可以使用varchar列。

您可以像这样排序。

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

今天 SQL Fiddle 不可用，否则我可以展示一个演示。

请运行此代码进行测试。

 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)

- Joe G Joseph

---

提示：在演示答案中，您可以更轻松地使用 VALUES 而不是 UNION ALL。例如： SELECT * FROM ( VALUES ( '1.0.0.0' ), ( '11.0.0.0' ) ... ) AS vid ( vid ) ORDER BY ... - user743382

@hvd 我通常只回答标记为 sql-server-2008 或 2012 的问题。 - RichardTheKiwi

@RichardTheKiwi 说得好，但是 HIERARCHYID 已经需要 SQL Server 2008 :) - user743382

Answer 2

将其存储为普通的varchar，适用于版本高达4个部分，使用PARSENAME拆分字符串并按照4个单独的列排序。

例如：

ORDER BY PARSENAME(version,4),
         PARSENAME(version,3),
         PARSENAME(version,2),
         PARSENAME(version,1)

- RichardTheKiwi

Answer 3

为了支持混合长度版本之间的排序（例如“1.2”与“1.2.3.4”），可以执行到十进制的映射（作为内联表值函数）。

create function Common.ufn_OrderableVersion(@pVersion nvarchar(100))
returns table
as
/*---------------------------------------------------------------------------------------------------------------------
    Purpose:  Provide a mapping from Versions of the form 'a.b.c.d', 'a.b.c, 'a.b', 'a', null to 
              an orderable decimal(25, 0) 

              Since Parsename() doesn't apply easily to mixed length comparisions (1.2 vs 1.2.3.4)

 Test Cases:
              select * from Common.ufn_OrderableVersion(null);       -- null
              select * from Common.ufn_OrderableVersion('0');        -- 1000000000000000000000000
              select * from Common.ufn_OrderableVersion('1');        -- 1000001000000000000000000
              select * from Common.ufn_OrderableVersion('1.2.3.4');  -- 1000001000002000003000004

              select Version
                from 
                   (
                      select '1.3.5.3' as Version
                      union all
                      select '1.2.5.3' as Version
                      union all
                      select '1.1.5.3' as Version
                      union all
                      select '1.3.5.2' as Version
                      union all
                      select null as Version
                      union all                      
                      select '' as Version
                      union all
                      select '2' as Version
                      union all
                      select '1.2' as Version
                      union all
                      select '1' as Version                      
                   ) v 
               order by (select Value from Common.ufn_OrderableVersion(Version))

    Modified    By              Description
    ----------  --------------  ---------------------------------------------------------------------------------------
    2015.08.24  crokusek        Initial Version
  ---------------------------------------------------------------------------------------------------------------------*/
return         
    -- 25 = 1 + VersionPositions * MaxDigitsPerSegment
    select convert(decimal(25,0), '1' + 
           stuff((select format(Value, '000000')
                    from 
                       (
                          select convert(int, Value) as Value, RowNumber 
                              -- Support empty string and partial versions. Null maps to null
                            from Common.ufn_SplitUsingXml(@pVersion + '.0.0.0.0', '.') -- pad right
                           where RowNumber <= 4 -- trim right
                       ) as v
                   order by RowNumber
                     for xml path ('')
                ), 1, 0, '')
           ) as Value
go

依赖项：

create function Common.ufn_SplitUsingXml
(
   @pList       nvarchar(max),
   @pDelimiter  nvarchar(255)
)
returns table
as
/*---------------------------------------------------------------------------------------------------------------------
    Purpose:  Split an Identifier using XML as an inline table valued function.  
              Using the SQL Server CLR (C#) capability would be the most efficient way to support this.

   Warnings:  Will not work if the input contains special XML characters like '<', '>' or '&'.
              Caller must add "option (maxrecursion 0)" for lists greater than 100 (it can't be added within the ufn)                  

    Modified    By              Description
    ----------  --------------  ---------------------------------------------------------------------------------------
    2015.08.24  inet            http://sqlperformance.com/2012/07/t-sql-queries/split-strings
  ---------------------------------------------------------------------------------------------------------------------*/
return 
(  
  select Value = y.i.value('(./text())[1]', 'nvarchar(4000)'),
         row_number() over (order by (select null)) as RowNumber 
  from 
  (  
    select x = convert(XML, '<i>' 
       + replace(@pList, @pDelimiter, '</i><i>') 
       + '</i>').query('.')
  ) AS a cross apply x.nodes('i') AS y(i)
  -- option (maxrecursion 0) must be added by caller for lists greater than 100
);
go

比较：

alter function Common.ufn_CompareVersions
(
   @pVersionA nvarchar(100),
   @pVersionB nvarchar(100)
)
returns table
as
/*---------------------------------------------------------------------------------------------------------------------
    Purpose:  Compare Version of the form 'A.B.C.D'.  
              Comparing versions of different lengths is also supported 'A.B'.

 Test Cases:
              select Result from Common.ufn_CompareVersions('1', null) -- 1
              select Result from Common.ufn_CompareVersions(null, '1') -- -1
              select Result from Common.ufn_CompareVersions('1', '1') -- 0
              select Result from Common.ufn_CompareVersions('1', '2') -- -1
              select Result from Common.ufn_CompareVersions('2', '1') -- 1
              select Result from Common.ufn_CompareVersions('1', '1.2') -- -1
              select Result from Common.ufn_CompareVersions('1.2', '1') -- 1
              select Result from Common.ufn_CompareVersions('1.2.3.4', '1.2.3.4') -- 0
              select Result from Common.ufn_CompareVersions('1.2.3', '1.2.3.4') -- -1
              select Result from Common.ufn_CompareVersions('1.2.3.4', '1.2.3') -- 1
              select Result from Common.ufn_CompareVersions('1.9.3.4', '1.2.3.4') -- 1
              select Result from Common.ufn_CompareVersions('1.2.3.4', '1.9.3.4') -- -1
              select Result from Common.ufn_CompareVersions('1.002', '1.2') -- 0
              select Result from Common.ufn_CompareVersions('1.2', '1.2.0') -- 0

    Modified    By           Description
    ----------  -----------  ------------------------------------------------------------------------------------------
    2015.08.24  crokusek     Initial Version
  ---------------------------------------------------------------------------------------------------------------------*/
return    
    with Compares as
    (
      select (select IsNull(Value, 0) from Common.ufn_OrderableVersion(@pVersionA)) as A,
             (select IsNull(Value, 0) from Common.ufn_OrderableVersion(@pVersionB)) as B
    )
    select case when A > B then 1
                when A < B then -1
                else 0
           end as Result
      from Compares
go

- crokusek