I want to talk about some best practices concerning naming conventions. Most teams or organizations will come up with their own naming conventions for things. This post is not here to tell anyone how to define their naming conventions, especially for things outside of SQL Server. I want to talk specifically about things inside the database or on the SQL Server server.
- Don’t prefix objects with “sp_”: I considered not mentioning this one because everyone should know this by now. It is repeated time and time again by just about everyone who talks about naming conventions. However, I still see people doing this, so here we are. System stored procedures are prefaced with sp_ and you should not use this prefix. It will cause your server to have to do extra work in order to find your procedure, but more importantly, if there is a system procedure by the same name, you could end up executing the wrong one. Don’t do it.
- Don’t include names of included columns in index names: You may be thinking, what will this hurt? It doesn’t hurt anything. However, I recommend simply including a notation that there are included columns. In my experience, the queries will change the columns returned and the columns you need to include are likely to change at some point. You don’t want to have to rename the index every time you change the included columns. As a result, what I see happens when people include the included columns in the index name is that the name no longer accurately reflects what the index covers.
My preferred naming convention for indexes is: Prefix_TableName_KeyColumns_INCs. I use INCs to indicate there are included columns.
- Don’t use keywords or reserved words as object names: Keywords and reserved words generally appear in a different color in SSMS. If you create a table, column, or other object and it’s name is blue or pink or some color other than black, then it is a keyword or reserved word. Don’t use it as an object name. It may require using square brackets to distinguish it. And even if it doesn’t require it now, it could at some point in the future and suddenly your scripts may start failing. In fact, it wasn’t too long ago that people’s upgrades to a new version was failing because they had tables named “merge” in the database. When they added the “merge” operation to T-SQL, you could no longer use it as an object name.
- Don’t use spaces, special characters, or anything that violates the rules for identifiers: If the object does not conform to the rules for identifiers, they have to be enclosed in square brackets. Not only will this make the DBA hate you, it can cause failures if you forget to enclose it in square brackets.
- Don’t use acronyms that make the name indecipherable to anyone but you: I swear I got an email one day saying, “I think there is bad data in the sxy column in the p497zy table.” And they said that like it should be obvious what data in p497zyy.sxy should look like. Would you rather be tasked with fixing data in dbo.p497zyy.sxy or in dbo.Customers.PhoneNumber?
Remember, just because it is obvious to you, that doesn’t make it obvious to everyone. Someone else will eventually be tasked to dealing with your database objects and your code. Don’t make it harder to maintain that it already is.