• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1352
  • Last Modified:

Sql-server equivalent for ORACLE's hash cluster

I'm making this convertion from oracle to sql-server.
Some of my tables are loaded into a hash cluster for performance reasons. The idea
is to minimize I/O not having an index to maintain.

The question is if sql server 2000 has anything like this?

  • 4
  • 4
1 Solution
Can you post more about a ORA hash cluster.

In MSSQL2K you can use distributed partitioned views to increase performance and to split an index.
hwendtAuthor Commented:
I'm not an expert in hash clusters, the link below explains:

Storing a table in a hash cluster is an optional way to improve the performance of data retrieval. A hash cluster provides an alternative to a nonclustered table with an index or an index cluster. With an indexed table or index cluster, Oracle locates the rows in a table using key values that Oracle stores in a separate index. To use hashing, you create a hash cluster and load tables into it. Oracle physically stores the rows of a table in a hash cluster and retrieves them according to the results of a hash function.


This seems to be built into MSSQLSERVER2K also.
See BOL Topics "hash indices","CHECKSUM"

From BOL

Returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is intended for use in building hash indices.

-- Create a checksum index.
USE Northwind
ADD cs_Pname AS checksum(ProductName)
CREATE INDEX Pname_index ON Products (cs_Pname)

The checksum index can be used as a hash index, particularly to improve indexing speed when the column to be indexed is a long character column. The checksum index can be used for equality searches.

/*Use the index in a SELECT query. Add a second search
condition to catch stray cases where checksums match,
but the values are not identical.*/
FROM Products
WHERE checksum(N'Vegie-spread') = cs_Pname
AND ProductName = N'Vegie-spread'


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

hwendtAuthor Commented:
As I understand the checksum index, a new column must be added for the table and an index must be applied to this column.

In contrast the hash cluster can be used without affecting the original table.

With the checksum I still have to maintain an index, which i can do without using the hash cluster, ie I can speed up insertions.
Yes, but ORA does the same thing, only transparently.

In MSSQL, you must use 2 columns (original and calculated).
When you hash 8 bytes to 4 bytes, the information of 4 bytes is missing. Even if you hash 4 bytes, you MUST use also a original column (CHECKSUM is an absolutely asymetric function).
hwendtAuthor Commented:
My current setup i have hash cluster with  
HASH IS agent_id HASHKEYS 1500 ;
The table is put in the cluster has an ID which does not exceed 1500.

The Managing Hash Clusters documentation states:

To find or store a row in an indexed table or cluster, a minimum of two (there are usually more) I/Os must be performed:

- One or more I/Os to find or store the key value in the index

- Another I/O to read or write the row in the table or cluster

In contrast, Oracle uses a hash function to locate a row in a hash cluster; no I/O is required. As a result, a minimum of one I/O operation is necessary to read or write a row in a hash cluster.

I dont see that the checksum has the same performance profile.

You know ORA better, but I thought you are looking for a MSSQL alternative.
hwendtAuthor Commented:
I just hopped for a better alternative, but you might be right that checksum is the one comming closest. You get the points anyway, as the only one answering.

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now