What are named and default instances?

What are named and default instances? What is/are the difference(s) between them? Why they are used?

Solution 1:

According to Microsoft regarding named vs default

Client applications connect to an instance of Microsoft SQL Server 2005 to work with a SQL Server database. Each SQL Server instance is made up of a distinct set of services that can have unique settings. The directory structure, registry structure, and service name all reflect the specific instance name you identify during setup.

An instance is either the default, unnamed instance, or it is a named instance. When SQL Server 2005 is in installed in the default instance, it does not require a client to specify the name of the instance to make a connection. The client only has to know the server name.

A named instance is identified by the network name of the computer plus the instance name that you specify during installation. The client must specify both the server name and the instance name when connecting.

By default, SQL Server installs in the default instance unless you specify an instance name. SQL Server Express, however, always installs in a named instance unless you force a default installation during setup.

Solution 2:

Also you can install only one default (unnamed instance) but you can install many named instances.

Many third party software will usually use a default named instance although they may not mention it. The reason is clear from the above answers, standard versions installs an unnamed instance by default while the Express version installs a named instance by default.

It is important to know about the difference from this perspective because if you have 2 or 3 DB Servers running, you might never connect to the right version. Because the third party software is looking for the default instance while you are thinking it is the SQLEXPRESS instance it is trying to connect to. It can significantly increase your troubleshoot time, if you don't know how to connect to a named vs unnamed instance.

So if you want to connect to Named or Unnamed Version, use the following guidelines.

MY-MACHINE-NAME\SQLEXPRESS  /* named version - correct */
MY-MACHINE-NAME             /* unnamed version (default instance) - correct */
MY-MACHINE-NAME\MSSQLSERVER /* unnamed version (default instance) - Wrong */

Note that even though a default instance has a name, it can not be referenced by its name!