This session is designed for delegates considering a move to a client server platform. An active demonstration of the major features of the SQL Server database management environment and the client-server features of Visual FoxPro form equal parts of the session.

The user interface of the SQL Server Enterprise Manager is explored during the creation of a client-server database. A table is created with fields, an index and default values. A primary key is then created and a referential integrity rule defined in the database. The process of defining a database with the SQL Enterprise Manager is compared and contrasted with the FoxPro database designer tool and some problems with upsizing noted.

Visual FoxPro remote views are the simplest way to access client server databases with Visual FoxPro. The advanced options of the FoxPro view designer are reviewed and properties set to optimise the performance of the view. The definition and use of a parameterised view to select a few records from a database is covered in detail.

Further server side implementation techniques are discussed and demonstrated to allow sophisticated data validation on the server using a Trigger programmed with the Transact-SQL programming language. A discussion of overall client-server design issues and the use of SQL passthrough and stored procedures running on the server concludes this technical overview.

20 December 1996 - Version 3.6

All rights reserved. No part of this handbook shall be reproduced, stored in a retrieval system, or transferred by any means electronic, mechanical, photocopying, recording, or otherwise without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this handbook, the publisher and author assume no responsibility for errors or omissions. Neither is any liability assumes for damages resulting from the use of the information contained herein. For information, address RED, 21 Great Russell Mansions, 60 Great Russell Street, London WC1B 3BE, England.

This section describes the features available in the SQL Executive Manager to define tables and fields and the relationships between tables. The properties for each field are described including field constraints and defaults, nullability, and rules and defaults. User Defined Datatypes are also discussed and the creation of Primary Keys and Referential Integrity relationships between Tables.

Tables are part of a Database and may be specified by selecting the Objects outliner for the required database.

This contains several items including Tables and Indexes which are used for Table definition.

Rightclicking on the Table item will allow the New Table option to be selected which produces a window allowing a new table to be defined.

SQL Sever has 17 predefined field types one of which must be used for each field.

Each field must have a field type. SQL Server field types are copied from the MODEL database each time a new database is created and may be added to by defining user defined field types.

The default field types include:

Selection of the correct field type is very important and requirements will vary according to the business requirements of the application. A market research database may have very large amounts of data and numeric field types should be selected with a view to the storage requirements. A SMALLINT integer field will need less space than a FLOAT field for example.

Numeric accuracy is important in financial applications and allowance should be given for the requirement to store fractions as decimals particularly for stock market applications. Some fractions may require several decimal places to be sorted accurately.

The VARCHAR field type allows character data of variable length to be stored at the cost of an extra bit for each value to store the width of the field. This field type is not appropriate for short field lengths or for fields where the width is relatively constant throughout the table.

TEXT and IMAGE fields store data in 2K chucks by default. It is recommended that Null values are permitted if there are a large number of empty values in the table. In some applications it may be more efficient to store the data as external files and use network protocols to access them.

Primary keys may be created from the Manage Tables window by using the Advanced Features setup.

Double-click on the Table and bring up the Manage Table window. Press the Advanced Features button and ensure that the Primary Key pageframe is selected.

Select the required field as the Primary Key index expression and build the Primary Key. SQL Server will automatically name and create the Primary key Index for you.

This section introduces the functionality available within SQL Server to implement data validation rules at both the field and the record level. The following functionality is introduced:

More sophisticated validations for referential integrity or those that require small programs to compare values from other fields or tables are discussed separately in the sections on referential integrity and triggers.

Fields are defined by selecting a Table in the outliner for the Objects of the required Database and rightclicking on the Edit option to bring up the Manage Tables window.

New fields are added by entering the new field definition at the bottom of the scroller. The name of existing fields can be changed and their widths altered by changing the values. Be careful when shrinking field sizes as data may be lost.

Defaults can be specified for a field by entering a constant in the column for defaults against the field. These are constant values specified for the individual field only.

Pressing the Save Table button saves any changes to the table. Definitions of keys and other table settings can be displayed with the Advanced Features option as discussed in the Referential Integrity section later.

Relational database theory differentiates between a null value for a field and a zero value. SQL Server can determine whether a value has never been entered against a numeric value, for example, or whether a zero has been entered by the user.

If you are creating a table and the column you are adding should not allow null values, select the Nulls box to remove the Nulls tick. If you are modifying a table, you cannot change the Nullability of an existing column and all new columns must allow null values.

Specifying that nulls are not allowed will fail an insert transaction that attempts to add a record without a specific value entered against particular fields. This is useful for forcing entry of numeric values, foreign key or lookup fields, and status flags.

The Nullability of a field can only be set when the table is first defined. Nullability cannot be changed once the table is defined and all new fields allow Nulls. Note that fields that accept Null values cannot be used as Primary Keys.

Null values are important in relational theory but can cause problems in application development if not used correctly. Arithmetic and Boolean operation on Null values can yield unexpected results.

Defaults may be defined against a field to automatically enter a value when a new record is inserted into the table if the application has not entered a value.

Defaults can be useful with fields that are defined as NOT NULL as SQL Server will abort the insert if a field value is not entered against the field by the application. Binding a default to the field will automatically enter a value if none is supplied by the application.

Defaults may be set by typing a value in against the DEFAULTS column when defining a field with the Manage Tables Window. This window is available when clicking on the required Table object in the SQL Enterprise Manager. The default must be specified as a constant value.

Check Constraints allow the definition of a simple piece of logic to check the values to be entered in a field. The AUTHORS table could be altered to prevent further entry of any authors from Texas by setting a constraint which prevented the state field from being set to TX.

Check constraints may be entered with the SQL Enterprise Manager by selecting the appropriate Table object and rightclicking to edit the table. Pressing the ADVANCED FEATURES push button will bring up a pageframe which allows various constraints to be set. The Check Constraints page allows for simple checks to be made on the data.

SQL Server will not allow a value to be entered or modified that conflicts with the check constraint.

SQL Server indexes allow the database engine to optimise retrieval of data from the database. All fields that are often used in queries should be indexed and each table should have a unique identifier that serves as the primary key.

Indexes may be defined on a single field or a set of fields as a composite key which may be useful for optimising queries or for sorting data for a report or batch processing tasks.

A single clustered index may be defined on a table that physically sorts the records into the index order. This can speed up performance on a table if sequential access to a set of records is often needed in the sequence of the clustered index. Retrieval of individual records is not improved by a clustered index.

There are more advanced options, discussed below, that can be defined against an index but are initially best avoided as they may result in part of a transaction failing with no indication to the user that some records have been ignored by the database engine.

Indexes are defined with the Manage Indexes window available by rightclicking on the required Table in the SQL Enterprise Manager and selecting the Indexes option. The fields for the Table are displayed and can be moved into the Index by clicking the Add button. More than one field can be added to create composite keys.

SQL Server indexes can be defined to ignore certain problems when inserting new records into a table. The Ignore Duplicate Row option causes records with duplicate rows, where a clustered index has been defined, to be ignored during a transaction without failing the whole transaction. In this case, duplicate rows are not inserted into the table but the remaining records are processed.

Similarly with the Ignore Duplicate Key option, attempts to insert a record with a duplicate key that has been defined as a unique index, will ignore only that record and continue with the remainder of the transaction.

The Allow Duplicate Rows option contradicts the principle of a primary key for each record and is required only in unusual circumstances.

Clustered Indexes allow for the Sorted Data checkbox to be specified so processing time is not wasted sorting the Index. The Index is not created however if the data is not sorted correctly.

Primary and Foreign keys are used to define relationships between tables. These fields can be defined as keys in the database definition and used to create relationships between tables that are enforced by the database.

A primary key uniquely identifies are record in a table. The primary key may be composed of one or more records and is defined as an Index which allows only Unique values.

The Advanced Features window of the Manage Tables windows allows for a Primary Key to be defined for a Table. Selecting the Advanced Features brings up a window with several pageframes. The Primary Key pageframe allows for the definition of a primary key.

The Primary Key combo allows for the selection of any field that does not allow null values. Composite Keys can be defined by selecting a second column for the Primary Key. Select the Clustered option if the table is to be physically ordered in the sequence of the Primary Key and then choose the [Add] button to define the Primary Key.

An index is automatically defined for the expression and a Key symbol appears next to the fields to show that the Primary Key has been defined.

There may be more than one Candidate Key that uniquely identifies a record in a table. For example, Employee Identifier or Social Security Number may be used to uniquely identify a record in a table of Staff members. Database designers will often use a virtual Primary Key which has an automatically generated number in this situation and allow candidate keys as an alternative access path.

These alternatives may be implemented as unique Indexes and defined in the Manage Indexes window.

Foreign Keys are the other half of a relationship between tables and link a child table to a parent table. The Foreign Key value should match directly to the value of the Primary Key.

Foreign Keys can be defined in SQL Server to automatically maintain the referential integrity of the table. These keys may be defined in the Advanced Features portion of the Manage Tables window provided that the Primary Key for the parent table has already been defined.

To create a Foreign Key, select the child table in the Enterprise Manager and rightclick to bring up the Manage Tables window. Select the Advanced Features and choose the Foreign Key pageframe.

Select the <new> option for the Foreign Keys combo and select the Primary Key Index of the parent table as the Referenced Table.

The fields that make up the Primary Key of the parent table are displayed in the scroller and require that the corresponding foreign key fields from the current table are entered.

Pressing the Add button will add the Foreign Key which will now automatically maintain referential integrity for the tables.