SQL Server 2000 Overview

2.  SQL Server Overview

Relational Database Terminology

Relational database theory was first defined by Edgar Codd on the principle that relationships between database tables could be defined by the programmer rather than implicitly in the database definition. This improved on the flexibility of the hierarchical database and allowed the programmer to join any two tables together on any common field as required at the application level.

The relational model defines tables as a collection of fields (domains) which contain values stored as records (tuples) in the table. Each record must have a primary key which uniquely identifies the occurrence of the record within the table. Fields are defined as numeric, character, date and so forth and may or may not contain values. A relational database can distinguish between a blank or zero and an empty or null value.

The programmer may define a join between two tables on any common field. This is usually determined by the database designer who includes foreign key values in the child datafile that contain primary key values of the parent datafile to allow corresponding records to match up. The programmer may however join tables on any field or fields of the same datatype in both tables to create a many-to-one or one-to-one relationship. Note that many-to-many relationships may not be implemented in a relational database and are implemented with a virtual link table containing foreign key relationships to each of the parent tables.

A Database Management System (DBMS) usually has a database definition language (DDL) which allows for the field types and tables to be defined and a data manipulation language (DML) which allows for the retrieval and update of data. The manipulation language often comes in several formats allowing access to the database from a variety of programming languages.

Codd went on to define a combined database definition and data manipulation language called Structured Query Language or SQL (pronounced Sequel). This was implemented in IBM's first relational database product and has now become the standard for most relational database systems. Many older hierarchical and network database management systems also allow data manipulation by interpreting SQL syntax to perform operations on data stored in more traditional logical database formats.

SQL Server History

Sybase

Sybase come into the fray over ten years ago as a pure implementation of an RDBMS taking into account many of the technical refinements of the first generation RDBMS. The implementation is functionally equivalent and an effective competitor to relational database products from Oracle, Ingres and IBM DB2. Sybase runs on many large UNIX computers and is compatible to a large extent with SQL Server.

SQL Server 4.2

Microsoft licensed Sybase technology for use on their operating systems and SQL Server is an implementation of Sybase 4.2 on the OS/2 and Windows NT platforms. The NT version offers the technical advantages of the Sybase implementation coupled with a visual administration tool and very cost effective transaction rates.

SQL Server 6.0 Splash Screen

SQL Server 6.0

SQL Server 6.0 is a rewrite of the original SQL Server product that takes advantage of the Windows NT Operating System and allows remote management of a collection of enterprise wide servers. Microsoft are following an independent path from Sybase and have incorporated advanced features such as Replication and support for multi-processor hardware in this version.

SQL Server 6.5

 SQL Server 6.5 contains several performance improvements particularly in areas where many users are accessing the same portion of a table for updates. This improves various contention scenarios when many users are attempting to add records and compete to add sequentially to a clustered index for example.

Replication is also much improved and can now replicate with other ODBC data sources as well as interface with Oracle or other more complicated corporate situations.

SQL Server 7.0

SQL Server 7.0 re-engineered the product to use native Windows NT files for a more logical integration with backup systems. Many of the configuration parameters became ‘self-tuning’ to avoid the need for a DBA on smaller systems.

SQL Server 2000

More improvements for the DBA including an index tuning wizard which suggests potential indexes to be placed on tables. Introduction of user defined functions and partitioned views and functionality to support XML.

SQL Server Features

SQL Server is a fully fledged relational database server that runs on all versions of Microsoft Windows. The server software is licensed from Sybase and there is a high degree of compatibility with large scale Sybase servers.

Transactions

Many DBMS allow for the concept of a transaction which is a programmer defined unit of work. The programmer defines the beginning and the end of the transaction and any changes made to the data in the database are logged in a transaction log until the programmer completes the transaction with a Commit command. The database will then write all of the transactions into the database. If there are any problems in completing the transaction, for example a record locking deadlock occurs with another user, then the DBMS will Rollback the database as if the transaction never happened.

The transaction log may also help with database recovery in case of a hardware failure in that the database can be rolled forward using the transaction log from a previously saved state until the last fully completed database transaction.

Correct use of programmer defined transactions allows for the data stored in the database to be correct at all times even if a hardware failure interrupts the program flow.

Data Dictionary

Usually an RDBMS will support a data dictionary. This is a set of tables which are stored in each user database and are referred to as system tables. SQL Server maintains several system tables each containing information about different parts of a database. For example a system table, called SYSINDEXES, exists in each user database which contains information about all the indexes set-up on tables across the users database.

These system tables can be queried and viewed like any other table but are usually hidden from the user to avoid confusion and can also be accessed using system stored procedures.

Constraints

Constraints may often be defined in a Database to allow data to be checked by the database software before it is added or modified in the database.  This has the advantage of ensuring that data is always valid as a program cannot pass in data that breaks a constraint and also allows these checks to be implemented once in the database software rather than in each application that updates the database.

Structured Query Language

The structured query language (SQL) used in SQL Server is very similar to the ANSI SQL standard. Following are a few examples of SQL commands, more detailed explanations of the commands available can be found in the Transact-SQL Reference manual supplied with SQL Server.

Creation of a table:

CREATE TABLE contact

(contact_id cid,

name varchar(30),

address varchar(60),

telephone_number varchar(20),

rating tinyint)

Insertion of a record into a table:

INSERT INTO TABLE contact

   (contact_id,

   name,

   address,

   telephone_number,

   rating)

   VALUES

   ('00003215',

   'John Brown',

   '67 North Street, Guildford, Surrey',

   '0327-7384629',

   8)

Updating of a record:

UPDATE contact

   SET telephone_number = '01327-7384629',

   rating = 9

   WHERE contact.contact_id = '00003215'

Deletion of a record:

DELETE contact

   FROM contact

   WHERE contact.contact_id = '00003215'

In addition to the standard SQL functions, SQL Server supports extensions to the traditional syntax to allow the implementation of outer joins and other enhancements to the language.

SQL Server also implements a programming version of the SQL language known as Transact SQL which is used in the definition of program scripts for triggers and stored procedures.

Enterprise Networking

SQL Server is part of the BackOffice suite of programs designed to run on Microsoft NT Advanced Server. The technology is suitable for Enterprise Networking where many NT Servers are situated throughout an organisation connected together in a Wide Area Network.

SQL Server may be installed on some or all of the servers to provide departmental databases. Programs may access more than one database if required. In addition, a SQL user may be configured as a remote user on another server so that the two servers communicate by automatically logging the user onto the second, remote, server to allow access to data.

Further facilities such as security that is integrated with network security, replication of data between servers, remote administration of servers from a workstation, and integration with electronic mail make SQL Server a good choice for a multi-server networked environment.

Administration

Administration of the server is performed through the SQL Enterprise Manager which allows for the management of any server on the LAN or WAN using client software running on Windows 95 or Windows NT.

Microsoft have implemented software components which can connect to SQL Server administration functionality for programmatic control of complex administration and system management.

SQL Server has a task management program that schedules activity at regular intervals. This activity includes the implementation of replication triggers which replicate data between servers.

The current Tasks can be viewed with the Tools-Task Scheduling… menu option in the SQL Enterprise Manager.

Task Scheduling Window

User security may be automatically inherited from the Network Configuration and the specification of physical devices for the database is fairly straightforward. The database can take advantage of sophisticated operating system features such as RAID fault tolerant disks to supplement the security features of mirrored transaction logs.

Connectivity

Microsoft provide the latest ODBC and OleDB drivers for SQL Server to provide some of the highest connection speeds available from a variety of programming environments. ADO ActiveX data object provide a convinient way to manage OleDB data sources and a well integrated with a SQL Server environment.

Gateways exist to transparently connect a request for SQL Server data through to a Mainframe database.

Views

Views on data may be easily defined to allow local or global corporate database schemas to be defined and yet allow for the underlying local structure to be changed if required without affecting existing programs.

Triggers

Triggers allow programs to be executed on the server whenever data is updated to prevent updates or to perform processing,

Stored Procedures

Stored procedures are programs that run on the server using an enhanced form of SQL called Transact-SQL. This includes program control functionality and the facility to call external programs residing on the server such as electronic mail.

There are considerable benefits in getting the server to perform tasks rather than calling a workstation process. This is particularly relevant in high transaction systems which interface with other components of the computing infrastructure as network traffic is not a bottle-neck when the processing is performed solely on the server,

Replication

Data is published on one server and other servers are defined as subscribers to that data. The SQL Executive copies data regularly during the process of database synchronisation. Replicated data is not modifiable on the subscription databases.

User Defined Functions

User defined function (new in SQL 2000) allow Transact-SQL to be used to create a program that returns a single value or one that returns a cursor. The former allows re-usable functions to be used in program implementation and the latter provides a programmatic alternative to defining a View.

XML

XML has considerable support in SQL 2000 and allows fully formed XML files to be returned directly from a server stored procedure without the need of any further middleware.

HTML

SQL Server has been integrated with Internet Information Server to serve directly to an http: request. This can provide powerful functionality when used together with stored procedures return XML in combination with an XLST formatting file.