T-SQL 101: 10 What are Schemas in SQL Server?

In the last post, when talking about querying a table, I mentioned that you should always include the name of the schema when you refer to a table. This is called using two-part names. But what are schemas in the first place?

In different database engines (e.g. Oracle, PostgreSQL, etc.), schemas are implemented and work differently i.e. there isn’t a consistent story across the industry. In SQL Server though, the closest analogy that most people would be familiar with, is a folder where you group things that are related in some way.

Schemas provide a way to group together tables, views, stored procedures, views, and more. Often this is done to simplify security, because security can be applied at the schema level, but in the more general case, it’s just a way to group related items together.

For me, a sure sign that you should be using separate schemas is if you name your tables with a prefix. See the list of tables here:

Notice how a bunch of tables have the same Dim and Fact prefixes. The designer has used a prefix to try to group together the tables for some reason. In this case, it’s to separate dimensions and facts.

Rather than doing this, it’s better to use a schema to group the objects, and to have the object named after what it holds. Unfortunately, many BI practitioners still do this, based on ideas that came before we had separate schemas in SQL Server 2005.

So instead of dbo.DimCustomer, I’d rather see Dimension.Customer and instead of dbo.DimAccount, I’d rather see Dimension.Account. Similarly, instead of dbo.FactInternetSales, I’d rather see Fact.InternetSales, and so on.

Regardless, as I mentioned last time, you should always use two-part names when referring to objects in SQL Server.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-03-25