SQL: Should we use schemas other than dbo in SQL Server databases? (Part 1)

In violation of Betteridge’s law of headlines, the answer is yes. When designing SQL Server databases, there are several reasons why you should be considering using schemas and not just having all your objects in the dbo schema.

This is a good example though, of where good advice regarding SQL Server dates. In SQL Server 2000 days, it was sound advice to keep all your objects owned by dbo (and at the time automatically part of the dbo schema), to avoid ownership chaining issues. That’s because ownership and the containing schema were one and the same concept.

There are two basic reasons why I think you should be using schemas.

Folder-style grouping

The first (and most straightforward) is as a container. I don’t imagine that in your Windows system, you have every file in a single folder. You use folders to group related files. Schemas in SQL Server are closer to the concept of folders than they are to the way schemas are used in other products like Oracle.

For me, the easiest test is to look at your file names. If you felt the need to have a prefix on your table names to create an artificial grouping, schemas are what you should be using instead. As an example, look at the names of the tables in the AdventureWorksDW database:

Note that most tables have a Dim or Fact prefix. Now this is a relic of how many of the BI people did table designs back in SQL Server 2000. Most database developers have moved on from prefixes, but many BI people seem to still be stuck in the past on this.

When you see a whole bunch of tables with the same prefix, to me, that screams “I need schemas”. How much cleaner would this be if the table names were:

Dimension.Account Dimension.Customer Dimension.Product Fact.ResellerSale

and so on?

I hear people say “ah but then I have to use the schema name all the time, whereas with dbo, I don’t have to”. But if you’re developing for SQL Server, the right advice is to always use two part names anyway, for a myriad of reasons.

Importantly, this allows the actual table names to just reflect what’s in it, not what type of table it is.

For one extra note, I’m not talking here about “data type” prefixes like tblCustomer or tblDate, vNewCustomers, etc. While many people still use those (I think mostly older Access users), they’ve always been a poor idea. I’ve been working on sites where, during maintenance, they’ve replaced tables with views, and then you have views called tblDate, etc.

It’s surprisingly easy to end up with incorrect prefixes when you do this. Ask Microsoft themselves. In SQL Server, system stored procedures all started with sp_ and extended stored procedures all started with xp_. Simple enough? If you think so, check the current list of both types of procedures. Both lists now have a mixture of prefixes. Why? Because when the way a procedure was implemented was changed, they weren’t prepared to change the name of the procedure.

Prefixes like these can lead to messes.

One current pain point though, is that Power BI currently doesn’t have an option for stripping off schema names on import. It prepends the schema name on the table. That’s a pain in the neck and I’ve been pushing to get an option to change that.

In my next SQL post, I’ll talk about the second reason for using schemas.

2018-09-10