Implementing a Hash Partition on 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

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.