alias – SQL Studies

Long names vs short names (or aliases)

Kenneth Fisher — Tue, 15 Jun 2021 13:00:00 +0000

I remember when I was first learning to code there was always the argument of how long a name should be. For example the table msdb.dbo.log_shipping_monitor_history_detail. That particular table name is 35 characters long, and it’s not the longest table name I’ve ever seen. Can you imagine typing that a dozen times in a query? How much easier would it be if the name was significantly shorter?

On the other hand, can you imagine if it was just named lsmhd? Or just tab001? And yes, I’ve seen object names similar to both. Much easier to type, knowing what it is on the other hand, not so easy.

Things like aliases and synonyms have made this less of an issue but it’s still an important consideration. Long and easy to understand, or short and easy to type. As I’m sure is obvious by now, I thought I’d throw in my two cents. And to be fair, this is entirely my opinion on this, you are welcome to have your own.

Object names should be as long as necessary to be completely readable and understandable. That means if your table name needs to be 100 or even 200 characters long for someone to be able to walk up and say “Hey, That’s where I can find xyz!” then that’s what you do.
Within a query every object should be qualified. i.e. at least a two part name depending on the type of object and if it’s in the current database. This means that column names should include the table, table names should include at least the schema, possibly the database etc.
Aliases
- Should be used if the original name is longer than you feel like typing throughout the rest of the query.
- Don’t have to be readable. Short is more important here. So lsmhd would actually work for an alias for the table above.
- Should be used in such a way that in the end your query is more readable, rather than less.

That last line is everything. Your naming conventions, for all types of objects and aliases, should make things easier to understand. For every time you type your code out, you (or someone else) will be reading it dozens of times (at least). Guess which I think is more important.

The default instance has a name

Kenneth Fisher — Mon, 31 Aug 2015 12:00:01 +0000

The default instance actually has an instance name. It’s MSSQLSERVER. This is probably familiar to a lot of you but hold on because I’m going to go a little bit deeper. When we connect to a default instance we typically just put down the server name right? Is that because the name MSSQLSERVER is special somehow? Nope. Not at all.

I’m guessing everyone has heard of port 1433 right? Well that particular port IS special. You do not have to specifically name the instance if it is on port 1433. And since by default the default instance starts on port 1433 …

So for example if we have two instances one is the default and the other is TOtherOne. Normally we would expect to reference these like this:

Referenced By	Instance Name	Port
Server	MSSQLSERVER	1433
Server\TOtherOne	TOtherOne	Not 1433

But if we change the port assignments so that TOtherOne is port 1433 and MSSQLSERVER is pointing to, well, really anything else. Now we reference them like this:

Referenced By	Instance Name	Port
Server\MSSQLSERVER	MSSQLSERVER	Not 1433
Server	TOtherOne	1433

If you want to read up more on how this works I would recommend reading this article on the SQL Server Browser Service.

This also allows us to do something really useful. If we have multiple instances on a single server we can alias them in such a way that each instance is called by a different server name and without an instance name. I was able to find some good instructions on how to do this here.

Aliasing a SQL Server: When it works, when it doesn’t and when it may be your problem.

Kenneth Fisher — Wed, 18 Jun 2014 12:00:40 +0000

Creating an alias for a SQL Server is fairly easy and there are several ways to do it. Configuration Manager is my personal favorite. Open up configuration manager and select the SQL Native Client xx Configuration. Under that you will find Aliases.

From here you can add, update or delete aliases.

So at this point some of the more polite members of the audience are probably thinking “Unfortunately I have no idea what you are talking about. Would you please explain what an alias is?” And I appreciate that from both of you. The rest are probably thinking something along the lines of “You idiot, if you’re going to talk about aliases it would be nice if you explained what they are first!”

Per BOL:

An alias is an alternate name that can be used to make a connection. The alias encapsulates the required elements of a connection string, and exposes them with a name chosen by the user.

In other words once you have created an alias you can connect to the aliased machine using the new name in the connection string. For example I create an alias GEORGE and point it to my (local)\sql2012 instance. I can now connect to my instance using either its correct name (local)\sql2012 or GEORGE.

When it’s working

Let’s say you have a series of servers: LARRY, MOE and CURLY. You decide to do a side by side upgrade of CURLY. You are going to move all of the databases on CURLY (a 2008 server) to SHEMP (a 2012 server) and then shut CURLY down. There is a bit of a problem however. The developers have told you in no uncertain terms they do not have time to find all the dozens of places that CURLY was hard coded into the various applications. We can solve this easily enough by creating an alias on the application server pointing the alias CURLY to the new server SHEMP. Now when the connection strings try to go to CURLY the alias says to go to SHEMP and everything continues to work.

When it doesn’t

Over the weekend you’ve moved the databases and the applications were all tested. Everything went smoothly so CURLY was permanently shut down. Monday morning rolls around and the developers start calling. They can’t connect to the database. Want to guess why not? An alias only works locally, each individual client (the machine you want to connect from) must have it’s own alias created.

When it might be your problem

Over time alias’ get created (sometimes by accident believe it or not) and get forgotten. Over the years I have seen a number of situations where the answer to “Why can’t I connect to server XYZ?” is an alias that the user either didn’t know about or had forgotten. That’s why this has become one of those Start with Stupid steps that I take when someone can’t connect but everything else looks ok.