Filter Unique Index

Database

Filter Unique Index

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


Accepted Solutions
Junior Supporter

Re: Filter Unique Index

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--

 

Teradata Master Frank
1 ACCEPTED SOLUTION
5 REPLIES
Teradata Employee

Re: Filter Unique Index

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.

Re: Filter Unique Index

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.

Teradata Employee

Re: Filter Unique Index

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.


Junior Supporter

Re: Filter Unique Index

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--

 

Teradata Master Frank

Re: Filter Unique Index

Thank you FGroenen. That worked.