Microsoft® SQL Server™ data is stored in databases. The data in a database is organized into the logical components visible to users. A database is also physically implemented as two or more files on disk.
When using a database, you work primarily with the logical components such as tables, views, procedures, and users. The physical implementation of files is largely transparent. Typically, only the database administrator needs to work with the physical implementation.
Each SQL Server installation has multiple databases. SQL Server has four system databases (master, model, tempdb, and msdb) and each SQL Server installation has one or more user databases. Some organizations have only one user database, containing all the data for their organization. Some organizations have different databases for each group in their organization, and sometimes a database used by a single application. For example, an organization could have one database for sales, one for payroll, one for a document management application, and so on. Sometimes an application uses only one database; other applications may access several databases.
It is not necessary to run multiple copies of SQL Server to allow multiple users to access the databases on a server. SQL Server is capable of handling thousands of users working in multiple databases on the same server at the same time. SQL Server makes all databases on the server available to all users that connect to the server, subject to the defined security permissions.
When connecting to SQL Server, your connection is associated with a particular database on the server. This database is called the current database. You are usually connected to a database defined as your default by the system administrator, although you can use connection options in the database APIs to specify another database. You can switch from one database to another with the Transact-SQL USE database_name statement, or you can use an API function that changes your current database context.
SQL Server version 7.0 allows you to detach databases from a server, then reattach them to another server, or even attach the database back to the same server. If you have a SQL Server database file, you can tell SQL Server when you connect that you want that database file attached with a specific database name.
Database Design Considerations