Exclude NULL value from UNIQUE INDEX in HANA

user_7063536 0
Viewed 363

I Need to create a Unique Index in HANA with nullable column. I need to exclude NULL value from Index.

In SQL SERVER I can create an Index with this sintax:

CREATE UNIQUE NONCLUSTERED INDEX [MyTableIX_] ON [dbo].[MyTable]
(
    [MyField1] ASC,
    [MyField2] ASC,
    [MyField3] ASC
)
WHERE ([MyField1] IS NOT NULL AND [MyField2] IS NOT NULL AND [MyField3] IS NOT NULL)

How can obtain the same result in HANA?

hana
1 Answers

AFAIK This is not possible as a UNIQUE index requires that all of the entries are unique at the time the index is created, and will prevent records being added which would create duplicate entires in the index. (The documentation explains this)

Most Database systems work this way- unique means unique.

However, if your table is a column store (most are in HANA) then do you really need to create this index? The Column store optimises the table for retrieval of data (which is why in HANA generally reads are so much faster than writes) so for retrieval the use of index may not make any significant difference.

If you want to enforce uniqueness you could implement a trigger on the table instead which would abort the insert or update if it finds any records which conflict.

The "Filtered Index" syntax for MS SQL Server is intended to optimised retrieval for a particular subset of records in the table so that when the filter applies an index can be used which does not have to cover all rows of the table - resulting in a shorter index and a (hopefully) faster query.

Given that for column store tables (most tables in HANA) every field is effectively indexed the need for optimised indexes for subsets of the table is reduced (probably to zero, depending on the data schema and values).

user_108790610
Related