THE SQL Server Blog Spot on the Web

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

Kevin Kline

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.

Published Monday, April 21, 2008 4:45 PM by KKline
Filed under: ,

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

 

Adam Machanic said:

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!

April 21, 2008 6:22 PM
 

KKline said:

Ah, I didn't think of that!

And you're right, it should be easy to do.  Both Oracle and DB2 have hash partitions in the box.

April 24, 2008 9:59 AM
 

ZZ said:

If a table has one & only one Column.

In which normal form we can categorize that table ?

April 15, 2011 11:49 PM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement