Coding and database standards like a religion or politics. Some people are fanatics and others really don’t care whatsoever. Getting stuck between any combination of the right and left or those that don’t care can be entertaining and sometimes outright dangerous.

The HBO show Silicon Valley has a whole episode based on formatting (tabs vs spaces). But that’s an entirely different topic. For now I’d like to quickly discuss naming of objects.

CamelCase vs Using_Underscores

CamelCase naming is achieved by concatenating word together with each word starting with a capitol letter. Some examples of this are: CustomerOrders, UserPermissions, VenderAddresses.

I know some of you are already shaking your head at the fact that I used plurals for those objects. I’m not here to talk about that topic today so let’s put that on the side table and look at underscores.

Using the underscore naming convention each word would be separated by an underscore like the following:  Customer_Orders, User_Permissions, Vendor_Addresses.

These conventions should be used not only for tables and views but for everything, including columns.

Let’s look at the following table:

CREATE TABLE HumanResources.EmployeePayHistory

(    BusinessEntityID int NOT NULL,

RateChangeDate datetime NOT NULL,

Rate money NOT NULL,

PayFrequency tinyint NOT NULL,

ModifiedDate datetime NOT NULL

)

We need an index on RateChangeDate. Let’s see what that should look like:

CREATE NONCLUSTERED INDEX ix_ncl_EmployeePayHistory_RateChangeDate
ON HumanResources.EmployeePayHistory

(    RateChangeDate ASC    )

I like to separate the columns in an index name with underscores. I also like to start each index with ix_cl or ix_ncl depending on it being clustered or non-clustered.

Now what if this were created using underscores?

CREATE TABLE Human_Resources.Employee_Pay_History

(    Business_Entity_ID int NOT NULL,

Rate_Change_Date datetime NOT NULL,

Rate money NOT NULL,

Pay_Frequency tinyint NOT NULL,

Modified_Date datetime NOT NULL

)

The same index would be named as follows:

CREATE NONCLUSTERED INDEX ix_ncl_Employee_Pay_History_Rate_Change_Date
ON Human_Resources.Employee_Pay_History

(    Rate_Change_Date ASC    )

This also applies to constraints and keys (PK, FK, UK).  Assigning a default constraint to a table from the UI would result in something like the following:  DF_Employee_Pay_History_Modified_Date. This just isn’t clean.

In my opinion, CamelCase looks cleaner and as you can see by the index naming convention I use it’s also much easier to parse.

I’m sure that there are a lot of strong opinions on this topic and I’d like to hear them all. Be sure to leave a comment.

Follow me on Twitter and LinkedIn and if you liked this post endorse me.