Lokesh Vij (8/30/2012)

---

What according to you would be the best way to query data. Every time I increase one attribute in my select clause, I need to add a join conditions.

Evil Kraig F (8/30/2012)

---

Create a view that will pivot the data for you and hook to that. Otherwise, you're basically stuck working with EAV as you describe to handle additional values as join conditions or to pivot it.

If you know your attribute names you can simply build it once (with further adjustments as necessary) as a pivoted view. Indexing the results may be in your best interest, but you will have to decide about the weight of the pivot updating for every change vs. speed of access to the rest.

Evil Kraig F (8/30/2012)

---

Create a view that will pivot the data for you and hook to that. Otherwise, you're basically stuck working with EAV as you describe to handle additional values as join conditions or to pivot it.

If you know your attribute names you can simply build it once (with further adjustments as necessary) as a pivoted view. Indexing the results may be in your best interest, but you will have to decide about the weight of the pivot updating for every change vs. speed of access to the rest.

Jeff Moden (8/30/2012)
Materializing the view as an indexed view may be the best bet for such a thing. Using a pivot or a Cross Tab to create the view without persisting the information would mean that the whole view would probably need to materialize before you could use any of the pivoted columns in something like a join.

CELKO (8/31/2012)

---

You are screwed; EAV is 10 orders of magnitude worse than even a bad non-normalized schema. Yes, you have to assembly each column with joins and all yiou can do is hide it.