How should I model a field that can contain both numeric and string values in SQL Server 2005?

Question

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.

Answer 1

A good way to get the query support you want is to have two columns: numvalue that stores a number and textvalue that stores characters. They should be nullable or at least have some default that represents no value. Your application can then decide which column to store its value and which to leave with no value.