THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Arnie Rowland

Discussion of issues related to SQL Server, the MSDN SQL Support Forums, the complex interplay between Developers and SQL Server Administrators, and our sometimes futile attempts to have a 'normal' life.

What’s in a Name?

Why do folks continue to use outdated and unnecessary naming conventions? Specifically, the ridiculous ‘tbl’ prefix on tables, and the equally meaningless ‘usp’ for stored procedures. I think that it is because that is what they see illustrated in books, magazines, white papers and on-line 'example' code.

 So the real question is really: Why do authors continue to follow outmoded and wasteful practices? I mean, if the characters (keystrokes) do not provide ‘value’, why invest the time and effort? Why continue demonstrating something that has little or no value? Are 'old habits' really that hard to change?

In my teaching, I often explain that all characters in an object name ‘should’ be meaningful; if a character does not add value, then it is a waste of time and effort. For example, if an object appears after the keyword FROM, it is very safe to assume that it is a data object (table, view, or table valued function.) Therefore, using the prefix ‘tbl’ (or ‘tbl_’) as a prefix to a table name is a complete waste of keystrokes (time and effort). However, It can be useful to use a suffix, such as '_vw' for VIEW, or '_tv' for a Table Valued Function. From context, it is almost always possible to identify the object being referred to as a data object. Three (or four) extra characters that have to be typed every time the table is referenced in both database and application code. What a waste.

The same goes for stored procedures. Contextually, it is possible to know that an object is a stored procedure, therefore using any prefix is a waste of keystokes (time and effort) that does not provide value. Granted, for code objects that are distributed with applications, it may be sagacious to have a somewhat unique prefix in order to reduce potential naming conflicts with the clients’ use of the database. But in the absence of a ‘third party' products, I contend that stored procedure prefixes, especially the somewhat lame ‘usp’ is a complete waste of effort.

I often work with very large projects, with very large development teams. It is not at all unusual for a single database to have 1000, 2000, even more, stored procedures. Without a sensible naming convention, folks will unintentionally duplicate effort. In one project, as we were nearing deployment, I found as many as 14 stored procedures that had the exact same functionality –just variations in naming. I ended up removing over thirty percent (30%) of the stored procedures (and forcing application code changes). In the ‘morass’ of thousands of T-SQL code objects in the source store, developers simply gave up attempting to find and reuse existing code because there was no consistent naming policy that allowed ‘finding’ what was needed with reasonable effort.

In a later post, I will make a case for selective and succinct ‘suffixes’ on certain objects –but most definitely NOT prefixes. I will also delve into naming conventions that seem to really work in any size environment.

What do you think about using naming 'prefixes'?

Published Thursday, October 23, 2008 1:13 AM by ArnieRowland



Jonathan Kehayias said:

I guess if this were a court trial, I would be guilty as charged, but only for preexisting code.  I used a 3 year old set of code in my PASS DBA SIG live meeting on Wednesday, and it sure enough had every stored procedure prefixed with usp_, every function was fn_ or udf_, and every view was vw_.  Old habits are not hard to break, but I don't see much use in going back to change things that I am not touching otherwise.

Anything I have been in volved in writing in the last 2 years however, has lost this naming convention.  I don't allow for procedures prefixed by sp_ as a firm rule in my environments, but I don't require a prefix on anything, just a meaningful name that if possible describes the purpose.

Definately something to think about since I am working to publish the code from my LiveMeeting online.  I guess it is an opportune time to fix this as an example/sample for others to follow.

October 25, 2008 8:30 PM

robboek said:

I agree with you for the most part. I don't like prefixes for tables or stored procedures. I do make an exception for views, I like to prefix them with a v, i.e. vCustomerInfo.

October 29, 2008 2:05 PM

drsql said:

Nicely put.  I try to avoid even the suffixes too.  Sometimes the word view might be in the name, to denote that it is a certain type of view (like yearEndSummaryView) but in most cases.

For code objects I use a convention of object$purpose.  The object is either a table, or a functional group of tables. Like for an insert proc for the salesOrder table:  salesOrder$insert.  This makes finding objects a lot easier than what I see a lot of people doing: insertSalesOrder. There could be 100s of insert procs, and I don't usually want to see them all together.

December 18, 2008 2:26 PM

Jake said:

I know this article is hella old, but I will say that those prefixes can save you a hell of a lot of time in intellisense, which for me is the main reason to prefix anything in code. Other than that, completely agree.

June 1, 2016 10:53 AM
New Comments to this post are disabled
Privacy Statement