Database Basics
A database in SQL Server 2005 is made up of a collection of tables that stores a specific set of structured data. A table contains a collection of rows, also referred to as records or tuples, and columns, also referred to as attributes. Each column in the table is designed to store a certain type of information, for example, dates, names, dollar amounts, and numbers.
Tables have several types of controls, such as constraints, triggers, defaults, and customized user data types, that guarantee the validity of the data. Declarative referential integrity (DRI) constraints can be added to the tables to make sure that interrelated data in different tables remains consistent. Tables can have indexes similar to those in books that enable rows to be found quickly. A database can also contain procedures that use Transact-SQL or .NET Framework programming code to perform operations with the data in the database . These operations include creating views that provide customized access to table data or a user-defined function that performs a complex calculation on a subset of rows.
For example, you create a database named MyCompanyDB to manage the data in your company. In the MyCompanyDB database, you create a table that is named Employees to store information about each employee. The table also contains columns that are named EmpId, LastName, FirstName, Dept, and Title. To make sure that no two employees share the same EmpId and that the Dept column contains only valid numbers for the departments in your company, you must add constraints to the table.
Because you want to quickly find the data for an employee, based on the employee ID or last name, you define indexes. You will have to add a row of data to the Employees table for each employee, so you have to also create a stored procedure named AddEmployee. This procedure is customized to accept the data values for a new employee and perform the operation of adding the row to the Employees table. You may need a departmental summary of employees. In this case, you define a view called DeptEmps that combines data from the Departments and Employees tables and produces the output. This illustration shows the parts of the MyCompanyDB that are created.
.gif "Objects in a database")
An instance of SQL Server can support many databases. Each database can store either interrelated or unrelated data from other databases. For example, an instance of SQL Server can have one database that stores personnel data and another database that stores product-related data. Alternatively, one database can store current customer order data, and another related database can store historical customer orders used for yearly reporting.
Important
You should not create any user objects, such as tables, views, stored procedures, or triggers, in the master database. The master database contains system-level information used by the instance of SQL Server, such as logon information and configuration option settings.
OLTP Database
Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. They typically have several users who are performing transactions at the same time that change real-time data. Although individual requests by users for data generally reference few rows, many of these requests are being made at the same time.
OLTP databases are designed to let transactional applications write only the data needed to handle a single transaction as quickly as possible. OLTP databases generally do the following:
- Support large numbers of concurrent users who are regularly adding and modifying data.
- Represent the constantly changing state of an organization, but do not save its history.
- Contain lots of data, including extensive data used to verify transactions.
- Have complex structures.
- Are tuned to be responsive to transaction activity.
- Provide the technology infrastructure to support the day-to-day operations of an organization.
- Individual transactions are completed quickly and access relatively small amounts of data. OLTP systems are designed and tuned to process hundreds or thousands of transactions being entered at the same time.
The data in OLTP systems is organized primarily to support transactions, such as the following:
- Recording an order from a point-of-sale terminal or entered through a Web site.
- Placing an order for more supplies when inventory quantities drop to a specified level.
- Tracking components as they are assembled into a final product in a manufacturing facility.
- Recording employee data.
Data Warehouse
In contrast to an OLTP database in which the purpose is to capture high rates of data changes and additions, the purpose of a data warehouse is to organize lots of stable data for ease of analysis and retrieval. A data warehouse is frequently used as the basis for a business intelligence application.
Following is a list of what data warehouses can do:
- Combine data from heterogeneous data sources into a single homogenous structure.
- Organize data in simplified structures for efficiency of analytical queries instead of for transaction processing.
- Contain transformed data that is valid, consistent, consolidated, and formatted for analysis.
- Provide stable data that represents business history.
- Be updated periodically with additional data instead of making frequent transactions.
- Simplify security requirements.
Database Snapshot
A database snapshot is a read-only, static view of a database, the source database. Each database snapshot is transaction-consistent with the source database as it existed at the time of the snapshot's creation. For more information, see Database Snapshots.
See Also
Concepts
System Databases
Understanding Files and Filegroups
Introduction to Transaction Logs
Database States
File States