SQL: The need for user-defined index types

A few days ago, I wrote about SQL CLR and how I don’t normally use it now, but if I did, which types of objects make sense for it. I briefly mentioned user-defined data types but today, I wanted to call out another limitations of these that I’d like to see addressed (if we keep on using SQL CLR).

Early versions of the user-defined data types in SQL CLR had a limitation on size, where they needed to be serializable within 8KB. That limit is now long gone and so the ability to define new data types using SQL CLR integration was now almost at a usable level, apart from one key omission: indexes.

Indexing user-defined data types

We have no ability to create our own types of index to support our data types. And for user-defined data types to really work, we need custom index types that support them.

It was interesting that when I discussed this with the product group back in 2005, they didn’t think it was necessary, so I had to laugh when Microsoft introduced the geometry and geography (spatial) data types. To make them work, they also needed to introduce a spatial index as a new type of index. So much for them not being needed!

But the same has been happening every time they add native types as well. The XML data type has a set of related index types. The JSON data type has JSON indexes. The vector data type has vector indexes.

What do other products do?

Those of us that need to work with the product as it’s supplied can’t just create our own new types of index objects. It would be interesting if we could. Products like PostgreSQL have many interesting index types as a direct result of people being able to create their own.

Other database engines (such as Oracle) have this capability. This makes it impossible to migrate applications that use Oracle Data Cartridges to SQL Server in an effective way. It also just makes the creation of data types in SQL Server that much more limiting than it could be.

Rather than just adding spatial indexes, what would have been far preferable would have been for them to add the ability to create user-defined indexes and for spatial indexes to have been one instance of that.

What to do?

At present, one alternative is to promote properties of CLR data types via persisted calculated columns and then index those but that’s somewhat awkward and more importantly, doesn’t really do the same thing.

If we’re going to still just have SQL CLR as a means of extending the product, I really wish the team would reconsider this aspect of it.

2026-05-06