Search code examples
sqlsql-serversqldatatypes

MS SQL - Storing Datatypes

I want to create a table with following columns

AssessmentTypeSpecificAttributeId int
AssessmentTypeSpecificAttributeName nvarchar(256)
AssessmentTypeSpecificAttributeDataType DATATYPE

Is there any way to store datatypes as data...

If there is no way, I will have to use nvarchar(256) then.

But first, I would like to give it a try.

Solution

  • Where is the actual value of your attribute? The only thing you can use with your current table is NVARCHAR because "datetime" is not a valid datetime value, "int" is not a valid int value etc.

    Assuming that the same table includes the attribute's value then an alternative would be to use sql_variant:

    drop table #t
go

create table #t (
    AttrID int primary key,
    AttrName nvarchar(256) not null,
    AttrValue sql_variant not null,
    AttrDataType as sql_variant_property(AttrValue, 'BaseType')
)
go

insert into #t (AttrID, AttrName, AttrValue) select 1, N'Test integer', cast(1 as int)
insert into #t (AttrID, AttrName, AttrValue) select 2, N'Test datetime', getdate()
insert into #t (AttrID, AttrName, AttrValue) select 3, N'Test decimal', cast(1.0 as decimal(2,1))
go

select * from #t

    But sql_variant has some big disadvantages: it can't store all data types, it's not supported by all tools and libraries, it requires extra coding everywhere to convert it to the 'real' data type etc.