MS SQL - Storing Datatypes

Question

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.

Pondlife · Accepted Answer · 2012-12-11 17:55:12Z

1

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.

answered Dec 11, 2012 at 17:55

Pondlife

16.3k6 gold badges40 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bilal Fazlani Over a year ago

great answer ! thanks :) I think I'll do it manually as I don know how this will work with typed datasets. I have created a table for datatypes. This was good to know.

Collectives™ on Stack Overflow

MS SQL - Storing Datatypes

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related