Skip to main content
added 138 characters in body
Source Link
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

I can't say it would be the best ("best" in what sense?) but one way to do this is to have the 4 numbers in 4 separate TINYINT columns.

You can have the PRIMARY KEY as a composite 4-column one or as a persisted column. Considering from the data provided that the 4th number can be NULL, I'd choose the persisted as primary key and add a unique constraints on the 4 columns:

create table versions
( a tinyint not null,
  b tinyint not null,
  c tinyint not null,
  d tinyint null,
  version as cast( concat( a, '.', b, '.', c, 
                           case when d is null then '' else concat('.',  d) end
                   ) as varchar(15)) 
      persisted,
  constraint version_uq
      unique clustered (a,b,c,d),
  constraint versions_pk
      primary key (version),
) ; 

Tested at rextester.com.


Another option would be to use the HierarchyID type.

I can't say it would be the best ("best" in what sense?) but one way to do this is to have the 4 numbers in 4 separate TINYINT columns.

You can have the PRIMARY KEY as a composite 4-column one or as a persisted column. Considering from the data provided that the 4th number can be NULL, I'd choose the persisted as primary key and add a unique constraints on the 4 columns:

create table versions
( a tinyint not null,
  b tinyint not null,
  c tinyint not null,
  d tinyint null,
  version as cast( concat( a, '.', b, '.', c, 
                           case when d is null then '' else concat('.',  d) end
                   ) as varchar(15)) 
      persisted,
  constraint version_uq
      unique clustered (a,b,c,d),
  constraint versions_pk
      primary key (version),
) ; 

Tested at rextester.com.

I can't say it would be the best ("best" in what sense?) but one way to do this is to have the 4 numbers in 4 separate TINYINT columns.

You can have the PRIMARY KEY as a composite 4-column one or as a persisted column. Considering from the data provided that the 4th number can be NULL, I'd choose the persisted as primary key and add a unique constraints on the 4 columns:

create table versions
( a tinyint not null,
  b tinyint not null,
  c tinyint not null,
  d tinyint null,
  version as cast( concat( a, '.', b, '.', c, 
                           case when d is null then '' else concat('.',  d) end
                   ) as varchar(15)) 
      persisted,
  constraint version_uq
      unique clustered (a,b,c,d),
  constraint versions_pk
      primary key (version),
) ; 

Tested at rextester.com.


Another option would be to use the HierarchyID type.

Source Link
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

I can't say it would be the best ("best" in what sense?) but one way to do this is to have the 4 numbers in 4 separate TINYINT columns.

You can have the PRIMARY KEY as a composite 4-column one or as a persisted column. Considering from the data provided that the 4th number can be NULL, I'd choose the persisted as primary key and add a unique constraints on the 4 columns:

create table versions
( a tinyint not null,
  b tinyint not null,
  c tinyint not null,
  d tinyint null,
  version as cast( concat( a, '.', b, '.', c, 
                           case when d is null then '' else concat('.',  d) end
                   ) as varchar(15)) 
      persisted,
  constraint version_uq
      unique clustered (a,b,c,d),
  constraint versions_pk
      primary key (version),
) ; 

Tested at rextester.com.