SQL 2000 Database Basics

What ever your job, I always think it an advantage to take the time to understand how things work ‘under the covers’.  I recommend that even if your job is just to administer SQL server that you learn a few transact statements, and delve into how data is stored.  This section introduces the key database terms which are used in SQL 2000 and other relational databases.

Plug gaps in your knowledge check out these terms:

Tables with their columns (fields) and rows.

If your role is just to care for the servers, it is still useful to know the basics of how the data is organized.  Vertical columns in a table, represent fields in a database.  Each field has properties like data type and number of characters.  The database designer may have indexed fields used in searches, and one field, the primary key, ensures that each value is unique.

Horizontal rows represent a line of attributes, each row corresponds to one person’s record.  (See database table below)

     Table1 Fields (5 Columns) 
  EmployeeID Firstname Lastname phone age
      2 Rows –>  007 James Bond 00234 007 34
  008 Jane Moneypenny 00234 008 28

                 

The Power of a Relational Database

Relational is the key concept in an SQL database.  Each table has one set of information, the power and efficiency comes when you link tables through common fields. e.g. EmployeeID

Take an example where you have an employee records in Table1 (above) and a second table with data on goods they sell, Table2 (below)

 

     Table2 Fields (3 Columns) 
  OrderNumber EmployeeID Item
      4 Rows –>  100205 007 Radio
  100206 007 Microwave
  100207 008 Radio
  100208 007 T.V.

 

Note the Relationship is this:  Table1 EmployeeID = Table2 EmpoyeeID

It is more efficient to store this information in a separate table to avoid duplicating the employees Firstname, Lastname every time you enter an item that they sell.  Another bonus of relational design is that if you needed to update the phone number you would only have to do it once, and not for every row in the database.

Using built-in commands like ‘SELECT’ and ‘JOIN’ you can easily extract information in all the tables that you needed.  For example you could produce an end of month reports on Firstname, Lastname, Item.  You would do this through a JOIN statement to link the two tables which are related by a common field = EmployeeID.

Structured Query Language (SQL)

When you manipulate data in the database, you need a set of commands which the DBMS software understands.  Many languages can be used with relational databases; the most common is SQL. The American National Standards Institute (ANSI) and the International Standards Organization (ISO) define software standards, including standards for the SQL language. SQL Server 2000 supports the Entry Level of SQL-92, the SQL standard published by ANSI and ISO in 1992. The dialect of SQL use in Microsoft’s SQL Server is Transact-SQL.

Two Keys

Primary key

Primary means first, so this key is important in identifying the unique feature of the data in your table.  The presence of a primary key will prevent duplicate information e.g. stop two users having the same EmployeeID, but allowing there to be two people with the same lastname.

Get into the habit of identifying the primary key in your table.  Table views make it easy by displaying a key symbol next to the primary key.

Foreign keys

This constraint means that the value must already exist in another table.  What is more this value must be unique in that other table.  So this is useful for maintaining the integrity of the data.

Two Types of Index

Let us first consider why database designers bother with indexes?  The answer is to speed up searching.

In a clustered index, the order of the rows on the disk, matches the order of the rows in the index.  So a clustered index is like a table of contents with pointers to the data.  If you think about it, there can only be one clustered index.  All the other (Non clustered) indexes are rather like the alphabetical table at the back of a book which point the relevant word.  However unlike a book, you can have many clustered indexes on a table of data.


Next Get started with some Transact SQL commands