What is the difference between Named Instance and Default Instance?

Question

I created a server which is named as <SystemName>\SQLEXPRESS and even enabled the TCP/IP protocol to let it searchable on local intranet. But the problem is, people are able to connect to the server having name <SystemName>; not <SystemName>\SQLEXPRESS. Any changes done on <SystemName>\SQLEXPRESS are easily replicated on the server having name <SystemName>. But I want to know what is the Difference between server name (i.e. system name) and systemname/sqlexpress?

Answer 1

When you provide only Server Name or System Name, the connection is established with Default Instance of SQL server on that system.

If you want to connect to specific Named Instance installed on that system, you have to specify both:

SystemName/InstanceName

In the example you included in question ("systemname/sqlexpress"), sqlexpress is the name of SQL instance on that system.

You can find more details about this on Microsoft:

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.

You can install multiple instances of SQL server on one system. There is no difference between Named and Default instance except that Default is default and you do not need to specify the name explicitly for it.

As noted by @Brian in comment, one difference that can be considered is SQL Server Browser Service:

If the SQL Server Browser service is not running, you are still able to connect to SQL Server if you provide the correct port number or named pipe. For instance, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433.

However, if the SQL Server Browser service is not running, the following connections do not work:

• Any component that tries to connect to a named instance without fully specifying all the parameters (such as the TCP/IP port or named pipe).

• Any component that generates or passes server\instance information that could later be used by other components to reconnect.

• Connecting to a named instance without providing the port number or pipe.

• DAC to a named instance or the default instance if not using TCP/IP port 1433.

• The OLAP redirector service.

• Enumerating servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.

Not actually a difference but.... default port for SQL Server is 1433. Generally, Default Instances run on this port; this can be configured to run on different port as well. Even though you change the port, Default Instance still remain Default Instance. You will need to specify the port explicitly then.