If you have some set descriptions then instead of having one large column with that information have a one-to-many relationship to a table of descriptions e.g.
create table Product(id int, [name] varchar(255),[price] decimal(15,2))
create table Description (id int, description varchar(255))
create table ProductDescription (ProductId int, DescriptionId int)
insert into #Product (id, [name], price) values
(1, 'Product1',100.10), (2, 'Product2', 200.20), (3, 'Product3', 300.30)
insert into #Description (id, [description]) values
(1, 'Baby Product'),(2,'Adult Product')
insert into #ProductDescription(ProductId, DescriptionId) values
(1,1),(1,2),(2,2),(3,1)
You can link them up like this:
select P.id, P.[name], P.price, D.[Description]
FROM #Product P
INNER JOIN #ProductDescription PD ON PD.ProductID=P.id
INNER JOIN #Description D on D.id=PD.DescriptionId
WHERE D.[Description] = 'Baby Product'
Results:
1 Product1 100.10 Baby Product
3 Product3 300.30 Baby Product
You can reconstruct the "entire description" for the product using FOR XML PATH e.g. (possibly not the best query tbh)
;WITH CTE AS
(
select P.id, P.[name], P.price, D.[Description]
FROM #Product P
INNER JOIN #ProductDescription PD ON PD.ProductID=P.id
INNER JOIN #Description D on D.id=PD.DescriptionId
),
CTE1 AS
(
SELECT id, [Description] =
STUFF((SELECT ', ' + CAST([Description] as nvarchar)
FROM [CTE] p1
WHERE p1.id = p2.id
FOR XML PATH('')), 1, 2, '')
FROM CTE p2
GROUP BY id
)
SELECT * from CTE1 A WHERE id IN
(SELECT ProductId FROM #ProductDescription PD INNER JOIN #Description D ON PD.DescriptionId = D.id WHERE D.[Description] = 'Baby Product')Results:
1 Baby Product, Adult Product
3 Baby Product
Performance from your original schema or my suggestion could potentially be improved by the use of appropriate indexes
Submit an article or tip