Creating SQL Server 2000 Databases

Attaching and Detaching Databases Detaching/attaching is used in moving a database between computers or physical disks. When a database is detached, the links from the server to the data files and transaction logs are removed. When you attach a database, you re-establish those links. When you detach and re-attach, you eliminate the need to restore the database backup manually. When a database is reattached, all files that have changed location must be specified.

Filegroups Filegroups are database files that have been grouped together for administrative purposes only. There are times when filegroup organization can increase performance, but that is not the primary goal of the filegroup. Filegroups are useful because they increase the flexibility of the database as a whole. Filegroups allow an administrator to partially back up a database. Filegroups also allow greater fault tolerance by granting the ability to spread the database over several physical disks. The filegroup that contains the primary data file is called the primary filegroup. While other, user defined filegroups can be created, the primary filegroup is the default unless explicitly changed. Also, unless explicitly specified, all files will be placed in the default filegroup. Unless explicitly specified when a database is created, the database will grow as large as necessary until disk space is physically exhausted. Filegroups can be assigned a maximum size and a growth rate that determines how much they will grow when their current space is exhausted. These attributes can be established during filegroup creation and can be changed after the filegroup is created.

Expanding and Shrinking a Database When a database is created, it is assigned a maximum size and an automatic growth rate to use when the initial file size is exceeded. A database can be manually expanded or shrunk once it has been created. The CREATE DATABASE Command The CREATE DATABASE statement allows you to establish many of the options on a database during its creation. The syntax for the CREATE DATABASE statement is as follows:

The CREATE DATABASE Command
The CREATE DATABASE statement allows you to establish many of the options on a database during its creation. The syntax for the CREATE DATABASE statement is as follows:

CREATE DATABASE <database_name>
[ON <filegroup_name>
NAME = <filename_1>,
FILENAME = 'drive:\directory\filename.mdf',
SIZE = <initial size in MB>,
MAXSIZE = <maximum size in MB>,
FILEGROWTH = <size in MB to add >
]

The ALTER DATABASE Command
The ALTER DATABASE command is used to change a data file after it has been created. The syntax for the ALTER DATABASE statement is as follows:

ALTER DATABASE <database_name>
{ ADD FILE [TO FILEGROUP filegroup] [FOR RESTORE]
(FILENAME = 'drive:\directory\filename.mdf'
SIZE = <Size in MB>
MAXSIZE = <size in MB>
FILEGROWTH = <size in MB to add>
TO FILEGROUP <filegroup_name>
}

The ADD FILE can also be replaced with the following statements to achieve the following results:

Statement Purpose

ADD LOG FILE Add a log file to the database
REMOVE FILE Remove a data, transaction, or log file from a database
ADD FILEGROUP Add a filegroup to a database
REMOVE FILEGROUP Remove a filegroup from a database
MODIFY FILE Change the attributes (SIZE, MAXSIZE, or FILEGROWTH) of a file in the database
MODIFY FILEGROUP Change the changes the properties on a specified filegroup (READONLY, READWRITE, DEFAULT)

Transaction Logs A transaction log records all changes made to a database and allows changes to be rolled back or forward in the event of a system failure or in the case of an erroneous entry. The transaction log is associated with the data files when the database is created. The transaction log actually consists of several files that exactly describe the changes made to the database. These files are treated by the database as one file for the purposes of space and placement.

There comes a point when old log records are no longer needed for recovering or restoring a database and must be deleted to make room for new log records. The process of deleting these log records is called truncating the log.

If possible, transaction logs should be placed on a different physical disk than the data files. This placement will increase performance by removing competition for hard drive access time from the transaction logs and the data files.

Write-Ahead Transaction Log SQL Server 2000 uses a write-ahead log. A write-ahead log ensures that no data modifications are written to disk before the associated log record. SQL Server 2000 maintains a buffer cache into which it reads data pages. Data modifications are not made directly to disk, but to the copy of the page in the buffer cache. The modification is not written to disk until the lazywriter process schedules a write for the page.

At the time a modification is made to a page in the buffer, a log record is built in the log cache recording the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page were flushed before the log record, it would create a modification on disk that could not be rolled back if the server failed before the log record were written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record. Because log records are always written ahead of the associated data pages, the log is called a write-ahead log.

Managing Database Objects

Tables are database objects that contain all data in a database. Tables organize data into a row and column format where each row represents a unique record and each column represents a field in that record. Tables are created by the CREATE TABLE command. Changes to a table can be made using the ALTER TABLE command.

Indexes are objects that are associated with tables that speed the retrieval of rows in the table containing keys that have been built from one or more columns in a table. Indexes come in two varieties, clustered and nonclustered. A clustered index sorts and stores rows of data in a table based on the key values. Nonclustered indexes do not affect how data rows are sorted in a table. Nonclustered indexes contain values that point to rows in the indexed table.

Constraints are conditions placed on data entered into a column that are meant to ensure data integrity. Generally constraints are placed on the column designated as the primary key. Constraints are by no means exclusive to the primary key column but they are a useful way to ensure that all row values in the primary key column are unique.

A foreign key is a column that is used to establish and enforce a link between data in two tables. Foreign key constraints are used to maintain referential integrity between tables by controlling the data that can be entered and the changes made to foreign keys in referring tables.

Stored Procedures are collections of SQL statements residing on the server that can be executed by users to achieve a desired goal. Stored procedures can have values or variables that can be passed to them by the user or the system that they can act upon. Since they are located on the server, stored procedures can greatly increase performance as there is only one authentication, the authentication for the initial execution of the stored procedure, for all the commands that are executed. You can nest stored procedures and can call other procedures within the body of a stored procedure. Stored procedures are useful in implementing consistent logic across applications. In a stored procedure SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once. Each application needing to perform that task can then simply execute the stored procedure. A structure using a stored procedure offers a single point of control for control of business processes.

Triggers are stored procedures that are activated on an event driven basis, instead of being explicitly called. Triggers are typically used to maintain data integrity, cascade referential actions, or to utilize external resources (e.g. send an e-mail, notify a pager, etc.). There are two types of triggers; AFTER triggers and instead-of triggers.

AFTER Triggers The default type of trigger is the AFTER trigger. On a table there can be multiple triggers defined for an event. You can designate the first and last triggers to fire but you cannot control the order that any other triggers will fire.

INSTEAD OF Triggers INSTEAD OF triggers are set up to specify the action to take when data is modified. INSTEAD OF triggers must be explicitly declared as instead-of or they will default to AFTER triggers. You can only have one IN STEAD OF trigger for each action.

Views A view is a filter that dynamically interprets information in one or more tables and creates a composite table that other operations can be performed upon. Rows can be updated, deleted, or inserted from views. A view is not an actual table, but it is presented in table format.