Multiple data types in column
-
Please assist.
This table:
Is turned into a pivot table:
See column K which contains data types. Can a column contain more than one data type, please advise?
-
Can a column contain more than one data type? The short answer is no, unless the column is of data type variant. I do not recommend using this. I mean, I DO NOT RECOMMEND using this. However, that's one way to have multiple data types in a column. It's a horrible practice and will lead to all sorts of problems. Don't do it.
Instead, people use the VARCHAR data type to store numbers, strings, whatever, then rely on automatic conversions or CAST/CONVERT to get the info to the correct data type when they finally need to do math, data math, what have you. Again, this leads to all sorts of problems. Again, a bad practice. Don't do it.
There are data types for a reason. The data stored in a SQL Server relational table is meant to be structured. If you need semi or unstructured storage, use JSON. Otherwise, store dates as dates, integers as integers, strings as strings. Don't try to cram everything into a single column, or cram thousands of columns into a table. Use structured storage as it's intended and use relational storage as it's intended. Trying to force behaviors in other directions inevitably leads to problems.
Can you hammer nails with a screw driver? Yes. Why would you want to? Should you? Is it going to work well? Are there well established alternatives?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
It appears that all of the values are either float or int types.
Unless you are dealing with astronomical sized numbers, I would recommend removing the float types. They are called "inexact numerics" for a reason. If all of them can be changed to the same type, some of the issues go away.
Are there multiple sources of this data, which is the reason for the differing types?
It appears that the two pictures do not contain the same data. In the "table" picture, there is nothing in the "pivot" picture that matches.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Just store everything as FLOAT. Integers are exact when converted to Floats so long is you don't violate the precision. Better yet, change both to be Decimal(38,15).
Then ask yourself if it's really worth trying to do the denormalization that you're trying to do.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply