CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-designazure-sql-database
Tim Petri
Tim Petri

Reputation: 105

Storing different datatypes in one column to reduce table count

I am currently working on a project where I am recreating a MS SQL Server database in Azure SQL database. I have been given the current schema (although not with the actual column data type) and I came across this:

Question (QuestionID, QuestionDescription, UnitOfMeasure, SQLDataType)
QuestionAnswer (QuestionID, AnswerID, Answer, SQLDataType, UnitOfMeasure)

So, the database contains a variety of questions which each are answered/measured with different types of data (int, text, date, etc,..) and ALL answers are stored together with a column identifying the data type used in the "Answer" column.

I would like to know if this is a common practice and how this can be implemented? (Multiple types of data in one column). An alternative would obviously be to keep all questions together but seperate out the various answers into different tables, but I can see how that becomes cumbersome with 30+ different questions.

Upvotes: 4

Views: 3363

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44961

You might be able to use sql_variant (the documentation is not clear about Azure).

A big advantage is that you don't have to maintain any metadata about the values.

https://msdn.microsoft.com/en-us/library/ms173829.aspx https://msdn.microsoft.com/en-us/library/ms178550.aspx

create table t (val sql_variant);

insert into t (val) values (123);
insert into t (val) values (4.5);
insert into t (val) values ('Hello');
insert into t (val) values (N'שלום');
insert into t (val) values (GETDATE());
insert into t (val) values (CURRENT_TIMESTAMP);
insert into t (val) values (newid());

select      t.val   
           ,sql_variant_property(t.val, 'BaseType')
           ,sql_variant_property(t.val, 'Precision')
           ,sql_variant_property(t.val, 'Scale')
           ,sql_variant_property(t.val, 'TotalBytes')
           ,sql_variant_property(t.val, 'Collation')
           ,sql_variant_property(t.val, 'MaxLength')

from        t
;

╔══════════════════════════════════════╦══════════════════╦════╦═══╦════╦══════════════════════════════╦══════╗
║                 123                  ║       int        ║ 10 ║ 0 ║ 6  ║             NULL             ║  4   ║
╠══════════════════════════════════════╬══════════════════╬════╬═══╬════╬══════════════════════════════╬══════╣
║ 4.5                                  ║ numeric          ║  2 ║ 1 ║  9 ║ NULL                         ║    5 ║
║ Hello                                ║ varchar          ║  0 ║ 0 ║ 13 ║ SQL_Latin1_General_CP1_CI_AS ║ 8000 ║
║ שלום                                ║ nvarchar         ║  0 ║ 0 ║ 16 ║ SQL_Latin1_General_CP1_CI_AS ║ 8000 ║
║ 2016-10-27 21:08:58.027              ║ datetime         ║ 23 ║ 3 ║ 10 ║ NULL                         ║    8 ║
║ 2016-10-27 21:08:58.030              ║ datetime         ║ 23 ║ 3 ║ 10 ║ NULL                         ║    8 ║
║ E0FBA251-8DC2-4F88-9D23-1FB354932AE5 ║ uniqueidentifier ║  0 ║ 0 ║ 18 ║ NULL                         ║   16 ║
╚══════════════════════════════════════╩══════════════════╩════╩═══╩════╩══════════════════════════════╩══════╝

Upvotes: 0

Daniel A. Thompson
Daniel A. Thompson

Reputation: 1924

This is a pattern that we use in production for the same type of problem. Yes, it works - you just have to make sure that each Answer can actually be cast to the given SQLDataType. We do this at insert/update time.

Upvotes: 2

Related Questions