SQL: Don't use CTE in the name of your CTEs

SQL: Don't use CTE in the name of your CTEs

I used to be able to tell when someone moved from working with Access databases and arrived at SQL Server. What was the tell-tale sign?

All their tables had names like tblCustomers.

We’ve pretty much stopped people doing that, and we also have pretty much stamped out Hungarian Notation, at least in SQL Server.  We don’t use variable names like @intCreditRating, although there are still a number of people that I see clinging to Hungarian Notation when they work with SSIS, particularly in their .NET code. In their code, you’ll see values like strCustomerName and so on. The irony is that .NET developers long since moved past doing that, yet people writing .NET code in SSIS still do it.

Schemas

I’m not a fan of prefixes and suffixes where the prefix or suffix denotes the type of the object. Even in data warehouses, I still see examples with lots of tables with names like DimCustomer, DimCategory, FactInternetSales, etc. If I see a whole lot of tables with the same prefix, it’s clear that the developer is trying to categorize or group the tables by using the prefix.

And that’s one primary use case for schemas.

Don’t name tables dbo.DimCustomer, dbo.DimCategory. If you feel a need to do that, at least make them Dimension.Customer and Dimension.Category, and so on.

Views

Another hold out area in SQL Server seems to be views. Many people use names for views like vCustomers. Again, this isn’t a great idea. Name objects for what they contain or represent, not for how they’re implemented. As maintenance occurs, this can go very wrong with this. I’ve seen tables called vCustomers and views called tblCategories. How did this happen? They’ve changed how the object was implemented and weren’t game to change the object’s name, to avoid breaking existing code.

That’s then worse as you have a prefix or suffix that’s misleading.

Even Microsoft fell foul of that in SQL Server itself. Originally, System Stored Procedures all had an sp_ prefix. And System Extended Stored Procedures all had an xp_ prefix. Probably sounded good when they did that.

But take a look at the list of System Stored Procedures now. There’s a bunch that have an xp_ prefix.

And same for System Extended Stored Procedures, there are some that have an sp_ prefix.

That’s a mess.  How did it happen? Again, they changed how the object was implemented but were not prepared to change the name.

CTEs

So that brings me to the topic for today. I see so many times that people writing code with CTEs (Common Table Expressions) who use the word CTE in the name of the expression.

Please don’t do that.

Worse, the Microsoft documentation pages also do this.

Expressions should be named for what they return, not for how they are implemented. Notice that SQL Servers own functions are expressions yet they don’t put the type in the name of the functions.

We have DB_NAME() not sysname_DB_Name(), and DB_ID() not int_DB_ID().

Don’t then use names like Sales_CTE or CTE_Sales, etc.  That’s no better than someone putting tbl as a prefix on all their table names.

Instead, use names that represent what the CTE returns. If the CTE returns customers who have low credit ratings, then the CTE can be something like LowCreditRatingCustomers. Please don’t just call them Customers_CTE.

2020-05-22