Chapter 2 Ordering Records

Sorting

Is a methods of managing  data, which involves physical rearrangement of records in table according to a predetermined order.  After sorting records of a table are copied in a specified order to a new table.

When FoxPro sorts a table, it creates number of temporary files.  Temporary files automatically deleted when sort is completed (The disk space in sorting is consumed it is a disadvantage of sorting.)

Data can be rearranged in ascending or descending order.  Sort can be done on one or more fields [A-Z] or [a-z].  Syntax:

Sort on FieldName[/A/C/D], file name x [/A/C/D] [ascending/descending]  Where

A= Ascending                                  
D=Descending                                             
C=Case

Sort on First_Name to Name.

Sort on Last_Name, First_Name/D to Name.

Sorting on Selecting Records and Fields:

To produce a sorted file containing selected records FOR clause is used.

Sort on Last_Name, First_Name to BOCity for City = “Bombay”

Sort on Mark3 for Mark>75.

INDEXING

Indexing a FoxPro table establishes an order of arranging the records without modifying the original table.  Index is the faster way to access data stored in a table.FoxPro uses index file to quickly look up a value and find the corresponding record numbers.  It also relies on the fact that index is sorted.  Order depends on the type of data stored in an index.

Advantages of Indexing

1)      Indexing serves two purposes.  It keeps data in a certain order & speeds up the searches.

2)      When an index is active FoxPro accesses the index file and then table order of information is determined by index.

Types of Indexes

There are two types of index files.  They are as follows:-

Single Index File

It stores a single index based on a single index expression or key.  Single index file has an extension of .IDX.

Compound Index File

It can store many indexes in a single file, each index is given special name -Tag.  The number of indexes and therefore tags are limited only by available memory and hard-disk space.  Compound index files have an extension of .CDX.  Syntax:

Use Student

Index on RollNo to Roll.

Use Employee

Index on EmpNo to Emps

To Clause creates a Single Index file.

Index on FName  + LName tag Stud.

An index (Stud) is added  to a compound index file.

Index on EmpNo + DeptNo tag Emps1

An index (Emps1) is added  to a compound index file.

Opening An Index Files

Index files can be used at the same time tables are opened by including index names as a part of the use command.

Use Student ORDER tag STUD.

Use Employee ORDER tag Emps1

An order (index file) can be opened independently of the use command with the set Index to Command.

Set Index to Stud.

Set Index to Emps1

Deleting Index Tags.

The Index Tags can be deleted by using delete tag (tag name) command.

Delete Tag Cities.

Delete Tag All.

Delete file.Idx (is used to delete Single Index File).

Str( ) function converts numeric value in a character string. Syntax:

Str(<Numeric Value>[,<length>,Decimal places>].

CtoD ( ) function converts character string to date.  Syntax:

CtoD(<Character String>)

DtoC ( ) function converts a date to Character String.  Syntax:

DtoC(<Date>)

Index on Course +DtoC(Dt_Start) tag CoDate.

Searching Data Using Index

Seek Command is used to find the specified Record.

Difference Between Sort/Index

1)        Sorting Creates another database file with another name.  While Index operation does not rearrange the records in a database file.  It creates a separate index file whose records are arranges based on values of the index key.

2)         Sorting takes lot of disk space.  Index file occupies less amount of disk space.

3)      Sorting is much slower than indexing.  Indexing is the faster way of arranging data in a specified manner.

Back To Index