/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

Store Custom Fields/Attributes in Microsoft SQL Server Database 
(Part 2 - Name/Value pairs)

2012-02-16
*****************************************************/
set nocount on
go

create table dbo.Articles
(
	ArticleId int not null,
	Name nvarchar(255) not null,
	Description nvarchar(max) not null,
	
	constraint PK_Articles
	primary key clustered(ArticleId)
)
go
	
create table dbo.ArticleAttributes
(
	ArticleId int not null,
	AttrIndex smallint not null,
	Value sql_variant not null,
	
	constraint PK_ArticleAttributes
	primary key clustered(ArticleId, AttrIndex),
	
	constraint FK_ArticleAttributes
	foreign key(ArticleId)
	references dbo.Articles(ArticleId)
	on update cascade
	on delete cascade
)
go



/* This table is not required for the demo
But it's good idea to have something like that */
create table dbo.CustomAttributesMetadata
(
	-- internal application EntityId
	EntityId smallint not null,
	AttrIndex smallint not null,
	AttributeName nvarchar(32) not null,
	-- references sys.types.system_type_id
	TypeId tinyint not null, 
	-- references sys.types.name
	TypeName sysname not null,	
	
	constraint PK_CustomAttributesMetadata
	primary key clustered(EntityId,AttrIndex)
)
go

insert into dbo.CustomAttributesMetadata
(EntityId, AttrIndex, AttributeName, TypeId, TypeName)
values
	(1 /*Articles*/, 1, 'Color', 231, 'nvarchar'),
	(1 /*Articles*/, 2, 'Weight', 62, 'float')
	-- Other attributes
go

-- Let's put some data
begin tran
	;with IDs(ID)
	as
	(
		select 1
		union all
		select ID + 1
		from IDs
		where ID < 10000
	)
	insert into dbo.Articles(ArticleId,Name,Description)
		select 
			ID, 
			N'Article ' + CONVERT(nvarchar(6),ID),
			REPLICATE('Description ',25)
		from IDs
	option (maxrecursion 0)

	;with IDs(ID)
	as
	(
		select 1
		union all
		select ID + 1
		from IDs
		where ID < 10000
	)
	,Attributes(Idx, Value)
	as
	(
		select 1, convert(sql_variant,'Black') union all
		select 2, convert(sql_variant,12.324) union all		
		select 3, convert(sql_variant,0) union all
		select 4, convert(sql_variant,23.214) union all		
		select 5, convert(sql_variant,N'Some Text Information') union all
		select 6, convert(sql_variant,100.21454) union all		
		select 7, convert(sql_variant,-28.72) union all
		select 8, convert(sql_variant,75.13574) union all		
		select 9, convert(sql_variant,'+1 (123) 456-7890') union all
		select 10, convert(sql_variant,N'John Doe') union all		
		select 11, convert(sql_variant,5712123) union all
		select 12, convert(sql_variant,0) union all		
		select 13, convert(sql_variant,1) union all
		select 14, convert(sql_variant,'contact@mydomain.com') union all		
		select 15, convert(sql_variant,10.25)
	)
	insert into dbo.ArticleAttributes(ArticleId, AttrIndex, Value)
		select IDs.ID, a.Idx, a.Value
		from IDs cross join Attributes a 
	option (maxrecursion 0)
commit

set statistics io on
go

-- Classic approach
select 
	a.ArticleId, a.Name, a.Description,
	CONVERT(nvarchar(32), aa1.Value) as [Color],
	CONVERT(float, aa2.Value) as [Weight],
	CONVERT(int, aa3.Value) as [Group],
	CONVERT(float, aa4.Value) as [UnitCost],
	CONVERT(nvarchar(max), aa5.Value) as [Comment],
	CONVERT(float, aa6.Value) as [UnitPrice],
	CONVERT(decimal(9,6), aa7.Value) as [MadeAtLat],
	CONVERT(decimal(9,6), aa8.Value) as [MadeAtLon],
	CONVERT(nvarchar(32), aa9.Value) as [TotalSold],
	CONVERT(nvarchar(128), aa10.Value) as [FactoryContact],
	CONVERT(int, aa11.Value) as [FeatureMask],
	CONVERT(bit, aa12.Value) as [ContactSpeaksSpanish],
	CONVERT(bit, aa13.Value) as [ContactSpeaksEnglish],
	CONVERT(nvarchar(128), aa14.Value) as [ContactEmail],
	CONVERT(money, aa15.Value) as [Shipping]
from 
	dbo.Articles a join dbo.ArticleAttributes aa1 on
		a.ArticleId = aa1.ArticleId and aa1.AttrIndex = 1
	join dbo.ArticleAttributes aa2 on
		a.ArticleId = aa2.ArticleId and aa2.AttrIndex = 2
	join dbo.ArticleAttributes aa3 on
		a.ArticleId = aa3.ArticleId and aa3.AttrIndex = 3
	join dbo.ArticleAttributes aa4 on
		a.ArticleId = aa4.ArticleId and aa4.AttrIndex = 4
	join dbo.ArticleAttributes aa5 on
		a.ArticleId = aa5.ArticleId and aa5.AttrIndex = 5
	join dbo.ArticleAttributes aa6 on
		a.ArticleId = aa6.ArticleId and aa6.AttrIndex = 6
	join dbo.ArticleAttributes aa7 on
		a.ArticleId = aa7.ArticleId and aa7.AttrIndex = 7
	join dbo.ArticleAttributes aa8 on
		a.ArticleId = aa8.ArticleId and aa8.AttrIndex = 8
	join dbo.ArticleAttributes aa9 on
		a.ArticleId = aa9.ArticleId and aa9.AttrIndex = 9
	join dbo.ArticleAttributes aa10 on
		a.ArticleId = aa10.ArticleId and aa10.AttrIndex = 10
	join dbo.ArticleAttributes aa11 on
		a.ArticleId = aa11.ArticleId and aa11.AttrIndex = 11
	join dbo.ArticleAttributes aa12 on
		a.ArticleId = aa12.ArticleId and aa12.AttrIndex = 12
	join dbo.ArticleAttributes aa13 on
		a.ArticleId = aa13.ArticleId and aa13.AttrIndex = 13
	join dbo.ArticleAttributes aa14 on
		a.ArticleId = aa14.ArticleId and aa14.AttrIndex = 14
	join dbo.ArticleAttributes aa15 on
		a.ArticleId = aa15.ArticleId and aa15.AttrIndex = 15
where
	a.ArticleId between 1100 and 1200
go

-- Using PIVOT
-- Plan with possible Merge Join
;with PivotedAttributes(ArticleId,Color,Weight,[Group],UnitCost,
	Comment,UnitPrice,MadeAtLat,MadeAtLon,TotalSold,FactoryContact,
	FeatureMask,ContactSpeaksSpanish,ContactSpeaksEnglish,
	ContactEmail,Shipping) 
as
(
	select 
		ArticleId,
		CONVERT(nvarchar(32), [1]) as [Color],
		CONVERT(float, [2]) as [Weight],
		CONVERT(int, [3]) as [Group],
		CONVERT(float, [4]) as [UnitCost],
		CONVERT(nvarchar(max), [5]) as [Comment],
		CONVERT(float, [6]) as [UnitPrice],
		CONVERT(decimal(9,6), [7]) as [MadeAtLat],
		CONVERT(decimal(9,6), [8]) as [MadeAtLon],
		CONVERT(nvarchar(32), [9]) as [TotalSold],
		CONVERT(nvarchar(128), [10]) as [FactoryContact],
		CONVERT(int, [11]) as [FeatureMask],
		CONVERT(bit, [12]) as [ContactSpeaksSpanish],
		CONVERT(bit, [13]) as [ContactSpeaksEnglish],
		CONVERT(nvarchar(128), [14]) as [ContactEmail],
		CONVERT(money, [15]) as [Shipping]
	from
	(
		select ArticleId, AttrIndex, Value
		from dbo.ArticleAttributes
	) as Source		
	PIVOT (MIN(Value) for AttrIndex in ([1],[2],[3]
		,[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])) as A
)
select a.ArticleId, a.Name, a.Description
	,p.Color,p.Weight,p.[Group],p.UnitCost,p.Comment
	,p.UnitPrice,p.MadeAtLat,p.MadeAtLon,p.TotalSold,p.FactoryContact
	,p.FeatureMask,p.ContactSpeaksSpanish,p.ContactSpeaksEnglish
	,p.ContactEmail,p.Shipping
from PivotedAttributes p join dbo.Articles a on
	p.ArticleId = a.ArticleId
where a.ArticleId between 1100 and 1200
go				

-- Plan with Nested Loop
select a.ArticleId, a.Name, a.Description
	,p.Color,p.Weight,p.[Group],p.UnitCost,p.Comment
	,p.UnitPrice,p.MadeAtLat,p.MadeAtLon,p.TotalSold,p.FactoryContact
	,p.FeatureMask,p.ContactSpeaksSpanish,p.ContactSpeaksEnglish
	,p.ContactEmail,p.Shipping
from 
	dbo.Articles a cross apply
	(
		select 
			ArticleId,
			CONVERT(nvarchar(32), [1]) as [Color],
			CONVERT(float, [2]) as [Weight],
			CONVERT(int, [3]) as [Group],
			CONVERT(float, [4]) as [UnitCost],
			CONVERT(nvarchar(max), [5]) as [Comment],
			CONVERT(float, [6]) as [UnitPrice],
			CONVERT(decimal(9,6), [7]) as [MadeAtLat],
			CONVERT(decimal(9,6), [8]) as [MadeAtLon],
			CONVERT(nvarchar(32), [9]) as [TotalSold],
			CONVERT(nvarchar(128), [10]) as [FactoryContact],
			CONVERT(int, [11]) as [FeatureMask],
			CONVERT(bit, [12]) as [ContactSpeaksSpanish],
			CONVERT(bit, [13]) as [ContactSpeaksEnglish],
			CONVERT(nvarchar(128), [14]) as [ContactEmail],
			CONVERT(money, [15]) as [Shipping]
		from
		(
			select attr.ArticleId, attr.AttrIndex, attr.Value
			from dbo.ArticleAttributes attr
			where attr.ArticleId = a.ArticleId
		) as Source		
		PIVOT (MIN(Value) for AttrIndex in ([1],[2],[3]
			,[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])) as A
	) p
where a.ArticleId between 1100 and 1200
go

-- Add index to support search and paging by one field
-- Max Value of the key could be an issue
create index IDX_ArticleAttributes_AttrIndex_Value
on dbo.ArticleAttributes(AttrIndex,Value)
go

-- Searching for the attribute value
update dbo.ArticleAttributes set Value = 10
where ArticleId between 100 and 110 and AttrIndex = 3
go

;with PivotedAttributes(ArticleId,Color,Weight,[Group],UnitCost,
	Comment,UnitPrice,MadeAtLat,MadeAtLon,TotalSold,FactoryContact,
	FeatureMask,ContactSpeaksSpanish,ContactSpeaksEnglish,
	ContactEmail,Shipping) 
as
(
	select 
		ArticleId,
		CONVERT(nvarchar(32), [1]) as [Color],
		CONVERT(float, [2]) as [Weight],
		CONVERT(int, [3]) as [Group],
		CONVERT(float, [4]) as [UnitCost],
		CONVERT(nvarchar(max), [5]) as [Comment],
		CONVERT(float, [6]) as [UnitPrice],
		CONVERT(decimal(9,6), [7]) as [MadeAtLat],
		CONVERT(decimal(9,6), [8]) as [MadeAtLon],
		CONVERT(nvarchar(32), [9]) as [TotalSold],
		CONVERT(nvarchar(128), [10]) as [FactoryContact],
		CONVERT(int, [11]) as [FeatureMask],
		CONVERT(bit, [12]) as [ContactSpeaksSpanish],
		CONVERT(bit, [13]) as [ContactSpeaksEnglish],
		CONVERT(nvarchar(128), [14]) as [ContactEmail],
		CONVERT(money, [15]) as [Shipping]
	from
	(
		select ArticleId, AttrIndex, Value
		from dbo.ArticleAttributes
	) as Source		
	PIVOT (MIN(Value) for AttrIndex in ([1],[2],[3]
		,[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])) as A
)
select a.ArticleId, a.Name, a.Description
	,p.Color,p.Weight,p.[Group],p.UnitCost,p.Comment
	,p.UnitPrice,p.MadeAtLat,p.MadeAtLon,p.TotalSold,p.FactoryContact
	,p.FeatureMask,p.ContactSpeaksSpanish,p.ContactSpeaksEnglish
	,p.ContactEmail,p.Shipping
from 
	dbo.Articles a join dbo.ArticleAttributes aa on 
		a.ArticleId = aa.ArticleId and
		aa.AttrIndex = 3 and
		aa.Value = 10
	join PivotedAttributes p on
		a.ArticleId = p.ArticleId
go	

--Paging
;with Paging(ArticleId, RowNum)
as
(
	select 
		ArticleId, 
		ROW_NUMBER() over (order by Value)
	from dbo.ArticleAttributes 
	where AttrIndex = 3
)
,PivotedAttributes(ArticleId,RowNum,Color,Weight,[Group],UnitCost,
	Comment,UnitPrice,MadeAtLat,MadeAtLon,TotalSold,FactoryContact,
	FeatureMask,ContactSpeaksSpanish,ContactSpeaksEnglish,
	ContactEmail,Shipping) 
as
(
	select 
		ArticleId,
		RowNum,
		CONVERT(nvarchar(32), [1]) as [Color],
		CONVERT(float, [2]) as [Weight],
		CONVERT(int, [3]) as [Group],
		CONVERT(float, [4]) as [UnitCost],
		CONVERT(nvarchar(max), [5]) as [Comment],
		CONVERT(float, [6]) as [UnitPrice],
		CONVERT(decimal(9,6), [7]) as [MadeAtLat],
		CONVERT(decimal(9,6), [8]) as [MadeAtLon],
		CONVERT(nvarchar(32), [9]) as [TotalSold],
		CONVERT(nvarchar(128), [10]) as [FactoryContact],
		CONVERT(int, [11]) as [FeatureMask],
		CONVERT(bit, [12]) as [ContactSpeaksSpanish],
		CONVERT(bit, [13]) as [ContactSpeaksEnglish],
		CONVERT(nvarchar(128), [14]) as [ContactEmail],
		CONVERT(money, [15]) as [Shipping]
	from
	(
		select a.ArticleId, p.RowNum, a.AttrIndex, a.Value
		from dbo.ArticleAttributes a join Paging p on
			a.ArticleId = p.ArticleId
		where
			p.RowNum between 1 and 10
			
	) as Source		
	PIVOT (MIN(Value) for AttrIndex in ([1],[2],[3]
		,[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])) as A
)
select a.ArticleId, a.Name, a.Description
	,p.Color,p.Weight,p.[Group],p.UnitCost,p.Comment
	,p.UnitPrice,p.MadeAtLat,p.MadeAtLon,p.TotalSold,p.FactoryContact
	,p.FeatureMask,p.ContactSpeaksSpanish,p.ContactSpeaksEnglish
	,p.ContactEmail,p.Shipping
from PivotedAttributes p join dbo.Articles a on
	p.ArticleId = a.ArticleId
order by
	p.RowNum

go