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


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.


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:

    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:

    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.

7 thoughts on “EAV Can Be Great In SQL Server, But It Depends On How You Query It

  1. Would you not prefer dedicated phone number, address and name tables for the examples given? I find that the RI, data type and indexing benefits outweigh the pain of EAV.

    The one place I find EAV makes sense is when you’ve got a system (like a CMS) that end users need to be able to define fields in. Even then it usually turns into a horror show.

    1. I’m not sure how that would help. They’d all end up stored as strings anyway.

      When your solution might make sense is when mixed data types end up in the “V”, e.g. dates and numbers, etc. that make querying unsavory. And yeah, letting users customize anything ends up poorly. You hate to see it 🤭

    1. I used to do that across the board, but I ran into a weird situation where I needed to store strings with different collations, which sql_variant supports. Absent that, you’re right.

  2. sql_variant are great for this purpose…
    Except if you have to use SSIS.
    I found out toooooo late, but had to change to your first example to avoid sql_variant

  3. Peter Larsson has a different take on it that can be really useful:


    I saw him present this live, and the demo was really impressive. Article and comments on SQL Server Central:


    I’m having trouble getting the links inside the comments to work, but the schema should be downloadable.

Comments are closed.