Ms-access – Microsoft Access – performance difference between index and constraint

constraintindexms access

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
);

or:

-- (2)
create table TestTable (
  ID autoincrement primary key
, F int
, constraint cnnF not null (F)
);

or:

-- (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.

Best Answer

Constraints and indexes are two entirely different things. A constraint defines what can go in the column and an index creates an internal structure to help performance.

-- (1)
create table TestTable (
  ID autoincrement primary key
, F int not null
);

This is part of the table/column definition. Probably the easiest and most common way to restrict a column from null values.

or:

-- (2)
create table TestTable (
  ID autoincrement primary key
, F int
, constraint cnnF not null (F)
);

This is a constraint. This will also work but it's really meant for things like making sure a char(1) column only has a Y or an N in it.

or:

-- (3)
create table TestTable (
  ID autoincrement primary key
, F int
);
create index idxF on TestTable (F) with disallow null;

This is an index. As a side effect it is disallowing null but really it's purpose is is to speed up your queries.

I hate to say it but lazy isn't going to work. I would put null/not null definitions into the column definition. Then use constraints on columns where you want to restrict what values go into the column, not if it's null or not. Then last but not least add indexes where needed for query performance.

Related Question