Implementing A Hash Partition In SQL Server 2005

One of the best things about becoming a Microsoft MVP is meeting other MVPs. I bring this up because last week was the annual MVP Summit in Seattle, WA. I was really looking forward to meeting Steve Kass. Steve Kass is one of the smartest SQL Server MVPs I’ve encountered, especially when it comes to SQL questions.A while back, I noticed that Steve made an interesting recommendation for a hash function that you could use for partitioning that I thought was worth noting.A hash function would be very useful if you wanted to implement your own variation of a range partition using a hash function rather than the standard sort of range partitioning where colA values of A-H go to partition 1, values of I-P got to partition 2, and so forth.

Steve notes that you could use the following for hashing something small in size:

CAST(

SUBSTRING(

HASHBYTES('SHA1',

CAST(my_col AS NVARCHAR(appropriate_size))),8,1) AS tinyint)

This is just an off the cuff recommendation from Steve and might need some fine tuning, for example, the CAST might throw off persistence.However, it’s a good start.

Thanks, Steve, for sharing this and thanks, readers, for sharing any improvements you might develop out in the field.

Comment Notification

Comments

The only problem with doing something like this is that every one of your queries against the partitioned table is going to have to know the algorithm in order to produce a predicate that can be matched for partition elimination. If the engine would do this hashing work for us it could encapsulate all of that logic... I'm not sure why this capability was removed from the list for both 2005 and 2008 -- at least on the surface, it certainly doesn't seem difficult to implement!