In the final post of
my series on naming conventions, I'm going to discuss some different ways to name some of the objects in your SQL Server databases. This topic alone could turn into it's own series, but for now I'm going to limit the discussion to naming tables, stored procedures and user defined functions. With all of these object types I have had two different naming constraints to deal with. The first one is where the development/data team has full control over the conventions used for naming all objects. Of course this is the perfect (in a developer's mind) world. The second situation is where the overseeing authority (client, IM department, etc.) or tool imposes restrictions. For developers, this sucks. For readability, this sucks (what the heck is stored procedure XYZ003 supposed to do?). Because your development efforts are being constrained by external forces, I'm not going to discuss work arounds or alternatives for specific situations. Unfortunately for you (and me on my current project), you have to live with it....or get another job. So with that, here we go.
First let's talk about Hungarian Notation. If you've read my previous posts on naming conventions, you'll know that I prefer Hungarian Notation is a number of different situations. This is not one of them. If you open Enterprise Manager (SQL Server 7 & 2000) or SQL Management Studio (SQL Server 2005) you will notice right away that all your tables, stored procs and user defined functions are grouped under one node in a treeview control. Prefixing your tables with the names 'tbl' or 'tb' or 't' does not help you group your items together any better. It doesn't help to make your T-SQL code to be any more readable. If it does neither of these things, why bother? All you're doing is clutter screens and code.
PascalCasing is the other naming convention that I've used for table names. It's pretty simple to explain this. All your SQL objects are named with PascalCasing.
There are one thing that you need to be aware of when naming some objects. First
*never* prefix stored procedure names with 'sp'. SQL Server sees this as a special value and will immediately look for the stored procedure in the Master database followed then by your database (this takes time and time is a huge part of relative performance). If you name your stored procedure with any other prefix ('xx' for example) SQL Server will looking your database base first.
The next thing I want to touch on is the fact that all of these objects will appear in T-SQL at some point. T-SQL has a pretty specific and orderly syntax to it. Table Names always appear after a FROM in a SELECT statement. Stored Procedures can't be called inline. User Defined Functions can be called inline in T-SQL. Because of this syntax these syntaxical orderings there are no compelling reasons to differentiate your objects based on a naming convention. Where they appear in the T-SQL will tell you exactly what they are.
Naming Stored Procedures is an interesting subject. I had a very long discussion, on the patio of a favourite local establishment, with a friend who made a very interesting argument on the verb and noun ordering when naming the stored procedure. Historically I've named my stored procedures verb-noun (i.e. GetCustomers or UpdateEmployees). This friend argued that he preferred the opposite syntax, noun-verb (i.e. CustomerGet, CustomerUpdate, EmployeeUpdate). His reasoning was that by using this ordering all the stored procedures related to one noun object (Customers for example) will appear grouped together in the treeview in SQL Management Studio. I'd never considered this, but when choosing your naming convention for stored procedure you will most likely be choosing between grouping them by functionality (verb) or domain object (noun). Since talking with him about this (and sobering up some) I've began to convert myself to the noun-verb syntax. I like knowing where all major operations on my domain objects are occurring.
My Choice
My choice for all SQL objects is PascalCasing, with a noun-verb ordering on those that need it.
posted @ Wednesday, July 19, 2006 7:53 PM