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]'
             )
);