At 05:19 PM 28/03/2009, you wrote:

>> (CHAR (N) is the wrong choice of data type for this, by the by,
>> although it doesn't affect the max. size of the index).
>
>Why is that? I thought having a fixed-length string would be better since look ups would just require some simple arithmetic.

"Look ups" is a client-side thing. In SQL, you're typically doing equivalence tests that return true or false (or bad equivalence tests that return null/unknown).

A CHAR(N) and a VARCHAR(N) containing the "same" characters are not equivalent. To give a simple example, suppose you have column A VARCHAR(10) column B CHAR(10). Suppose you place the string 'magic' in both columns, viz.

update aTable
set A = 'magic', B = 'magic'

then you wanted

select .... from ...
where atable.A = atable.B

then that row you updated back there would not be returned. Why? because the data are not equivalent. The varchar column stores the characters + two end bytes that store the total number of characters that are "used". The char column stores the characters plus a blank for each "empty" slot between the last character and the full defined length of the column.

Use CHAR only for strings whose actual length is prescribed by your design requirements - the usual example is the US social security number (whose length I don't know). Various barcode formats (though not all) have prescribed lengths...and so on.

BTW, it's not about how much storage they take up on disk. Firebird compresses both CHAR and VARCHAR in storage.

>Though having thought more about it, I guess that having to compare two 260 char strings would be slower than comparing their lengths and then the strings themselves.

It's not even about speed. It's about whether they match or not. Blanks are characters! Using hyphens here to represent blanks (which the engine doesn't, of course!)

'magic' <> 'magic----'

>> Another comment: that's not a valid table declaration anyway...you
>> have to declare a FOREIGN KEY constraint explicitly and it's not a
>> great idea to try and declare FK constraints in the same
>> transaction as you define the table...it will likely cause your
>> next exception, after you deal with the index overflow issue.
>
>Ah, thanks for the heads up. I've never really used databases before and guides for getting started don't mention this kind of stuff, so I just worked off the IB 6 language reference for what was and wasn't possible.

It's wrong according to the LangRef, though.

./heLen