Adding an idexed column 1

[audiopro]

I need to add an indexed column to an existing table.
I get confused with the different types of indexes and my reference book doesn't make things any clearer.

If I was adding it using the manager I would set it as a regular index but what is the equivalent in the required SQL statement?

This creates the column

ALTER TABLE ORDER2 ADD COLUMN REC N(8)

but how do I create a regular index.
Is it a candidate, unique, primary key or whatever?


Keith

http://www.studiosoft.co.uk

 

[bborissov]

ALTER TABLE ORDER2 ADD COLUMN REC N(8)
SELECT Order2
INDEX ON Rec TAG Rec

That will make a REGULAR index.

1. Primary index - you may have only ONE value in your table based on index expression. One and only. That index expression should define the record as unique value(s)
2. Candidate - Because you may have only ONE Primary index in your table Candidate comes to help. It have the same requirements as Primary index.
3. Regular (most used indexes) - They are used just to sort the data. You may have whatever you want in the field(s) used in Index key.

Keep in mind that ALL indexes counts deleted records also. So you may have errors if you try to insert a record in a table that have primary(candidate) index(es) that double the value of the field(s) used in Primary(Candidate) index no matter if the previous record is deleted or not.

All indexes can be used in SQL query for optimization.
Primary and Candidate indexes are most used to keep tables to match DB design rules.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[audiopro]

Thanks Borislav
I don't do much with VFP these days and I am getting a little rusty.

Keith

http://www.studiosoft.co.uk