Converting an EAV design to sparse columns and populating

One of the uses for sparse columns will likely be replacing entity-attribute-value designs. In an EAV design, the "sparse attribute" values are represented by a separate table. The attribute value can either be declared as (n)varchar or sql_variant. An example I've always used is the hardware store that wants to keep track of its products […]

More about sparse columns and column_sets

I'm still getting used to the new sparse column feature in SQL Server 2008. I'd just read in the BOL definition of ALTER TABLE that you can add a column_set to an existing table. I was converting a sample app from an EAV (entity attribute value) design to sparse columns. I used the existing "attribute-value" […]

Sparse tables, sparse columns, and XML

I've been talking with folks for (it seems like) a long time about modeling sparse attributes in a relational database. Seems like I run into a new design where there's a need for sparse attributes every few weeks. If you remember the "hardware store" example (where each new sales item may have unqiue properties), that's just […]

And the EAV winner is …. sparse columns

Many of you have already heard the "hardware store" story. What's the best way to model products in a hardware store, where new items arrive at the hardware store each day. Each item has a "short list" of similar properties (e.g. UPC, price) and a long list of dissimilar properties (e.g. paint has color, type, […]