CODEWITHSUNDEEP
sql-serverindexing
NewPy
NewPy

Reputation: 663

non-unique index vs full table scan

When a column is non-clustered index column, its key-row address relationship is stored somewhere else.

I understand if the index column data type is numeric, SQL server is using B-tree searching mechanism to find the answer. How about when the column type is text, especially there can have duplicated values in the index?

What is the difference between SQL searches a separate table with key-row address pair and non-index column of the table?

Upvotes: 0

Views: 642

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89090

How about when the column type is text, especially there can have duplicated values in the index?

There is no difference in how nonclustered indexes on varchar/nvarchar values are handled. Although the maximum key size is 1700 bytes.

And for duplicate values, all BTree indexes are actually stored as unique indexes. If the index is non-unique, the clustered index key is added to each key value on the non-leaf pages. See Geek City: More About Nonclustered Index Keys

What is the difference between SQL searches a separate table with key-row address pair and non-index column of the table?

Very little. If you look at the query plans you'll see what is essentially a join between the non-clustered index and the clustered index, the same as would be between a separate table and a clustered index.

Upvotes: 1

Related Questions