I’m watching some Brent Ozar videos (like this one, for instance) and he suggests not prefixing tables with ‘tbl’ or ‘TBL’.
On the internet I found some blogs saying it adds nothing to documentation, and also that “it takes longer to read it”.
Questions and considerations
- It this really a problem? Because I’m prefixing tables with ‘tbl’ since my first dba job (the senior DBA told me to do that for organization).
- Is this something that I need to get rid of? I made some tests, copying a really big table and giving it the ‘tbl’ prefix, while keeping the other one without it, and I didn’t notice any performance issue.
Best Answer
I once had a table and it was shiny and beautiful. It held all the financial transactions for an organization. And then we started loading data into it.
In the current month, they can state and restate values as often as they want. In the final 10 days of a month, they'd restate numbers -> run ETL processing -> review reports several times a day. Once the month is complete, then the books are sealed and they can't modify values.
It's amazing how much financial data a financial services firm generates... Something we didn't realize with our test data set was the volume of data was going to make their month end procedures untenable. It took an increasingly long time to delete out the "current month's data" prior to replacing it with the new trial run.
We had to do something to make it faster for processing without breaking the uncatalogued list of "who knows what" that all depends on the MonthlyAllocation table. I decided to play magician and whip the tablecloth out from underneath them. I went old-school and used a Partitioned View. The data already had an IsComplete flag so I made two tables - each with contrary check constraints: MonthlyAllocationComplete, MonthlyAllocationInComplete
I then created the partitioned view with the same name as the original table: MonthlyAllocation. No process was any the wiser about the physical change we made to the database. No reports broke, none of the analysts with direct access reported any issues with that "table" before or after.
Cool story bro, but where you going?
What if they had a naming convention there, tbl_MonthlyAllocation? Now what? Do we spend lots of man hours going through every ETL, every report, every ad-hoc spreadsheet in the organization and updating them to use vw_MonthlyAllocation? And then of course all those changes go through the Change Board and that's always a quick and painless process.
You boss might ask: What's the reward to the company for all that work again?
The other option becomes we leave this view named as tbl_ and not spend all that time testing, updating and deploying code. Which becomes an amusing anecdote you explain to all the new hires, and those with short attention spans, that have to work with the database as to why you are inconsistent with the naming of objects
Or you don't double encode objects with redundant metadata. The database will happily tell you what is a table, what is a view, what is a table valued function, etc.
Naming conventions are good, just don't paint yourself into a corner with them.