[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

SQL: storing mixed data types

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.

2 Solutions
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!
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

BlearyEyeAuthor Commented:
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now