THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

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

 

Published Wednesday, March 09, 2011 1:59 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

opc.three said:

Indexing calculated columns came to mind as I was reading your article...can you please elaborate on "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."?

March 9, 2011 1:12 PM
 

Greg Low said:

Hi, you can create a persisted calculated column that is based on retrieving a property or calling a method on a UDT. Those properties can then be indexed. While this is tedious to set up, it is useful to a degree. However, it's not the same as being able to index the data type directly. If you consider the spatial data types, ask yourself how much more powerful the spatial indexes are than an index on the lat and long properties.

March 9, 2011 6:40 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement