Filter Unique Index

[ssharathc]

I have a table with an ID column. The ID column can have many NULL values but all non-NULL values should be unique. Is it possible to create a USI but ignore NULL values. SQL server has a concept of filter index where certain values can be ignore while creating index. Looking for something in teradata.

Thanks

Sharath

[GJColeman]

This might be a job for a single-table join index.  Look in your Database Administration or Database Design manual, and see the SQL DDL manual for create syntax.

[ssharathc]

Didn't find the exact syntax or example on this. If you have any pointers or enocuntered such issues in prod, please let me know.

[GJColeman]

16.x doc is at https://info.teradata.com/HTMLPubs/DB_TTU_16_00/Database_Management/B035-1094-160K/qbk1472240588626.... - If you go to info.teradata.com you can find manuals (with examples) for different releases, in PDF or HTML format.  Note there are many kinds of JI's - single-table is one; sparse might be another option.

[FGroenen · Accepted solution]

Something simple like;

CREATE JOIN INDEX JI_Name AS

SEL <list of column names> FROM Your_Table

WHERE Your_Column_name IS NOT NULL --this makes it a Sparse-JI

UNIQUE PRIMARY INDEX (Your_Column_name);

  --note check for correct syntax--

[ssharathc]

Thank you FGroenen. That worked.