Database Fundamentals in SQL Server 2008

This course is aimed toward those students who are new to using Microsoft SQL Server.
This course is also useful to those students who are studying for exams:

•70-432: Microsoft® SQL Server® 2008, Database Administration

•70-433: Microsoft® SQL Server® 2008, Database Development

Lesson Introduction

A database is a repository of data that can be stored, manipulated, and retrieved. There are many database models. SQL Server specializes in the implementation of a relational database model.

The purpose of this lesson is to explore the fundamental components of a relational database system. Tables and relationships form the basis of SQL Server. The organization of these tables are known as normalization, which makes working with SQL Server efficient, regardless of whether you are writing queries or creating a database from the outset.

Finally, you will be introduced to additional objects that are implemented within a SQL Server database to support data.

Types of Databases

A relational database is a collection of tables that are related together by a common field through a relationship. The purpose of a relational database is to break down data into business entities such as customers, orders, and employees and reflect each entity as a table. This helps reduce data redundancy and improves performance.

Within a database, two models of database can be created.

Online Transactional Processing (OLTP) Online Analytical Processing (OLAP)
The tables are organized in a way that the database is more optimized for systems that perform data modifications such as inserts, updates and deletes.

Typically, deals with detailed information.

Users can still read data from these systems. However, read performance may be affected because the structure of these tables is designed to reduce the amount of redundant and repetitive data OLTP is commonly implemented in many relational database systems.

Data is up to date and is immediately available after the transaction that modified the data is complete.

Each transaction typically deals with small amounts of data.

In this model, data is stored is in multidimension views of aggregated and detailed data. These can be stored as tables in a SQL Server database, other storage models also exist.

Typically, deals with summary information.

The tables here are optimized for reading data. Although it is possible to modify data, the performance against OLAP may be impaired for write operations.

OLAP databases are managed by SQL Server Analysis services and is becoming popular with the need to analyze data becoming more of a business imperative.

OLAP represents historical data over time.

Each transaction typically deals with large amounts of data.

What Are Tables?

Tables are objects that are used to store data. A table consists of columns and rows.

Columns have a data type such as an integer or time data type associated to a specific column to control the type of data that can be entered.

Rows are used to hold specific information about a business entity such as a customer or an order.
Typically, each row consists of a unique identifier associated to a particular row such as a customerID or as in the graphic, the DepartmentID column to assign a unique attribute to a record.

A primary key constraint is typically associated with the ID column to ensure that the entity data is unique.

What Are Relationships?

Relationships are used to join tables together through common fields between two tables.

Primary keys can be used to enforce uniqueness of records within a single table. You can also use the primary key to enforce data integrity between two tables. This is known as referential integrity.
In a typical two table relationship, one of the tables will be joined to the other table by a primary key column. The table that has the primary key is known as the primary table, and all records in the primary key column are unique. The secondary table on which the column is joined from the primary table typically has repeating values. The column that holds the repeating values is referred to as a foreign key column. This creates a relationship known as one to many and is the most common relationship type used.

By default, the impact of this relationship on joined columns is:


  • A value cannot be placed in the secondary table unless it exists in the primary table.
  • A record cannot be removed from the primary table if it has related records existing in the secondary table.
  • A record cannot be updated from the primary table if it has related records existing in the secondary table.

This ensures that data integrity is retained and that accidental deletions and updates cannot occur. This default behavior can be modified. Additional relationships that can occur in a relational database management system include the following.

  • One-to-One. There is one record in the secondary table for each record in the primary table.
  • Many-to-Many. There are many records in the secondary table for many records in the primary table.

What Is Normalization?

Normalization is the process of organizing data and tables within the SQL Server database. This is implemented by creating tables and setting relationships between the table to remove repetitive and redundant data.
Redundant data occupies unnecessary space in the database. Moreover, a change in information in one area requires a change in all areas where the same data exists because data is repeated in several locations. This can be particularly problematic if the data changes frequently.
There are many degrees of normalization. For use in commercial databases, it is typical to only implement one of three levels of normalization. These typically set rules for how data should be broken down.

These are referred to as:


  • First Normal Form (1st NF)
  • Second Normal Form (2nd NF)
  • Third Normal Form (3rd NF)

Normalization is a useful guide in breaking down data into a relational format. However, there can be slight variations when it is implemented. Ultimately, remember the purpose of normalization.

  • To prevent waste of space
  • To make data consistent
  • To ease long-term maintenance
  • To ease data updates and modifications

Additional SQL Server Objects

Tables are the fundamental storage object of data within a SQL Server 2008 database. There are also additional objects that can be created in SQL Server, which are used to support, manage, and protect the data that is stored within the tables. The following is an overview of each object and the purpose that it serves within SQL Server.

SQL Server Object Description
Constraints Constraints are objects that are associated with single or multiple columns within a table. Constraints ensure that data that is inserted or updated meets the requirements that the constraint places on the data.

The type of constraint objects that can be associated to a table are:

Primary key

Unique constraints

Foreign key

Default constraint

Check constraint

XML schemas

Indexes Indexes are objects within SQL Server that provide pointers to the data. They act as a bridge between queries and the data.Indexes can speed up data retrieval from a database if the appropriate indexes are created.

Without indexes, SQL Server scans an entire table to find data that will be required in a query.

Moreover, like an index in a book, an index in a database requires disk space.

Stored Procedures Stored procedures are SQL Server objects that encapsulate T-SQL logic within a named object.Stored procedures can hold T-SQL statements for repetitive or complex T-SQL queries and tasks without the need to rewrite the T-SQL statement.

The main advantage of stored procedures is that they are precompiled.

This means that the first run of a stored procedure has to work out the best way to retrieve data for a query or a task before it is executed.

Subsequent execution of the same stored procedure will take advantage of the previous execution plan that was previously worked out.

This can substantially improve the performance of queries and modifications that are conducted through stored procedures.

Stored procedure objects can also be secured, so access to the data can be controlled.

Views Views are objects in SQL Server that are analogous to virtual tables. Views consist of rows and columns like a table, and they can be secured to control access to the data like stored procedures.Views can pull data from any number of tables into a single virtual table. This provides the following benefits:

Focuses the data for the user.

Simplifies the management of permissions.

Hides the complexity of the underlying tables.

Provides shared application logic for .NET applications.

In certain situations, views can be used to improve performance.

Functions Functions are objects within SQL Server that can be used to manipulate or change data.SQL Server provides a wide range of system-supplied functions that can be used against data in the database, such as:

CONVERT – To covert data from one data type to another

GETDATE() – To retrieve today’s date

However, SQL Server provides database professionals with the ability to create user-defined functions if the SQL Server system functions do not provide the required functionality.

Data Manipulation Language (DML) Triggers Like stored procedures, triggers are SQL Server objects that encapsulate T-SQL logic within a named object.However, triggers are event-driven. A trigger is an object that is associated to a table or a view and dependant on the type of trigger defined will only fire when one of the following T-SQL statements are issued: