Rick Debay wrote:
What is the effective difference between these two key elements orderings?
ALTER TABLE T ADD CONSTRAINT U UNIQUE (PK, HIGH_MAX_DUP)
ALTER TABLE T ADD CONSTRAINT U UNIQUE (HIGH_MAX_DUP, PK)
The first version is how I normally order key elements, from most unique to least. Since a primary key index exists, the second would allow HIGH_MAX_DUP to be used in queries. But would ordering the key elements like this have any impact due to the high number of duplicates (it's almost as bad as an even split between Boolean values).
Ann W. Harrison answers:
The key will be smaller if you put the field with lots of duplicates first. Firebird does prefix compression, so the leading part of the key that duplicates the previous value is not stored. The example below show how five different key values would be stored, assuming that 00000 is the first in its page or sector:
00000 is stored as suppressed length 0 key 0000 12345 is stored as suppressed length 0 key 12345 12346 is stored as suppressed length 4 key 6 12346 is stored as suppressed length 5 no key 12356 is stored as suppressed length 3 key 56.
I'd choose ALTER TABLE T ADD CONSTRAINT U UNIQUE (HIGH_MAX_DUP, PK)