How Should I Model A Field That Can Contain Both Numeric And String Values In SQL Server 2005?

- 1 answer

I have a new database table I need to create...
It logically contains an ID, a name, and a "value".
That value field could be either numeric or a character string in nature.

I don't think I want to just make the field a varchar, because I also want to be able to query with filters like WHERE value > 0.5 and such.

What's the best way to model this concept in SQL Server 2005?

EDIT: I'm not opposed to creating multiple fields here (one for numbers, one for non-numbers), but since they're all really the same concept, I wasn't sure that was a great idea.
I guess I could create separate fields, then have a view that sort of coalesces them into a single logical column.

Any opinions on that?

What I want to achieve is really pretty simple... usually this data will just be blindly displayed in a grid-type view.
I want to be also able to filter on the numeric values in that grid. This table will end up being in the tens of millions of records, so I don't want to paint myself into a corner with querying performance.
That querying performance is my main concern.



Your issue with mixing data may be how Sql 2005 sorts text data. It's not a 'natural' sort.

If you have a varchar field and you do:

where value > '20.5'

Values like "5" will be in your result (as in a character based sort "5" comes after "20.5")

You're going to be better off with separate columns for storage.

Use Coalesce to merge them into one column if you need them merged in your results:

select [ID], [Name], Coalesce( [value_str], [value_num] )
from [tablename]