Avoid headaches while writing queries by using this column-naming method

One common practice that can cause you lots of frustration is to use the same column names in different tables. For example, you may have a Services table with a column Name for the name of the services a company performs, and a Company table with a column Name for the company name. Moreover, to help keep track of foreign keys, the Company ID may be CompanyID in both tables.

This approach can definitely come back to bite you. Suppose, for example, you need to create a query to join the Services and Company tables together and insert the results into a table variable. Not only will you need to reference the table names for certain columns to avoid the error Ambiguous column name—you’ll also need to create column aliases, because you can’t have different columns called Name in a table, view, or table variable.

All of the table referencing, table aliasing, and column aliasing you’re likely to have to do can be both time-consuming and confusing. Instead, simply include the table name inside the column name:
Company_Name
Services_Name

Identifying the column names uniquely this way also makes it much easier to make sense of a schema—and to figure out whether some result called Name is the company name or the services name.

Similarly, you can use names like the following to keep track of primary and foreign keys:
Company_PK
Services_CompanyFK
While this practice may look odd at first, it will save you lots of time when you’re writing code and trying to quickly remember what the column names are.