SQL Server - Storing multiple decimal values in a column?

Question

I know storing multiple values in a column. Not a good idea.

It violates first normal form --- which states NO multi valued attributes. Normalize period...

I am using SQL Server 2005

I have a table that require to store lower limit and uppper limit for a measurement, think of it as a minimum and maximum speed limit... only problem is only 2 % out of hundread i need upper limit. I will only have data for lower limit.

I was thinking to store both values in a column (Sparse column introduces in 2008 so not for me)

Is there a way...? Not sure about XML..

Answer 1

You'd have to be storing an insane amount of rows for this to even matter. The price of a 1 terabyte disk is now 60 dollars!

Two floats use up 8 bytes; an XML string will use a multiple of that just to store one float. So even though XML would store only one instead of two columns, it would still consume more space.

Just use a nullable column.

Answer 2

To answer your question, you could store it as a string with a particular format that you know how to parse (e.g. "low:high").

But ... this is really not a good idea.

Dealing with 98% of the rows having NULL value for upper limit is totally fine IMHO. Keep it clean, and you won't regret it later.

Answer 3

Even so, I agree with Andomar. Use two colums, low limit and high limit. If either value could be unknown, make those columns nullable.

Alternatively, designate a default arbitrary minimum and maximum values, and use those values instead of nulls. (Doing this means you never have to mess with trinary logic, e.g. having to wrap everything with ISNULL or COALESCE.)

Once you define your schema, there are tricks you can use to reduce storage space (such as compression and sparce columns).