Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

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.

share|improve this answer
    
Thanks. That shed some light on the logical differences. I want to wait a little bit and see if someone can comment on the internal behaviour e.g. index auto-creation for constraints. –  Yawar Feb 22 '13 at 15:59
    
The only case of index auto-creation on a constraint is the primary key constraint to the best of my knowledge. Now constraint creation may automatically cause a statistic to be created but I'm not sure. –  Kenneth Fisher Feb 22 '13 at 16:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.