4

SQL Server 2008 apparently has filtered indices.

What is the cleanest way to achieve the same in H2?

Community
  • 1
  • 1
Reto Höhener
  • 5,419
  • 4
  • 39
  • 79

1 Answers1

2

I believe you can use a computed column for this purpose . . . assuming you have a unique id. Let me assume the unique id is numeric and never negative. Then:

alter table t add col (case when <condition> then -1 else uniqueid end);

create unique index unq_t_col on t(col);

I believe that H2 supports unique indexes, computed columns, and indexes on computed columns, so this should work.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    Thank you for this suggestion. I will test this approach. Still wondering if there is a solution that does not require an additional table column. – Reto Höhener Apr 23 '17 at 15:14