SQL: storing mixed data types

Posted on 2008-11-07
Last Modified: 2012-06-27
I have an SQL application where I need to store different kinds of data. The structure is
  - id: string
  - value: string or number

It seems I have 2 choices. Either keep all id/string pairs in one table (say TabS) and all id/number pairs in another (say TabN), or else convert numbers to strings and store them all together. There will be another table that gives the type of each id.

I'm inclined to the second alternative (convert to strings) but wanted to see if there's something I'm missing. The solution needs to be compatible with MS SQL Server and with MySQL.

Question by:BlearyEye
    LVL 2

    Accepted Solution

    I would say that your second option is definitely your best bet, for almost nothing more that reasons of simplicity.  You can store all the values as varchar in the table.  Then when you want to retrieve one, you can programmatically convert it back to whatever format you'd like.  I've done this before and it is not as much trouble as it sounds like.  Hope this helps!
    LVL 39

    Assisted Solution

    Well you have this in the MY and MS SQL zones.  So I don't know what you have.

    BUT.  If you are using MS SQL Server, you can use a SQL_Variant column which will allow you to store all data types, except Text/nText.  The great thing is that it stores it in it's native format.

    create table #t (theVar sql_variant)
    declare @date       datetime
           ,@int        int
           ,@nvarchar   nvarchar(4000)
           ,@varchar    varchar(8000)
           ,@numeric    numeric
    set @date      = getdate()
    set @int       = 1
    set @nvarchar  = N'Hello'
    set @varchar   = 'goodbye'
    set @numeric   = 11.2
    insert into #t values(@date)
    insert into #t values(@int)
    insert into #t values(@nvarchar)
    insert into #t values(@varchar)
    insert into #t values(@numeric)
    select * from #t
    drop table #t

    Open in new window

    LVL 1

    Author Comment

    Clever idea, BrandonGalderisi. Unfortunately, the solution has to support by MS and My SQL, which is why I cross-listed it. Looks like I'm stuck with storing numbers as strings.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now