In my sql server code, I create an secondary index for two columns. But I was wondering would be the syntax in Teradata Studio. For example, here a sql server code I have now:
create index [ndxID1]
on TEMP (ID1)
create index [ndxID2]
on TEMP (ID2)
In Teradata the syntax is
CREATE [UNIQUE] INDEX index_name (column_name(s)) ON table_name;
which results in
CREATE INDEX ndxID1 (ID1) ON TEMP;
Don't forget to Collect Statistics and then check Explain if the index is actually used.
And check if the index is really required! Teradata is creepy fast with full table scans. And a lot of queries run via de primary index. In general we do not have many indexes on our tables...
Thanks for the quick replied back. I ran the secondary quiey code and it ran fine yesterday. But, when I tried to run this secondary index again today, I get the following error message:
Failed [5321 : HY000] Constraint, primary index, or secondary index with the same name 'ndxID1' already exists in table.
Anyway to resolve this issue?