Chapter 4. Databases and Database Files


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.




Inside MicrosoftR SQL ServerT 2005. The Storage Engine
Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)
ISBN: 0735621055
EAN: 2147483647
Year: 2004
Pages: 115
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net