Database denormalization: multiple rows into single column. How bad is it?

Question

We have few join-heavy queries to our SQL Server 2008 database and in an attempt to optimize them I thought of having a view that would produce this

OrderID     Number    Traveller
1           102       1015;John Doe|1016;Jon Appleseed

out of

OrderID     Number
1           102


TravellerID    OrderID    FullName
1015           1          John Doe
1016           1          Jon Appleseed

My question is: how bad is it from a DBA's standpoint? On the one hand, it gets the job done, but this solution really bothers the purist side of me.

Do you plan to further use these views by joining them to other tables (or - the horror - to similar views) using these columns? — ypercubeᵀᴹ, Jun 14 '13 at 11:46
How heavy are your join queries? Do you have proper indexing/statistics? If so you should have no trouble with the joins, as opposed to the non-relational-like view — JoseTeixeira, Jun 14 '13 at 12:04
What will the results be used for? If it's for some type of report, perhaps what you need to do is cache the result and invalidate when the data source changes. — Jon Seigel, Jun 14 '13 at 14:52

score 0 · Answer 1 · answered Jun 14 '13 at 13:39

It depends on how the views are going to be queried. If the purpose is to do something like

SELECT OrderID from dbo.vw_Orders WHERE Traveler like '%Jon Appleseed%'

then you should NOT do this. If you are just using the view to return a list that will be parsed by your application, then it may not be so bad.

1 Answers1