Best practices for redesigning a wide table with flexible schema

Question

I'm currently working with a large table that contains several hundred different types of records (with around 100 million rows in total). To fit all of these types of records into the same table, it has ~400 nvarchar(1000) "flexible" columns that are either filled with some form of data or left null, depending on the record type (specified by a typeId). Most record types don't use anywhere near that many columns and so are mostly null, but a few of them do. Users are able to add or modify record types through the system's UI, and can define which fields are stored in which columns.

Most queries on this table are on a single record type at a time. The queries may filter or sort on any of the flexible columns in use by the record type, but some are much more likely to be filtered/sorted on than others. However, given that the same column might be important/unimportant/unused depending on the record type, there are no indexes on any of the flexible columns.

I'm not sure why this design was originally chosen, but I have been considering different options for a redesign, as it is not very performant. Furthermore, I'm unable to make any updates to the current table schema, as this causes SQL Server to attempt to verify that each row remains within the row size limit, and this process takes too long to feasibly complete.

Some options I've considered so far:

1. Combining all flexible columns into a single nvarchar(max) column that contains record data in JSON format. In a test, this resulted in generally slower queries, seemingly due to the overhead of querying with SQL Server's JSON functions. It also ballooned the storage size of the table, due to all the extra characters needed for JSON objects/properties, and because the table could no longer effectively use compression.
2. Storing one record field/value per row in a new table, which I see from other questions is usually called EAV. In a test, this also resulted in slower queries, due to the additional joining required. It also resulted in much slower inserts, as it seems inserting many small rows takes noticeably longer than one large row.
3. Creating a new table for each record type. This has not yet been tested, but I'm unsure if it would be wise to create so many tables in SQL Server for this data. It would also present challenges in maintaining these tables, given that record types can be added or modified by users (I suppose it might involve dynamic SQL to add tables/columns, but I'm unsure how removing columns would be handled without losing historical data). However, it would have the huge benefit of enabling the use of proper indexes, as well as column types that better reflect the data.

I'm hoping to get some feedback on best practices in handling this kind of data, and whether option 3 is the right direction to explore next, or if there are better options I haven't yet considered.

Answer 1

As others have hinted at, having some more concrete information about your model structure and your application's use cases may be helpful in providing a more meaningful answer here.

But yes, in general option 3 is usually most suitable of a design pattern in a relational database management system (RDBMS). It will naturally help you normalize your schema which should generally result in improved performance and maintainability as a byproduct.

Currently your single wide table is a very denornalized implementation, and likely has worse maintainability, since any structural change needs to be applied to every row, even when a majority of the models stored in that table are unrelated to the change being made.

Also when you say:

It would also present challenges in maintaining these tables, given that record types can be added or modified by users (I suppose it might involve dynamic SQL to add tables/columns, but I'm unsure how removing columns would be handled without losing historical data).

These challenges already exist just the same whether you have a single table to store all models or multiple tables broken out per model. So that's nothing new being added by option 3. The only difference now is in the application you just need to provide a list of all the models (e.g. a dropdown) so that the user can choose which one to modify.

The EAV pattern is actually considered an anti-pattern by most, but in my opinion can be useful under the right circumstances too. I have a feeling your test implementation of it wasn't architected as best as it could've been, since joining and inserts shouldn't be much noticeably different in performance from your existing implementation. But hard to say without a schema in front of me. Either way, I think EAV doesn't fully solve your issue either (as I imagine you still had all your core models stored in a single table still).

As mentioned in the comments, since you already have your models identified by typeid then the different types are probably great starting candidates for a separate table for each.

Answer 2

There are a couple of things you could try to improve performance that may give you some breathing space while you figure out a new design and rewrite the code.

Filtered Indexes

Normally an index would reference all rows in a table. It is possible to limit the rows indexed by adding a WHERE clause. I'd suggest having each index apply to only one type and then index it as you would a typical well-normalised table i.e. focus on columns used in predicates, joins, aggregates and sorts. If types have values 1,2,3.. and generic columns are named col1,col2.. and we determine that col1 is particularly important for type=1 the index will be something like

create nonclustered index T1C1
on dbo.GenericTable(col1)
where Type = 1;

Now a query like select .. from dbo.GenericTable where Type = 1 and col1 = 'whatever' should be able to use index T1C1 to produce a more efficient plan. There are, unsurprisingly, caveats of which here are a couple are examples, inter alia.

Partitioned Views

Moving the data to type-specific tables and re-writing the application all in one go could be too much work. A partitioned view would allow refactoring the data while maintaining the programming interface to the application. It also surfaces the new schema so the application rewrite can proceed.

Let's say each type gets its own table so type 1 is moved to table dbo.T1 and type 2 to dbo.T2. Further, let's suppose type 1 uses columns 1 and 2 from the current generic table and type 2 uses columns 3 and 4 only. The view will be something like

create view dbo.Generic_Table -- the old table's name
as

select
    1 as Type, C1, C2, NULL as C3, NULL as C4
dbo.T1

union all

select
    2 as Type, NULL as C1, NULL as C2, C3, C4
dbo.T2

A partitioned view is constructed in such a way that it is unambiguous which base table provided any row returned from the view. There are a number of conditions on the schema to make this work. They are documented in the link above. This makes the view writeable and the application can continue to insert rows as before.

The underlying tables are just normal tables. They can be indexed however best suits the use cases. Application code can be refactored incrementally to address these new base tables instead of the generic table. Once migration is complete the now-superfluous columns and object can be dropped.

View Triggers

A view can have triggers. Triggers can execute a broad range of TSQL. Having created separate tables for each type a view can be created to replace reads against the current generic table. This view will have a trigger for each DML statement which directs writes to the new correct schema.

Of course partitioned views and view triggers require the base table schemas to be somewhat static. It is possible to have application code add and remove columns to base tables at run-time. The performance implications may be more than your users are willing to bear, however.

JSON

You say you have had no luck with JSON. I think there are a few more things you could try.

First, vertically partition the table with only the PK and JSON in the new table. Then create a clustered columnstore index (CCI) on this JSON (see here). By keeping the JSON physically separate it will have no read impact on queries which do not use it.

Second, pull specific values from the JSON to persisted computed columns (here). Create secondary B-tree indexes on these computed columns, filtered by the type that holds them.

XML

XML is handled differently to JSON. It is a separate data type. Documents are shredded and stored internally in a binary format. Indexes can be declared directly on the XML column rather than on computed extracts. As with JSON, I would suggest not mixing XML and 400 nullable columns in the same base table.

Sparse Columns

There is an optimisation for tables which have many columns, most of which are NULL. I've never used it, nor do I know of any application that has. It does seem to be a good match for this use-case, however.