What is the Difference Between Default Instance and Named Instance in SQL Server

The main difference between default instance and named instance in SQL server is that a SQL server can have only one default instance, but it can have multiple named instances. 

SQL Server is an RDBMS of Microsoft. It allows the users to create databases and arrange data in the tables of the databases. The user can manipulate the data using the Structured Query Language. SQL instance is a copy of the sqlserver.exe executable file. It is an installation of the SQL server. There are two types of SQL server instances as default instance and named instance. When installing SQL server, the instance configuration window appears, and the user can specify whether he wants to create a default instance or a named instance.

Key Areas Covered

1. What is Default Instance
     – Definition, Functionality
2. What is Named Instance
     – Definition, Functionality
3. What is the Difference Between Default Instance and Named Instance in SQL Server
     – Comparison of Key Differences

Key Terms

Default Instance, Named Instance, SQL Server

What is Default Instance

If there is no SQL server installed yet, a default instance will be created unless the user specifies a named instance. There can only be one default instance. The default instance name is MSSQLSERVER.  For example, assume that an application program in Java or .NET sends data to a database in the default instance. It does not require the client to specify the name of the instance to make the connection. When there is a default instance of SQL server installed, the user can add a default instance of analysis services to the same machine.

What is a Named Instance

Named instances are all instances other than the default instance. When the programmer gives a name to the instance when installing it, is a named instance. In other words, the user determines a named instance during the setup. Also, it is possible to install SQL Server as a named instance without installing the default instance first.

Figure 1: SQL Server

However, there are certain rules to consider when creating a named instance. The instance names are not case sensitive. Moreover, they cannot begin or terminate with an underscore symbol (_). The user cannot also use “Default” or other reserved keywords to name the instance. If he uses a reserved keyword, SQL server will display a setup error. Also, the first character must start with a letter while the maximum number of characters in the instance is 16. Furthermore, it is not possible to use spaces or special characters such as black slash, colon, comma, single quote, hyphen etc. in the instance name.

Difference Between Default Instance and Named Instance

Definition

A default instance is a type of instance that is used when installing a single instance of SQL server. In contrast, a named instance is a type of instance where the user specifies an instance name when installing the instance. 

Quantity

There is only one default instance but can have multiple named instances. Thus, this is the main difference between default instance and named instance in SQL server.

Application

If the user plans to install a single instance of SQL server, it is a default instance. However, if the user plans to install multiple instances on the same computer, then the instances other than the default instance are named instances. Hence, this is another difference between default instance and named instance in SQL server.

Conclusion

Default instance and named instance are two types of SQL server instances. The main difference between default instance and named instance in SQL server is that there can be only one default instance in SQL Server while there can be multiple named instances in the SQL Server.

Reference:

1.“Database Engine Instances (SQL Server).” Microsoft Docs, Available here.
2.“Instance Configuration.” Microsoft Docs, Available here.

Image Courtesy:

1. “SQL Server” By Soumyasch – Own work (CC BY-SA 3.0) via Commons Wikimedia