Comparing Index Types of SQL Server and Teradata: Clustered vs Row Partitioning, Non-clustered vs NUSI, USI, Join Index, and More

This article compares the index types in SQL Server and Teradata. It can benefit those transitioning between the two platforms to understand their distinctions and overlaps, despite their different architectures.

Clustered Index vs Teradata Row Partitioning

The SQL Server’s clustered index arranges table rows in a specific physical order, making it ideal for range-based queries. Functionally, it is similar to Teradata Row Level Partitioning.

The objective is to arrange the rows on the disk for optimal range query performance. Teradata and SQL Server accomplish this without requiring extra space, although Teradata does require an additional 2 or 8 bytes for partition mapping.

The Teradata Row Partitioned Table can be efficiently queried through the Primary Index. Notably, a clustered index is not required in SQL Server, resulting in unsorted data storage.

Nonclustered Index vs Teradata NUSI, USI, Join Index

SQL Server’s non-clustered index is a B-Tree that stores a subset of table columns and is ideal for fetching a few rows. Teradata offers various index types, including USI, NUSI, Join Index, and Hash Index, each with a distinct design.

Index Coverage

Teradata and SQL Server can retrieve required data solely from the index without accessing the base table, provided all columns are included in the index.

Teradata Sparse Join Index and SQL Server Filtered Index

Sparse Join Index and Filtered Index are indexing options that allow for the exclusion of certain values in the Index Columns. This is particularly useful in cases with numerous NULL values in the indexed columns, as it helps to save space. Teradata provides the Sparse Join Index, while SQL Server offers the Filtered Index.

Different types of index usage

Teradata and SQL Server both support different ways of querying indexes. SQL Server refers to querying individual index values as “Seek”, while Teradata utilizes ROWHASH for Primary Index Access, USI Access, and NUSI Access via Hash.

The index ranges can be queried using either the SQL Server or Teradata. Teradata offers the option of using the value-ordered index (NUSI, value-ordered join index) or row-level partitioning. If the index does not provide full coverage, the base table can be looked up using USI and NUSI. The join index can be designed accordingly. In the SQL Server, querying additional columns is called a lookup.