In Shakespeare, "That which we call a rose by any other name would smell as sweet." But although people might still be able to recognize an object when its name changes, SQL Server can't. A name, in the database sense of the word, is a string of characters that identifies an object—such as a table, a column, or a stored procedure—to the database and to users. When that string changes, your database thinks it's looking at a different object. And in the context of your database, users also might think they're looking at a different object. So it's important to establish and stick to a naming convention for your database objects.
In "Seven Deadly Sins," November 2002, InstantDoc ID 26727, I told you about some of the dangers you face when you don't use a naming convention for your databases. Databases evolve, and the names of database objects evolve too. Over time, naming inconsistencies can become so prevalent that they inhibit productivity and cause confusion when users try to access data. This month, I give you examples of my favorite naming conventions and hints about how to handle global name changes in your databases and in the programs that touch those databases.
A good, clear naming convention makes database objects easier for you and SQL Server to recognize and manage. If you have a good naming convention, you won't find yourself struggling to understand what a table named ILOrderMod, for example, is doing in your database. In addition, good names help you avoid confusion when you or members of your staff are writing programs. Did you mean to write a stored procedure for table prof_svc, prof_svc_src, or svc_src_prof? If you have a good naming convention, the table names will be clear, concise, and meaningful to everyone and everything that uses them.
Within your environment, all objects of the same type (e.g., all tables, all views, all stored procedures) must have unique names. SQL Server 2000 won't let you duplicate names among tables, views, and other objects such as constraints, defaults, and primary keys.
SQL Server uses a multipart naming convention for each object within each database. When a DBA creates a table named MyTable on a database called MYDB, the full name of that table is MYDB.dbo.MyTable. If the table is a user table and you don't use its full name in a query, SQL Server resolves the name by checking the local database for a table that has that name. If user Sam creates a table named MyTable in the MYDB database, his table's full name is MYDB.Sam.MyTable. To SQL Server, these two tables have two different names—the full names differentiate the tables.
Two instances of SQL Server can't have the same name if they're on the same network. If you deploy a distributed database solution on linked servers, you need to ensure that no two tables (or a table and a view) on the linked servers have the same name. If you find that you have duplicate object names, you have to use a four-level, fully qualified name for each object (e.g., AccountsSvr.Accounting.dbo.Expenses). On the positive side, using a fully qualified name for each object clarifies what you're talking about; you can think of this practice as a method of code self-documentation. Also on the positive side, a fully qualified name reduces performance overhead somewhat because SQL Server doesn't have to resolve names when it processes a query. But on the negative side, fully qualified names involve a lot of typing and an increased opportunity for typing mistakes.
Any naming convention you choose should produce object names that are descriptive, concise, and easy to manage. In SQL Server, you can create names as long as 128 characters. If you're working for a company that uses a heterogeneous database environment—that is, you integrate data and applications from different database platforms such as SQL Server, Oracle, and DB2—you have to use a more restrictive naming scheme than you do in a SQL Serveronly environment. For example, no platform other than SQL Server (and the Microsoft Access Jet Engine) supports spaces in object names. So in a heterogeneous environment, you'd choose a naming convention that excludes spaces so that your names are compatible across all your platforms. If you're faced with implementing cross-platform compatibility, you can find information about the lexical attributes for each of the three major database platforms in Peter Gulutzan's article "SQL Naming Conventions" at http://www.dbazine.com/gulutzan5.html. One of the article's tables lists the maximum lengths of names for some of the most common database objects, and another table lists the characters that various platforms allow for their database object names.
Gulutzan's article also lists some naming conventions for different database platforms. For some of these objects, I suggest that you add the letter u to the prefix to distinguish your user functions or user stored procedures from those that are part of the database management system. For example, Microsoft suggests using the prefix fn_ for function names, sp_ for procedure names, and trig_ for trigger names. But I suggest using ufn_, usp_, and utr_, respectively, because if you create a user stored procedure with a name such as sp_myproc, SQL Server will think that sp_myproc is a system stored procedure and search the master database first. The erroneous search will result in diminished performance, and SQL Server might even execute the wrong stored procedure if you've given your stored procedure the same name as a system stored procedure.
Prev. page
[1]
2
next page 
