How Should I Model A Field That Can Contain Both Numeric And String Values In SQL Server 2005?
I have a new database table I need to create...
It logically contains an
name, and a
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?
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]
- → How to make Laravel use my class instead of native PDO?
- → SQL: simple custom column in select doesn't work?
- → How to execute Stored Procedure from Laravel
- → Which database engine type should be specified for Microsoft SQL Database in Laravel?
- → How to troubleshoot PDOException?
- → laravel sql server stored procedure output
- → Issue with converting a date using Carbon
- → SQL microsoft query to Laravel 4.2
- → General error 20018 Cannot Continue the file execution because the session is in the Kill state
- → List names of all available MS SQL databases on server using python
- → Variable which replace DB of name in SSMS
- → Java: database connection. Where is my mistake?
- → How Can I use "Date" Datatype in sql server?