Recommended usage for the CMP Index Type.
The CMP index can be created on columns that are NULL, NOT NULL, or a mixture. The CMP index cannot be unique. Note that numeric and decimal data types are considered identical. You may create CMP indexes on them when precision and scale are identical. For CHAR, VARCHAR, BINARY, and VARBINARY columns, precision means having the same column width.
For example, the following commands show how to create a table, then create appropriate Compare indexes:
CREATE TABLE f(c1 INT NOT NULL, c2 INT NULL, c3 CHAR(5), c4 CHAR(5))
CREATE CMP INDEX c1c2cmp ON f(c1, c2)
The following index is illegal because the columns indexed are not of the same data type, precision, and scale:
CREATE CMP INDEX c1c3cmp ON f(c1, c3)