On 14 Sep, 01:55, Michael Gramelspacher wrote:
Why would I want memo fields to be indexed?

I have almost no practical experience, and I really am
wondering because I have read this many times before.

One reason is the 255 chracter limit on NVARCHAR AND NCHAR columns.

One government agency schema I must work with has a wide key; let's
say it's up to 1000 characters. In Jet, this column would be best IMO
as a MEMO column (yes I know it's blob data but surely that's better
than scaling my own NCHAR/NVARCHR columns?)

Values are unique in the domain so naturally I'd want a UNIQUE
constraint in my db. The SQL DBMSs I work with all use indexes to
implement such constraints, as does Jet. Also, I have to search, join,
etc on this column so why _wouldn't_ I want to index it if possible?
Hint: they agency chose such a wide key because the number of entities
in the domain is vast (they have since realized they didn't need such
a wide key but the seed has been sown) and users could have literally
millions of values in the db...

...SQL Server Express...

I'm glad *you* mentioned that g. In SQL Server I can create a
calculated column to store a hash of the key value with a constraint
to ensure the hash is a match for the value, and put an index (dupes
allowed, obviously) on the hash column. I can then use both the value
column and the hash column in a trigger to ensure uniqueness and the
index on the hash column yields acceptable performance. Obviously, the
wise may use both columns in regular SQL JOINs, WHERE clauses etc to
ensure the index on the hash gets used. This is partially achievable
in Jet (e.g. the hash column and index), more so in Access (e.g.
validation rule to ensure the hash is a match) but not everything
(e.g. the trigger to ensure uniqueness).

Jamie.

--