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.

4 thoughts on “SQL: Don't use CTE in the name of your CTEs”

  1. For things like views I've seen it help me in troubleshooting and debugging issues to be able to clearly see that an object in a FROM statement is a view versus an actual table. For example, when I look at an execution plan that has references to objects I don't see in the query I'm reviewing, if I had known that an object in the FROM clause was a view and not a table, it might have saved me some time.

    1. I understand the thinking but current tooling will immediately show you it's a view if you just hover over it, in the same tools that you're looking at the execution plans in.

  2. I'm fully on board with eliminating hungarian-type notation. I still see it drifting over from weakly-typed languages. Using schemas to put SQL object types to really is what schemas are for (amongst other things). I just really wish you could do something equivalent to namespacing or hierachical organisation with schemas – so you didn't end up with either hungarian schemas (deptNameDimension.tableName, deptNameFact.tableName) or hungarian objects (deptName.dimTableName, deptName.factTableName) when you do schema-based security via AD.

    1. Hi Will, I agree that a 3rd level could be useful but I don't see that ever happening. Perhaps separate databases might work in your case. It is a namespace.

Leave a Reply

Your email address will not be published.