0

What is the best data type for field with format like this X.X.X.x

Here is the sample field data

15.8.0
15.8.1
15.8.2
15.8.3
15.8.4
1.8.0
2.8.2
9.8.2
9.8.3
9.8.4
10.8.0
13.15.0.1
13.15.1.1
13.15.2.1
13.15.3.1
13.15.4.1
13.0.0
26.0.0.1
26.0.0.2 

This field use as primary field of table

Thanks

1
  • 4 separate (TINYINT) columns. – ypercubeᵀᴹ Oct 14 '16 at 9:15
3

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.