Given that they require schema modification locks, why are sparse columns considered an alternative to EAV?

Question

Pro SQL Server Relational Database Design and Implementation: Best Practices for Scalability and Performance is a very reliable source of good database design ideas. As an alternative to Entity-Attribute-Value it suggests using dynamic SQL to allow your users to add new sparse columns to a table.

To me, this idea stinks. Adding new columns requires a schema modification lock. This is a very serious lock and I would rather my users not have the ability to obtain it.

Is there any property of sparse columns that makes allowing users to take such a serious lock not as terrible as it would be for other types of column?

Answer 1

While commendable, your concern regarding adding and dropping columns is mostly misplaced.

Adding a nullable column is a metadata-only operation: that is, it only entails minor changes to the database's definition of the table in internal tables, and does not require rewriting the clustered and all the non-clustered indexes. This means that such a modification is very fast. Likewise, dropping a column is also metadata-only, as the storage engine will simply ignore that data until each page is eventually overwritten without it.

You can see this in action in this fiddle. SET STATISTICS IO ON is used to show reads of the table itself, and you can see that it only happens on the addition of a non-nullable column.

---

Your only real concerns should be:

• Possible blocking chains.
  For example, a long-running SELECT holds a Sch-S lock on the table. The ALTER tries to take a Sch-M lock and waits. All other SELECT and modification queries then pile up behind it, waiting on their own Sch-S lock which they can' take.
  Unfortunately, this can't be avoided using WAIT_AT_LOW_PRIORITY as that's not yet implemented for this type of ALTER. Your best bet is to put the following before your ALTER:

  SET LOCK_TIMEOUT 10000;  -- milliseconds
  

• All views and procedures which access this table will need their metadata refreshed. You can use the following script, which gets all such objects and refreshes them:

DECLARE @sql nvarchar(max) = (
    SELECT STRING_AGG(N'
EXEC sp_refreshsqlmodule N''' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ''';',
      '')
    FROM sys.objects o
    JOIN sys.schemas s ON s.schema_id = o.schema_id
    WHERE o.object_id IN (
        SELECT ed.referencing_id
        FROM sys.sql_expression_dependencies ed
        WHERE ed.referenced_id = OBJECT_ID(@yourTable)
    )
      AND o.type IN ('P', 'V', 'FN', 'IF', 'TF', 'TR')
);

PRINT @sql;

EXEC sp_executesql @sql;

Answer 2

To me, this idea stinks. Adding new columns requires a schema modification lock. This is a very serious lock and I would rather my users not have the ability to obtain it.

Why not? (Sure I can list out a bunch of theoretical reasons but...) Regardless if your users make a schema change or you as a developer make those same changes, then a schema modification lock will be required. Most use cases where end users have this access are in a limited world where they'd only be shooting themselves or their team in the foot. In a multi-tenant environment, there should be no crossover, and tenants should be separated anyway.

Further application design measures can be implemented to minimize the hurt from end users punching themselves in the face too, such as delayed execution of the schema changes (e.g. a scheduled queue to run the generated SQL off-hours) and limiting access to this ability to admin privileged users of the application.

Is there any property of sparse columns that makes allowing users to take such a serious lock not as terrible as it would be for other types of column?

Again, I don't believe so, and the solution should be an architectural solution. Aside from what I mentioned above other design choices that can mitigate the risk of this is either have a preset amount of columns of varying data types that individually get mapped when the end user needs a customized column, or have a separate table, e.g. ObjectNameExtended where new columns are added dynamically by the users so that the schema modification lock only affects customizations and not the native application at least.

---

Also, as Erik Darling pointed out, Sparse columns have a set of limitations that can be pretty lame, such as preventing you from compressing any tables / indexes that they're part of:

Sparse columns are incompatible with data compression. Therefore sparse columns cannot be added to compressed tables, nor can any tables containing sparse columns be compressed.

Also, from a maintenance announcement on this network:

sparse columns feature prevents us from adding new non-null columns with a default value as an online, metadata-only change.