In this article, we will compare the index types of SQL Server and Teradata. For those switching from SQL Server to Teradata (or vice versa), it can be quite helpful to see the differences and similarities, even though we are dealing with two completely different architectures.
Clustered Index vs Teradata Row Partitioning
The clustered index on SQL Server is the table itself implementing a specific physical order of rows. It is typically used for range-based queries; from a functionality point of view, we can compare the clustered index with Teradata Row Level Partitioning. In both cases, the goal is to sort the rows on the disk so that range queries are optimized. For both Teradata and SQL Server, no additional space is required (But Teradata needs 2 or 8 additional bytes to manage the mapping to partitions). However, the Teradata Row Partitioned Table can additionally be queried efficiently via the Primary Index. It should be mentioned that a clustered index is not mandatory, and the data is then stored unsorted.
Nonclustered Index vs Teradata NUSI, USI, Join Index
The non-clustered index on SQL Server is an index based on B-Trees that contains a subset of table columns. It’s typically an excellent choice for retrieving a small subset of rows.
In Teradata, several index types are available for this purpose. USI, NUSI, Join Index, Hash Index. However, the design is completely different.
Both Teradata and SQL Server can obtain the data they need exclusively from the index without accessing the base table. For this, however, all columns must be present in the index.
Teradata Sparse Join Index and SQL Server Filtered Index
Often we do not want to index all values of the Index Columns. For example, if there are many NULL values in the indexed columns. This can save space. Teradata offers the Sparse Join Index, SQL Server the Filtered Index.
Different types of index usage
Both Teradata and SQL Server can query an index in different ways.
Querying individual index values is called Seek with SQL Server, with Teradata, it is accessed via the ROWHASH (Primary Index Access, USI Access, NUSI Access via Hash).
The query of ranges of the index is possible both with the SQL server and in Teradata. In Teradata, the value ordered index (NUSI, value ordered join index) can be used here; alternatively, row-level partitioning is also possible.
If the coverage is not given by the index, a lookup of the base table can always be performed using USI and NUSI. The join index can be designed accordingly. The query of additional columns in the SQL server is called lookup.