The need for user-defined index types

Since the removal of the 8KB limit on serialization, the ability to define new data types using SQL CLR integration is now almost at a usable level, apart from one key omission: indexes.

We have no ability to create our own types of index to support our data types. As a good example of this, consider that when Microsoft introduced the geometry and geography (spatial) data types, they did so as system CLR data types but also needed to introduce a spatial index as a new type of index. 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.

What would have been far preferable would have been for the ability to create user-defined indexes to have been added to the product and for spatial indexes to have been one instance of that.

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.

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 you’d like to see user-defined index types be considered in the future, you know what to do. Vote once, vote often :-)

https://connect.microsoft.com/SQLServer/feedback/details/650268/introduce-user-defined-index-types

2011-03-09