I've looked around a bit but haven't come across anything talking specifically about MS Access. So: what, if any, is the performance difference between a field with a constraint and a field with an index?
For example, I can define a field F to be non-null in three different ways as far as I can tell:
-- (1) create table TestTable ( ID autoincrement primary key , F int not null );
-- (2) create table TestTable ( ID autoincrement primary key , F int , constraint cnnF not null (F) );
-- (3) create table TestTable ( ID autoincrement primary key , F int ); create index idxF on TestTable (F) with disallow null;
All three end up disallowing null values in F. But what specifically is happening when I use the first two methods as opposed to the third?
(1) Does Access internally create a constraint here? Or an index?
(2) Does Access internally create an index when I specify the constraint? Does (2) have the same performance as (3) if I'm doing lots of querying e.g. with WHERE clauses on F?
The ultimate reason for this question is laziness: I'm wondering if I can get away with specifying indexes for my fields by just using the CONSTRAINT clause in the CREATE TABLE statement, instead of having to chain together several SQL statements in a VBA sub/function.