SQL: How many tables is too many in a SQL Server database?

I spend a lot of time with developers and with ISVs (Independent Software Vendors). Often, I come across really weird table designs. A common problem is the creation of tables that hold many different types of objects.

If I look at one of your tables, and ask you what it holds, your answer shouldn’t start with “It depends”.

A worse design is one where the same discussion happens about columns. If I ask you what is held in the ObjectID column, and you say “that depends upon what’s in the ObjectType column”, you’d better be building some sort of utility, not a relational database to support an application. There are so many downsides to this type of design but that’s a topic for another day.

Today, I want to talk about the table to rule them all.

[caption id=“attachment_2482” align=“alignnone” width=“521”] Image by Andy Omvik[/caption]

This is a table that holds many different types of entities. When I ask developers why they’ve done this, there are many reasons, but the one that I dislike the most is:

“We wanted to reduce the number of tables”

There’s usually no great answer to the next question, which is why?

[caption id=“attachment_2806” align=“alignnone” width=“367”] Image by Ken Treloar[/caption]

If the reason is that you can’t find them easily, I’ll bet you aren’t using schemas effectively. Schemas can work just like folders in Windows. In the same way that you wouldn’t keep all your files in one folder in Windows (or I presume not), you shouldn’t keep all your tables in the same schema unless there aren’t many.

An easy test for this is the presence of prefixes. If you have a bunch of tables with the same prefix, chances are you’re using the prefix to group them. That should be a schema instead. (BI people: I’m looking at you with all your DimSomething and FactSomething tables too).

If the reason is that you think the limit on tables isn’t that high, you’re just wrong. The number of tables is limited only by the number of database objects, currently 2, 147, 483, 647.

A couple of hundred tables isn’t going to make a difference to anything except the clarity of your data model.

2018-03-12