EAV Can Be Great In SQL Server, But It Depends On How You Query It

Valued

EAV styled tables can be excellent for certain data design patterns, particularly ones with a variable number of entries.

Some examples of when I recommend it are when users are allowed to specify multiple things, like:

• Phone numbers
• Physical or email addresses
• Contact names

This is a lot better than adding N number of columns to a table, especially when either most people won’t use them, or it adds artificial restrictions.

For example, if you have a large table that was designed 10 years ago, you’re not gonna rush to add a 3rd phone number field to it for a single customer. Changing tables like that can be painful, depending on version and edition of SQL Server.

Careful

Where you need to be careful is how you design them. One particularly rough spot to end up in is with a table like this:

CREATE TABLE 
    dbo.eav
(
    entity int,
    attribute int,
    int_value int,
    date_value date,
    short_string_value nvarchar(100),
    long_string_value nvarchar(max)
);

While it does make data type consistency easier, I have to wonder about the wisdom of making the values “good” for searching. Certainly, indexing this table would be aggravating if you were going to go that route.

A design that I generally like better looks like this:

CREATE TABLE 
    dbo.eav
(
    entity int,
    attribute int,
    value sql_variant
);

While the sql_variant type is certainly not good for much, this is a proper time for it, particularly because this data should only be written to once, and only read from after. That means no searching the sql_variant column, and only allowing lookups via the entity and attribute.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.