Sql-server – How to handle a results in a field where they could be NVARCHAR, FLOAT, or INT

datatypessql server

I have a table that holds results. Based on what the ResultTypeId is these could be either NVARCHAR, FLOAT, or INT. In order to handle all these types the Value field is NVARCHAR

I've found that when handling the results I get problems. For example when trying to sum multiple FLOAT results I need to cast them to FLOAT first.

I think this is the wrong approach to storing the data. The only alternative I can think of is to create a value field for each data type, e.g.:
ValueNumeric ValueText.

What is the recommended approach for this problem?

Best Answer

Assuming your question is asking how you might define a table similar to your 'alternative', here is 'an' answer.

While I cannot tell you 'the recommended approach', I can say we have a table that is very similar to your situation. We can store different kinds of data depending on other criteria. Our table definition is similar to what your 'alternative' describes.

In our table, we have a column called DATA_TYPE CHAR(1) which identifies the type of data being stored. I didn't design the table (as I would have opted for more than 1 character to identify the data type), but valid values are

  • C (Character)
  • D (Date)
  • I (Integer)
  • P (Decimal) - (it comes from a mainframe data type called packed-decimal)

(In your situation, you might also define F-Float, B-Bit, etc.)

We then have separate columns to hold the different data types

CREATE TABLE [SCFBDM].[CDB_AUDIT_LOG](
    [ENTITY_TYPE] [char](6) NOT NULL,
    [ENTITY_ID] [char](2) NOT NULL,
    [ENTITY_KEY] [datetime2](6) NOT NULL,
    [POLICY_NUMBER] [int] NOT NULL,
    [DEC_AMEND] [decimal](7, 3) NOT NULL,
    [ITEM] [datetime2](6) NOT NULL,
    [ENDORSEMENT] [char](6) NOT NULL,
    [ENDORSEMENT_ITEM] [datetime2](6) NOT NULL,
    [TABLE_OWNER] [char](8) NOT NULL,
    [TABLE_NAME] [char](18) NOT NULL,
    [ENTITY_OR_COLUMN] [char](1) NOT NULL,
    [ENTITY_COLUMN_NAME] [char](18) NOT NULL,
    [ROW_TYPE] [char](1) NOT NULL,
    [ACTION] [char](1) NOT NULL,
    [OVERALL_EFFECT] [char](1) NOT NULL,
    [ACCOUNT_NUMBER] [datetime2](6) NOT NULL,
    [DATA_TYPE] [char](1) NOT NULL,
    [DATA_CHARACTER] [char](35) NULL,
    [DATA_DATE] [date] NULL,
    [DATA_DECIMAL] [decimal](11, 2) NULL,
    [DATA_INTEGER] [int] NULL,
    [USER_ID] [char](8) NULL
)