How does an index for SQL User-Defined Type (UDT) work?

Question

This has distorted me a bit, and I hope one of the SQL Server experts can shed some light on it.

The question arises:

When you index a SQL Server column containing a UDT (CLR type), how does SQL Server determine which index operation to perform for a given query?

In particular, I am thinking of a hierarchyid type (AKA SqlHierarchyID ). The way Microsoft recommends using it — and the way I use it — is:

• Create an index in the hierarchyid column itself (call it ID ). This allows you to search by depth, so when you write WHERE ID.IsDescendantOf(@ParentID) = 1 , it can do an index search.

• Create a constant computed Level column and create an index on (Level, ID) . This allows you to do a breadth-first search, so when you write WHERE ID.GetAncestor(1) = @ParentID , it can search for the index (at the second index) for that expression.

But what I do not understand is how is this possible? . This seems to violate the usual rules of the query plan - the calls to GetAncestor and IsDescendantOf do not look impregnable, so this should lead to a full index scan, but this is not the case. Not that I complained, obviously, but I'm trying to figure out if it is possible to reproduce this functionality on my own UDTs.

Is hierarchyid just a “magic” type that SQL Server has a special awareness of and automatically changes the execution plan if it finds a specific combination of query elements and indexes? Or is the CLR type SqlHierarchyID just defining special attributes / methods (similar to how IsDeterministic works for constant computed columns) that are understood by the SQL Server engine?

I can not find any information about this. All I could find was a paragraph that stated that the IsByteOrdered property allows you to do things like indexes and constraint checks, guaranteeing one unique view for each instance; while this is somewhat interesting, it does not explain how SQL Server can perform searches using specific instance methods.

So again, the question is: how do index operations for hierarchyid types work, and is it possible to get the same behavior in the new UDT?

Answer 1

The query optimizer team is trying to handle scripts that do not change the order of things. For example, cast(someDateTime as date) can still move. I hope that over time they spark a bunch of old ones like dateadd / lateiff with a constant.

So ... Ancestral Handling really enjoy using the LIKE operator at the beginning of a line. This does not change the order, and you can still leave with things.

Answer 2

You're right. HierarchyId and Geometry / Geography are the “magic” types that the query optimizer can recognize and rewrite plans to receive optimized queries — it's not as simple as recognizing sargable statements. It is not possible to simulate equivalent behavior with other UDTs.

For HierarchyId, binary serialization of this type is special for representing a hierarchical structure in binary order. It is similar to the mechanism used by the SQL Xml type and is described in the research article ORDPATHs: Insert-Friendly XML Node Labels . Thus, although the QO rules for translating queries that use IsDescendant and GetAncestor are special, the actual base index is a regular relational index for the binary data in the hierarchy, and you could achieve the same behavior if you were willing to write your original queries. to select a range instead of calling a simple method.