RE: Querying EAV data – SQLServerCentral

SSC Guru

Points: 1003853

August 30, 2012 at 7:59 pm

Evil Kraig F (8/30/2012)
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.
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.

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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)