Loopback Linked Servers
You can set up a linked server to point to itself, but why would you want to?
One reason Microsoft cites is that you may only have one server to develop on, but need to test linked servers for a live environment with more than one server (See, you can try this at home!)
Loopback linked servers do come with a couple of drawbacks though--you cannot use them for Distributed Partitioned Views, and you cannot use them for nested transactions (which limits their potential for testing Update statements).
One other good reason for using Loopback Lined Servers is the Application Role.
Once an Application Role has been started, you will not be able to directly access a different database on the same server, even if your Login ID has the necessary data rights. This is because the Application Role suspends all the rights associated with the login ID and replaces them with its own. However, you can still access Linked servers, so you can get at the other databases in this manner.
Note that although Network Traffic will not be an issue with Loopback Linked Servers, there are still processing overheads to deal with, as the server still has to initiate a new connection every time you access the Loopback Linked Server.
» 
Comment and Contribute
