Best way to store column data as row MS SQL

Question

I have some data which columns is dynamic and the number of column data can increase/decrease at any time. So i am planning to store them in row wise instead of the column format.

I have the master table of columns placed and its indicate what data type the columns is using. I am drawing the master table below for your reference

CID     Name          Type
1      Speed         Double
2      Input1        Bool
3      Message       String
.......
.......

Now i have thought of two way to store this dynamic column data First way is

CID      Data_bool       Data_String      Data_Double
1        NULL            NULL             12
2        True            NULL             NULL
3        NULL            test             NULL
1        NULL            NULL             5
1        NULL            NULL             15

Second way was to have one generalized varchar column and store each value as string over there so it looks like

CID      Datas
1        12
2        True
3        test
1        5
1        15

If you look at the database normalization point of view then second ways seems to be good. But i think it can create problem in data retrieval. Because i want to filter the data like "Speed > 10". So if i go second way (Where i store all value as string) i think the expression will take more time to evalute And if i go first way for the expression then first I need to determine the columns against which i need to evalute the expression. Ex. for the expression Speed > 10, first i have to check Speed is of which data type (string, bool etc) and then again execute the expression of "data_double > 10"

Both have their own drawbacks. Can someone point out that which way will give me less headache in future. Remember this table will grow in millions of records on later stage.

I appreciate your view and time here. thank you.

Answer 1

I' m not shure how you are accessing the data, maybe SQL_Variant may be a option for you in combination with SQL_VARIANT_PROPERTY.

Declare @a table(id int, cont sql_variant)
insert into @a select 1,'test'
insert into @a select 1,Cast('20130101' as DateTime)
insert into @a select 1,Cast('20130201' as Datetime)
insert into @a select 1,Cast(1 as Bit)
insert into @a select 1,Cast(0 as Bit)
Select * from
(
Select *  from @a
where SQL_VARIANT_PROPERTY(cont,'BaseType')='datetime'
) x
Where cont>Cast('20130101' as DateTime)

Answer 2

One approach could be to use a one table for each data type you're interested in. Each of these tables would have just two fields. An int-type PK and a corresponding-type column to store data. In the master table, you could just have an FK of int-type that links to one of the specific type tables and another field of tinyint type that decides which child table the FK belongs to.

Master Table

ID int PK

ValueID int Not Null

Type tinyint Not Null

Child Table(s)

ID int PK

Value string Not Null

The ValueID is FK from Child table to Master table. Similar child tables can be created for other types.