Professional Web Applications Themes

What is a hash table ? - Microsoft SQL / MS SQL Server

The BOL mention that a hash join creates a "hash table" to resolve the join. But what is a hash table ? it is just like a temporary table ? Additionaly, could you give any recommendation about links, books or articles that widely explain the different join algorithms ? Thanks in advance, P.S. I sure I posted this, but I can't find my post. If someone sees it in the wrong group, sorry about that....

  1. #1

    Default What is a hash table ?

    The BOL mention that a hash join creates a "hash table" to resolve the join.
    But what is a hash table ? it is just like a temporary table ?

    Additionaly, could you give any recommendation about links, books or
    articles that widely explain the different join algorithms ?


    Thanks in advance, P.S. I sure I posted this, but I can't find my post.
    If someone sees it in the wrong group, sorry about that.


    Tim Guest

  2. #2

    Default Re: What is a hash table ?

    > The BOL mention that a hash join creates a "hash table" to resolve the
    join. 

    Yes, it is a kind of a temp table.
     

    Kalen Delaney: Inside MS SQL Server 2000

    --
    Dejan Sarka, SQL Server MVP
    FAQ from Neil & others at: http://www.sqlserverfaq.com
    Please reply only to the newsgroups.
    PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org


    Dejan Guest

  3. #3

    Default Re: What is a hash table ?

    Tim,
     [/ref][/ref]
    Yeah..You posted this in .clients newsgroup and Erland gave a brilliant
    reply too.In case you cant find it, here is his post

    <begin quote>
    Tim Conner (com) writes: 

    No, a hash table in this case has nothing to do with a regular
    database table.

    Hashing is a common technique to store data when you are not using a
    relational database, for instance when you store data in main memory.

    Say that you have 1000 values that you need to store, and the keys for
    the data are numeric. However, they are spread over a range from 200
    to 2 millions, so you cannot use an integer-based array, because it
    would take up much space for the task. There are several mechanisms
    to handle this, one is to use a hash table.

    You have a hash function that translates the key to a value in some
    range, say, 1 - 1201. (Usually the number of entries the hash table is a
    prime number, I believe.) It is perfectly possible that the two values
    hashes to the same key values, so you have a linked list at each entry.
    The hash table works well as long as there are not too many collisions.

    In this example I had integer keys, but you can to the same with strings
    or any other data.

    So, SQL Server first scanes the data which is to be search and hashes
    all values in the search column(s). Then it scans the other table, and
    uses the hash table to look up data in the other table. In this way,
    SQL Server only needs to scan the other table more than once.

    SQL Server usually takes to hash joins when there are no suitable
    indexes, so seeing a hash join in a query plan may be a warning sign.
     

    The standard recommendation is of course Kalen Delaney's "Inside
    SQL Server 2000". She also written about indexes in her columns in
    SQL Server Magazine. (www.sqlmag.com).

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

    <end quote>


    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Tim Conner" <com> wrote in message
    news:phx.gbl... 
    join. 


    Dinesh.T.K Guest

Similar Threads

  1. Question translate xml file into hash table using perl
    By veerubiji in forum Perl / CGI
    Replies: 0
    Last Post: September 30th, 12:00 PM
  2. Passing Hash table to function?
    By Tushar Gokhale in forum PERL Beginners
    Replies: 3
    Last Post: January 20th, 02:17 PM
  3. Distributing an array of hash refs across a table?
    By Zaphod in forum PERL Modules
    Replies: 4
    Last Post: December 24th, 12:23 PM
  4. newbie question. how to assign an array to a hash table?
    By Guest1 in forum PERL Miscellaneous
    Replies: 4
    Last Post: September 3rd, 03:58 PM
  5. [PHP-DEV] hash table question
    By Ard Biesheuvel in forum PHP Development
    Replies: 0
    Last Post: August 13th, 03:56 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139