Inside Microsoft SQL Server 7.0 (Mps)

Simply put, a Microsoft SQL Server database is a collection of objects that hold and manipulate data. A typical SQL Server installation has only a handful of databases, but it's not unusual for a single installation to contain several dozen databases. (Theoretically, one SQL Server can have as many as 32,767 databases. But practically speaking, this limit would never be reached.)

A SQL Server database:

• Is a collection of many objects, such as tables, views, stored procedures, and constraints. The theoretic limit is 2 ³¹ (or more than 2 billion) objects. Typically, the number of objects ranges from hundreds to tens of thousands.
• Is owned by a single user account but can contain objects owned by other users.
• Has its own set of system tables that catalog the definition of the database.
• Maintains its own set of user accounts and security.
• Is the primary unit of recovery and maintains logical consistency among objects in the database. (For example, primary and foreign key relationships always refer to other tables within the same database, not to other databases.)
• Has its own transaction log and manages the transactions within the database.
• Can participate in two-phase commit transactions with other SQL Server databases on the same server or different servers.
• Can span multiple disk drives and operating system files.
• Can range in size from 1 MB through a theoretical limit of 1 TB (1,048,576 MB) in size .
• Can grow and shrink, either automatically or by command.
• Can have objects joined in queries with objects from other databases in the same SQL Server installation.
• Can have specific options set or disabled. (For example, you can set a database to be read-only or to be a source of published data in replication.)
• Is conceptually similar to but richer than the ANSI SQL-schema concept (discussed later in this chapter).

A SQL Server database is not

• Synonymous with an entire SQL Server installation.
• A single SQL Server table.
• A specific operating system file.

A database itself isn't synonymous with an operating system file, but a database always exists in two or more such files. These files are known as SQL Server database files and are specified either at the time the database is created, using the CREATE DATABASE command, or afterwards, using the ALTER DATABASE command.