In this chapter: | | System Databases | 88 | Sample Databases | 90 | Database Files | 92 | Creating a Database | 94 | Expanding or Shrinking a Database | 97 | Using Database Filegroups | 101 | Altering a Database | 104 | Databases Under the Hood | 106 | Setting Database Options | 115 | Database Snapshots | 127 | The tempdb Database | 132 | Database Security | 137 | Moving or Copying a Database | 142 | Compatibility Levels | 147 | Summary | 148 |
Simply put, a Microsoft SQL Server database is a collection of objects that hold and manipulate data. A typical SQL Server instance has only a handful of databases, but it's not unusual for a single installation to contain several dozen databases. The technical limit for one SQL Server instance is 32,767 databases. But practically speaking, this limit would never be reached. To elaborate a bit, you can think of a SQL Server database as having the following properties and features: It is a collection of many objects, such as tables, views, stored procedures, and constraints. The technical limit is 2311 (more than 2 billion) objects. The number of objects typically ranges from hundreds to tens of thousands. It is owned by a single SQL Server login account. It maintains its own set of user accounts, roles, schemas, and security. It has its own set of system tables and views to hold the database catalog. It is the primary unit of recovery and maintains logical consistency among objects within it. (For example, primary and foreign key relationships always refer to other tables within the same database, not in other databases.) It has its own transaction log and manages its own transactions. It can span multiple disk drives and operating system files. It can range in size from 1 megabyte (MB) to a technical limit of 1,048,516 terabytes. It can grow and shrink, either automatically or by command. It can have objects joined in queries with objects from other databases in the same SQL Server instance or on linked servers. It 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.)
And here is what a SQL Server database is not: It is not synonymous with an entire SQL Server instance. It is not a single SQL Server table. It is not a specific operating system file.
While a database isn't the same thing as an operating system file, it 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 afterward, using the ALTER DATABASE command. |
