Difference between SQL Server Instance / Database
Few points on difference between SQL Server Instance and Database:
| Instance | Database |
|---|---|
| An instance contains databases. Each instance manages several system databases and one or more user databases. | A database contains tables where the data is stored. |
| Instances of the SQL Server Database Engine must be installed using setup. | Databases are contained on the instance, and can be created using Transact-SQL (CREATE DATABASE) which is very fast. |
| Server resources such as CPU and memory can be configured for each instance, but not for each database. | |
| The biggest difference between instance-wide privileges and database-wide privileges is that instance-wide privileges are granted directly to the login. | Database-wide privileges are granted to users, and these users are then mapped to logins. | SQL Server is installed on a machine that becomes a server and can be referenced by its machine name. If SQL Server is on the same machine as the requesting software, the machine can be referred to as (local). The engine can be installed more than once on a machine, and each installation is called an instance. SSE installs as an instance named (local)\SQLExpress. | The existence of databases as a separate realm has the advantage of allowing a database to be detached from a server instance and to be attached to another one. |