SQLServer choosing primary key type

I have a list of objects each of its own id and I need 开发者_如何学Goto create a table for them in a database. It's a good idea to use their ids(since they are unique) as a primary key in the table but there's one problem. All ids are integers except for the one object - it has 2 subobjects with ids 142.1 and 142.2, so the id list is 140, 141, 142.1, 142.2, 143... Now if I choose a double as a type of primary key then it will store unnecessary 6 bytes(since double is 8 bytes and INT is 2) to only support two double numbers and I can't choose INT. So what type should I use if I cannot change the list of objects?

The math for double is imprecise, you shouldn't use it for discrete numbers like money or object id's. Consider using decimal(p,s) instead. Where p is the total number of digits, and s is the number of digits behind the dot. For example, a decimal(5,2) could store 123.45, but not 1234 or 12.345.

Another option is a composite primary key for two integers n1, n2:

alter table YourTable add constraint PK_YourTable primary key (n1, n2)

An int is four bytes, not two, so the size difference to a double is not so big.

However, you should definitely not use a floating point number as key, as a floating point number isn't stored as an exact values, but as an approximation.

You can use a decimal with one fractional digit, like decimal(5,1), to store a value like that. A decimal is a fixed point number, so it's stored as an exact value, not an approximation.

Choose VARCHAR of an appropriate length, with CHECK constraints to ensure the data conforms to your domain rules e.g. based on the small sample data you posted:

CREATE TABLE Ids 
(
 id VARCHAR(5) NOT NULL UNIQUE
    CONSTRAINT id__pattern
       CHECK (
              id LIKE '[0-9][0-9][0-9]'
              OR id LIKE '[0-9][0-9][0-9].[1-9]'
             )
);