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 perf is my main concern.

Answer 1

If you want to store numeric and string values in the same column, I am not sure you can avoid doing a lot of casts and converts when using that column as a query filter.

Answer 2

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.

Answer 3

two columns.

Table: (ValueLable as char(x), Value as numerica(p,s))

Answer 4

I don't think it's possible to have a column with both varchar and int type. You could save your value as a varchar and cast it to int during your query. But this way you could get an exception if your value does contain any character. What are you trying to achieve?

Answer 5

If you want it to be able to hold a character string, I think you have to make the column varchar, or similar.

An alternative could be to have 2 or 3 columns instead of the one value column. Maybe have the three columns, value_type (enum between "number" and "string"), number_value, string_value. Then you could reconstruct that query to be

WHERE value_type = 'number' AND number_value > 0.5

Answer 6

I don't think you're going to be able to get around using VARCHAR or NVARCHAR as your data type. With mixed data like you're describing, you'll have to test the value when you pull the field out of the db and perform the appropriate CAST or CONVERT based on the data type.

Answer 7

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]

Answer 8

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?

It depends on the source of the data. If you are getting the data from users (or some other system) in some free-form manner and don't really care what type of data it is, then the best way to store it is the most generic manner (varchar, etc). If the incoming data is more structured and you care about that structure, then it makes more sense to keep that structure in the database by using separate fields.

From the viewpoint of a SELECT it doesn't really matter; you can store it either way and read it as the same schema. Once you get into filters (as you mention) things get a bit more hairy, but still easily doable. However, you don't mention if you need to be able to update this data and if so, if you need to enforce any validation on the data.

From the sounds of it, you need to do different types of searches based on the "type" of value being stored. As such, it may make sense to add a Type field so that any filters can be quickly limited to the type of values that you care about. Note, by Type I mean a more logical, application scope, Type; not the actual datatype being stored.

My recommendation would be to use a single field with a Type column if you need to easily support UPDATEs or use multiple fields (or tables, if these are totally different data sets) if SELECTing and filtering is all that is needed.

Answer 9

You might consider using two columns, one "string" and one "numeric" (whatever variants of those are appropriate) with the "string" column NOT NULL and the "numeric" column allowing NULL values. When inserting a value, always populate the "string" column indpendent of the type, however if the value is numeric, ALSO store it in the "numeric" column. Now you have a built in indicator as to the type (if the "numeric" column is populated it is numeric, if not it is a string), can always just pull the value for display from the "string" column, and can use the "numeric" value in calculations or for proper numeric sorting / comparison as needed. You could always add a third column indicating the value type, but this approach eliminates the need for that. Note that you might consider maintaining the numeric and string values using a set of INSERT and UPDATE triggers.