Sunday, March 2, 2008

ConnectionStrings: What's the Difference between (local) vs. LocalHost vs. "."

There's a lot of things in this world I don't really think about. Until not too long ago one of these things was how many different there are of specifying the name of a local machine when connecting.

For instance when connecting to a machine what's the difference if I connect to "localhost", "(local)" or simply "."?

The answer ends up being it depends on the provider you're using to connect. If you're using the SQL Server Native Client or the .NET Data Provider for .NET 2.0 the behavior is the same regardless of which name you use (localhost/(local)/or "."). These providers will first try to connect using Shared Memory, then try to connect using TCP/IP and then finally Named Pipes. So for these providers it doesn't really matter what you use to specify the server name, they both behave the same.

Developers who work with other providers though know that these aren't all equivalent. Someone who's intimately familiar with the Microsoft OLE DB Provider for SQL Server have probably had occasions where they could connect with "(local)" but not with "localhost" or ".". This is because for the Microsoft OLE DB Provider for SQL Server provider will do a little more work for (local) than it will for "localhost" or ".". If you specify (local) it will behave as mentioned above, it will first try to connect via Shared Memory, then TCP/IP and then finally via named pipes. If you specify "localhost", or "." however these are protocol specific for this provider. That is, they will only try to connect via the protocol that you specify.

I guess as a rule of thumb if you just want to connect and don't care for details you might want to consider always connecting to "(local)". Chances are that (depending on your provider) it will do a little more leg work for you. If you're neurotic enough to want to know the exact protocol that you're using to enter the data store (could be useful to know when it comes time to deploy) then use either "localhost" or ".".

Hope that sheds a little light.