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
Solved! Go to 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--
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.
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.
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.
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--