BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

Programming Concepts

Relational Database Concepts for SQL Server

This is the first in a series of articles describing the use of Microsoft's SQL Server database management system (DBMS). The tutorial is aimed at software developers using SQL Server for data storage. Part one reviews database concepts and terminology.

Indexes

One of the greatest benefits of holding information in a database is the ability to quickly retrieve it. When querying a database, it is possible to apply criteria to ask for a specific set of rows. For example, returning all employees that use a specific vehicle or returning employees within a particular range of payroll IDs.

If the DBMS needed to scan through all of the data within a table in order to retrieve the desired information, the process would be very slow, particularly for tables with millions of rows. To improve retrieval performance a table can have one or more indexes. Each index provides a fast "look-up" facility for rows, as an index in a book allows all references to a topic to be located without reading every page.

In the fleet database example, an index may be created for the Vehicle Registration column. This would facilitate fast queries that required data to be filtered using that column.

Keys

There are two types of key that are important for Microsoft SQL Server databases. The first is the primary key. A primary key is a single column, or group of several columns (compound key), that can be used to uniquely identify rows in a table. Each table in a database may have a single primary key. Once defined, no two rows in the table may contain matching data in the primary key columns. In the fleet database example, the unique Payroll ID value could be the primary key for the Employees table.

Foreign keys are used when defining relationships between tables. A foreign key is a single column, or group of columns, in a table that reference the primary key in another table. This creates a link between the two tables. In the fleet database, a table named "Vehicles" may be used to contain the details of each vehicle owned by the company. The Vehicles table could have a primary key based upon the vehicle registration number. By creating a foreign key in the matching column of the Employees table, the employee and vehicle data become linked.

The information in foreign key columns does not have to be unique. Matching information can appear in multiple rows, as in the example employees table where two people use the same car. For this reason, relationships are often called "one-to-many links", as one value in the primary key of a table may link to many rows in related tables.

NB: A foreign key does not always refer to a secondary table. By linking a foreign key in a table to the primary key of the same table, a self-referencing relationship is created. These can be used to create hierarchical data structures.

Constraints

Constraints are rules that are applied to the information in a database. These are usually used to enforce business rules upon the tabular data. For example, a constraint may reject any attempt to create an Employee row where the employee's age is not within an acceptable range, ensuring compliance with local employment legislation.

Views

Views provide the useful concept of virtual tables. A view gathers specific information from one or more sources and presents it in the format of a single table. The information may be filtered within the view to remove unnecessary information. For example, the Vehicles table in the fleet database could contain a column to hold the type of each vehicle. A view could then be created that returns the employee and vehicle details for all drivers of heavy goods vehicles.

Stored Procedures

A stored procedure is a predefined set of statements that can be executed when required. Stored procedures provide the main means of creating programs within SQL Server databases. They can be as simple as single queries that return data or update rows, or can be complicated programs containing looping and conditional structures. Stored procedures can require the passing of parameters to control their exact execution.

Structured Query Language

The structured query language (SQL) is the language used to query and manipulate information within a SQL Server database. SQL is actually an ISO and ANSI standardised language. However, as with most RDBMS software, SQL Server uses proprietary extensions within its own Transact-SQL (T-SQL) variant of SQL.

SQL Statements

The basic building block of the structured query language is the SQL statement. Using statements, information in a database can be manipulated and queried. The four most common commands are:

  • SELECT. Retrieves a set of rows from one or more tables or views, permitting joining of tables using their primary and foreign keys and filtering of information according to defined criteria.
  • INSERT. Creates rows in a table.
  • UPDATE. Modifies data in one or more rows in a table, depending upon specified criteria.
  • DELETE. Permanently removes one or more data rows from a table.
18 May 2008