Welcome to the first in my randomly ordered SQL 101 series. This main goal of this series to is provide an entry level guide to some of the basics in the T-SQL world. It also helps me to easily create my own reference sheet as I often find myself going back to blogs I wrote in the past to find a MSDN link when I fail at Google.
With all of that said the first topic today is going to cover using an ALIAS for table names in side of T-SQL. The full and complete MSDN rundown can be found in the link. Now lets get started.
Basics: Table Alias
The use of table alias in SQL is very common practice for all of the environments I have been exposed to. They offer the person writing the code to save time from typing out long table names, and it also helps others to digest the code and quickly understand what is going on. The one important thing to note is that if you decide to alias your table please ensure you remain consistent throughout the environment as best you can. While it’s not a requirement it’s best practice to do so.
Demo: Table Alias
So in the image below you can see I have used a table named RM00101 from my test company I setup inside of Dynamics GP. I love this database as it’s more aligned to the real world with naming conventions and complexity.
1: So this is a normal SELECT statement that returns the customer number and customer name from my table. If you want to try open up SSMS and write out a SELECT statement similar to the above using the two part name of table.column. When I execute this statement we see the results returned without any issue.
2: This is the same statement as the one above, the only difference being that I’ve aliased the RM00101 table as CustomerMaster. Now if you execute that code you will receive an error message like the one below.
The reason here is that once you have aliased a table SQL requires that you refer to all instances of the alias in your query. So in the SELECT statement we have the RM00101.COLUMN which is invalid since SQL is expecting to see CustomerMaster.COLUMN
3: This is the same statement as the two above only we’ve aliased the table and also updated the items in our SELECT list to use the aliased table name instead.
Aliasing is amazing and can help shorten the amount of code a dev must type out, all the making it easier for anyone reading your code to quickly find out what is going on. I encourage you to read up fully on the topic using the MSDN link above.