SQL Server Identity Column vs VFP AutoInc Field

In  order to have an efficient table structure which minimizes data redundancy, developers have employed normalization in which tables are broken down into several tables connected together by a certain field which is either a primary key or a foreign key for the purpose of RDMS.

This is being done not only to avoid data redundancy but also to keep the size of the table small as possible especially if you are working with VFP free tables which have a maximum of 2GB filesize per table.

I have come to learn the use of the primary key (PK) and foreign key (FK) from www.foxite.com when I was younger and in VFP, we heavily make use of a special field called AUTOINC (auto-increment) field.

The AUTOINC field, as the word implies by itself, is just a field which automatically increments itself after a successful INSERT INTO command. The addition of this in VFP 9 has since freed the developers from the agony of maintaining a cumulative number which is used as a PK or for in any invoicing purposes.


The AUTOINC parameter is unique to VFP SQL and it's use is pretty straightforward--that is, to increase a number by a  certain number which can be one or more. To use the AUTOINC feature in a VFP table, here's an example:

Visual Foxpro:

Create Table Table1 (PKID Int Autoinc Nextvalue 1000 Step 1, dNow DateTime)

INSERT INTO Table1 (dNow) VALUES (DATETIME())

Table1 with first column named PKID and is an autoinc field beginning from 1000.

In SQL Server, there is no AUTOINC keyword but there's the IDENTITY column which is similar to VFP's AUTOINC. It also needs a seed value and the step value. To use the IDENTITY column in SQL server, here's how:

SQL Server:

CREATE table test2.dbo.table1 (PKID INTEGER IDENTITY (1000,1) PRIMARY KEY, dNow DateTime)
GO

Insert INTO Table1 VALUES (GETDATE())
GO

Table1 with first column named PKID and is an IDENTITY COLUMN beginning from 1000.

Pretty much these two are the same and are useful for generating unique ID's or for invoicing. Both are Integer data type but the difference is that in VFP, you must specify the field(s)  in which new records are to be inserted. So the VFP SQL command below won't work...

INSERT INTO Table1  VALUES (DATETIME())

...because VFP will require you to supply an additional parameter for the field other than the AUTOINC field and in this case, it's the datetime column.

Whereas in SQL Server, you can bypass the name of the IDENTITY COLUMN and the target field to be inserted provided that the supplied values are correct. The command below works without specifying the column name for the datetime.

Insert INTO Table1 VALUES (GETDATE())

Both AUTOINC in VFP and IDENTITY column in SQL Server have been proven to be useful whether it is simply for  generating PKID's or for producing unique numbers.