DWHPro Insights

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

By Roland Wenzlofsky ·

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

SQL Server clustered indexes store the table rows at the B-tree leaf level. Range seeks and Teradata partition elimination can both reduce work, but they are different access mechanisms. Neither storage organization guarantees SQL result order; use ORDER BY.

A clustered index replaces heap storage, while upper tree levels, free space and maintenance consume resources. Teradata row partitioning adds partition identifiers and can change block fill and storage overhead. Compare measured space and access costs rather than assuming either design is free.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

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.

Indexing is only one of the places the two systems diverge; the wider picture is in the architectural comparison of Teradata and SQL Server.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam