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.
