For some reason I decided to re-join a discussion in the SQL community from way back in 2012.

Karen started it, Thomas ran with it, and I decided I had to throw my 2 cents into it as well.

The short version: it’s hard to know what you’re looking at in database code (read: SQL, stored procs) when your tables don’t start with ‘TBL’ or your views don’t start with ‘VIEW.’

For example:

SELECT stuff, more_stuff, yet_more_stuff
FROM bit_bucket

So, is ‘bit_bucket’ a table, a view, a synonym, a materialized view, or what?

One camp would say you need to name that object, ‘TBL_BIT_BUCKET.’

That seems weird to me. But then I’m a weird guy.

As a tool geek, I’m used to have really easy ways of finding out what I’m looking at.

I like the ctrl+click drilling down into the object method the most.
I like the ctrl+click drilling down into the object method the most.

How to do those 3 things in blog-format:

Note, if you do the ctrl+click or the pop-up DESC, if you switch to the info tab, it will tell you what’s what.

What is it, who owns it?
What is it, who owns it?

So for me, I don’t see a need to add labels to my object names.

And if I’m in a command-line interface, and I’m doing serious coding, I can always use the Oracle DESC command to see what’s what.

If you’re curious about the debate, here’s the original blog post from Karen in 2012.

If you’re curious about what those little red pictures are in the screenshot above…

I was having some fun with how to display NULLs in SQL Developer, thought, NULL, that rhymes with skull, and I like to see my NULLs in red…RED SKULL!

I'm pretty sure Karen and Thomas have a lot to say about NULLs too...
I’m pretty sure Karen and Thomas have a lot to say about NULLs too…
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

7 Comments

  1. Nice detail about the “red skull”, but I have to ask: This modification has any effects (by exporting the data to Excel or any other destinations)?

    I would like to add it, but, well, I have this precaution…

    Also, in MS-SQL “or SQL Server”, where you put the cursor pointer “just to be clear” on the name of a table, for example, the IntelliSense shows the “schema” of the object – this case, the table.

    For example: if I have a table called “HR.EMPLOYEES”, MS-SQL will show the popup like:
    “myDatabase.HR.EMPLOYESS” – where in this case “HR” is the schema owner of the table in the “myDatabase” database.

    This feature would be great for those who come from MS-SQL and have to work in SQL Developer.

    Again, just sayin’ 🙂

    Thank you, Jeff.

  2. I boil it down to how OCD you are and are you on meds for it. No I see no problems if people use it. And not using it is not a problem for me. Does it make it ding bat proof sure. Does it matter no. Am I anal retentive… nope. I firmly believe it comes down to the standards at your place of work. I.E. Consulting right now on the side. The main guy there began all tables with tbl… So I am using it, would I have used it previously. NOPE.. Will I use it in other jobs probably not, but thats the standard they chose. Thats just my two cents.

    • Ha! Meds..hadn’t considered that. I wonder how long ago the ‘main guy there began all tables with tbl’ set that standard. And if he would have done it again if started today. Better software AND meds out there now.

  3. The red skulls are, indeed, awesome.

    It’s not just tools that lead to the metadata stuffing. Often times a company will insist on a design standard. A common standard for many programmers is Hungarian notation. This experience gets transferred into database design.

    I think this is the result of the “none of us is as dumb as all of us” groupthink that results from 35 people sitting around a table all trying to come to an agreement on a standard.

    And as long as I see people adding “sp_” to their stored proc names, then I’m going to say that “tbl_” is just fine as well. Is it needed? Nope. Does it help you quickly tell what the object is? Yep. Does it hurt performance? No.

    At the end of the day, just use what works for you.

    • makes things hard to read, harder to type…but I’m with you, mostly

      the sp_ thing – i kinda like that in the sense when you hear someone say sp_awesome, you don’t have to explain that you’re talking about program you wrote that does awesome things

    • I fear folks that write SQL who don’t understand NULL. Especially ones that write SQL that power reports that generate my credit scores…

Write A Comment