As a community, we've often done well at providing standards for the way we do many aspects of database development, administration and management. There have been other areas where it has been too difficult to gain a consensus. SQL Naming conventions are one of these. Layout is another. Both are important for teamwork. I hate having to maintain databases that are poorly laid out, or have badly-named tables, procedures and functions.

Many organisations and individuals publish their own standards for SQL Server. Some of the ones I've read are excellent. Some are so rigorous that they allow some chores such as the creation of foreign key constraints to be automated. In general, though, they tend to find it difficult to advise on a standard naming convention for SQL Server objects, which includes everything listed in sys.objects, from Aggregate CLR functions (AF) to Extended stored procedures (X).

There have been attempts in the past. Joe Celko was courageous enough to tackle the topic in a book. Probably the most widespread attempt was the use of prefixes in 'reverse-Hungarian' notation. This wasn't a considered move but was due to two unrelated causes. Firstly, some system procedures and functions were given prefixes "sp_", "xp_" or "dt_", to signify that they were 'special' and should be searched for in master first. Secondly, use of the tbl_ prefix, for a table, often called 'tibbling', came from databases imported from Access. Unfortunately, this was an Access convention inherited from Visual Basic, a loosely typed language. There are established codes for SQL Server and a table is U, not tbl. However, SQL Server objects are unlikely ever to need a type-prefix because you will never be in any doubt as to the type of object you're using, if you know its name, schema and database; its type is there in sys.objects. Also, it is obvious from the usage.

Going beyond the prefix, I was very unsure how to name procedures and functions until PowerShell firmly set out its verb/noun convention, using standard verbs. It was well thought-out even before they went public with it. These standard verbs have been accepted and used without any criticism or issues. Obviously, our SQL verbs are different, but surely, we could do a similar exercise to create a meaningful naming convention? After all, we Get_InvoiceDefaults, New_PreparedStatement, Find_SimilarWord, and so on.

What seems important to me is that we should respect the idea of SQL being an intelligible language, based on written language. This would suggest that function names should fit into the semantics of the SELECT sentence. If we have a function that capitalises a sentence, i.e. makes the first character a capital letter if the word is longer than three characters (MLA), then you'd call it 'capitalized()'. Procedures would be verb-noun names of tasks, since they are executed.

I gather that there are many style guides for naming conventions out there. Shouldn't we as a community, be talking a lead by making things easier for anyone wrestling with SQL coding standards, by coming up with templates that organisations can adopt?

Phil Factor