Concatenate strings in group column

I have following problem:
I want to display a concatenated string in a group row of a report.
The grouping works fine, i can "sum" numerical values etc. as
expected, but i could not find a way to concatenate strings (from the
rows that make up the group). "Join(...)" does not work (gives me an
error message).
I can use "First(...) & Last(...)" to concatenate the string columns
from the first and last row of the group, but couldn't find a way to
concatenate the string columns from all rows...
I am using SQL Server 2005 / Visual Studio 2005.I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
Server 2005.
In reports, I use Replace function to change my separator character.
--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"PR" <pr_gg@.egal.de> wrote in message
news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
>
>|||On Jan 17, 8:21 am, "Jean-Pierre Riehl" <jean-pierre.ri...@.bewise.fr>
wrote:
> I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
> Server 2005.
> In reports, I use Replace function to change my separator character.
> --
> Jean-Pierre Riehlhttp://blog.djeepy1.nethttp://www.bewise.fr
> "PR" <pr...@.egal.de> wrote in message
> news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>
thanks for the idea. I do use SQL Server 2005, however, since this is
a "group" column in a report, i can't use SQL to concatenate the
strings as i deal with a grouped subset of the sql query result...|||On Jan 16, 9:31=A0am, PR <pr...@.egal.de> wrote:
> I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" =A0to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
I'm having a hard time visualizing what you want to display. Do you
want your concantenation in each detail row or just in a group header?
Can you mock up an example?|||On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> I'm having a hard time visualizing what you want to display. Do you
> want your concantenation in each detail row or just in a group header?
> Can you mock up an example?
I want the concatenation in each detail row. I have some group "sum"s
that work fine, and want to do the same for a string. Basically it's
just a normal grouping of columns with group sums etc.. just that i
need to concatenate a string besides building the group sums.|||On Jan 22, 4:23=A0am, PR <pr...@.egal.de> wrote:
> On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> > I'm having a hard time visualizing what you want to display. =A0Do you
> > want your concantenation in each detail row or just in a group header?
> > Can you mock up an example?
> I want the concatenation in each detail row. I have some group "sum"s
> that work fine, and want to do the same for a string. Basically it's
> just a normal grouping of columns with group sums etc.. just that i
> need to concatenate a string besides building the group sums.
Is this what you want?
=3D"String Expression " & SUM(Fields!Name.Value)
It would look something like:
String Expression 1234.56
If you're wanting to concantenate actual field values, say like if
you're grouping on a combination of fields then:
=3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
would give you something like City, State or Company, Division
HTH|||On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
> Is this what you want?
> ="String Expression " & SUM(Fields!Name.Value)
> It would look something like:
> String Expression 1234.56
> If you're wanting to concantenate actual field values, say like if
> you're grouping on a combination of fields then:
> =Fields!GroupField1.Value & ", " & Fields!GroupField2.Value
> would give you something like City, State or Company, Division
> HTH
Sorry, no, i don't want to concatenate strings with the sum value of
the group.
I have a grouping in the report, where a sum value (i.e. =Sum(Fields!
Name.NumValue)) is written to one textfield of the row.
What i want to do is concatenate a string from the groups result set -
pretty much like the "Sum" sums up the numerical values from the
result set over the rows. If "Join" would work it would look like
=Join(Fields!Name.StringValue) for the other text field.. but
unfortunately that does not work.
Any ideas ?|||On Jan 23, 3:50=A0am, PR <pr...@.egal.de> wrote:
> On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
>
> > Is this what you want?
> > =3D"String Expression " & SUM(Fields!Name.Value)
> > It would look something like:
> > String Expression 1234.56
> > If you're wanting to concantenate actual field values, say like if
> > you're grouping on a combination of fields then:
> > =3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
> > would give you something like City, State or Company, Division
> > HTH
> Sorry, no, i don't want to concatenate strings with the sum value of
> the group.
> I have a grouping in the report, where a sum value (i.e. =3DSum(Fields!
> Name.NumValue)) is written to one textfield of the row.
> What i want to do is concatenate a string from the groups result set -
> pretty much like the "Sum" sums up the numerical values from the
> result set over the rows. If "Join" would work it would look like
> =3DJoin(Fields!Name.StringValue) for the other text field.. but
> unfortunately that does not work.
> Any ideas ?
I think if you can get your Fields!Name.StringValue values into an
array, you could then use Join() to get what you want. Unfortunately,
I'm not enough of a VB or .NET guy to guide you through that.
Hopefully someone else can jump at this point. Sorry I can't get you
further.|||On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> I think if you can get your Fields!Name.StringValue values into an
> array, you could then use Join() to get what you want. Unfortunately,
> I'm not enough of a VB or .NET guy to guide you through that.
> Hopefully someone else can jump at this point. Sorry I can't get you
> further.
Thanks for your effort. Yes, Join() requires an array.. and i have no
idea how to convert the string values into an array to do that (and
could not find anything about it in the documentation).|||Hello, PR.
Did you find any solution to your problem? I have the same issue and I can't
find any solution for this...
Thank you.
--
Alexandra
"PR" wrote:
> On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> >
> > I think if you can get your Fields!Name.StringValue values into an
> > array, you could then use Join() to get what you want. Unfortunately,
> > I'm not enough of a VB or .NET guy to guide you through that.
> > Hopefully someone else can jump at this point. Sorry I can't get you
> > further.
> Thanks for your effort. Yes, Join() requires an array.. and i have no
> idea how to convert the string values into an array to do that (and
> could not find anything about it in the documentation).
>|||On Feb 22, 12:13 pm, Alexandra Ribeiro
<AlexandraRibe...@.discussions.microsoft.com> wrote:
> Hello, PR.
> Did you find any solution to your problem? I have the same issue and I can't
> find any solution for this...
> Thank you.
> --
> Alexandra
>
Hi Alexandra,
no, i haven't found a solution for this. Maybe using a sub-report is
an option, but i haven't tried this yet.sqlsql

Concatenate list of values under a group

I have the following dataset:

State ZIP Homes Schools

WA 98007 2000 4

WA 98052 3000 5

WA 98079 2000 3

Now if I have set the group by expression on State but as display if I want to show it as “[98007, 98052, 98079]” how can I accomplish this.

My report needs to show:

State Homes Schools

[98007, 98052, 98079] 7000 12

Any help will be greatly appreciated

SPJ11, Why dont you do this in SQL itselves? You can also do this in SSRS. At the group level use,

Sum(Fields!Homes.value), Sum(Fields!Schools.value) -- these will retun the counts correctly.

I am not sure as how to concatnate the zip codes. I thought this should work

Join(Fields!Zip.Value) -- but this does not seem to work, I will see if I can come with something else...

|||

Never mind. I could make it work. Thanks to everyone who spent time on this thread

sqlsql

Concatenate Columm Values from multiple Rows into a single col

Yes, the order is not guaranteed.
ML
http://milambda.blogspot.com/ML (ML@.discussions.microsoft.com) writes:
> Yes, the order is not guaranteed.
Not even that. You are not even guaranteed to get all rows. For 1, 2, 3, 4
you could get '1,2,3,4' or you could get only '4'.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That would make the function completely useless - could you give an example,
please? I've tested it with a few typical set-ups and have always found it t
o
return expected results.
ML
http://milambda.blogspot.com/|||ML (ML@.discussions.microsoft.com) writes:
> That would make the function completely useless - could you give an
> example, please? I've tested it with a few typical set-ups and have
> always found it to return expected results.
Check out http://support.microsoft.com/default.aspx?scid=287515, and pay
particular attention to the first sentence under CAUSE.
Nevermind that the article then bend over backwards, to specify things that
may work. For me the conclusion is clear: don't rely on this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I agree using functions in the ORDER BY clause in this case is a disaster
waiting to happen, but since my function does not use them at all, were you
able to reproduce the problem anyway?
If you're too busy to play with this, I absolutely understand. I'm just
trying to learn new things every day. I promise I'll stop a few days after
I'm dead. ;)
ML
http://milambda.blogspot.com/|||ML (ML@.discussions.microsoft.com) writes:
> I agree using functions in the ORDER BY clause in this case is a
> disaster waiting to happen, but since my function does not use them at
> all, were you able to reproduce the problem anyway?
My point is not that I can reproduce it here and now. My point is that
what works today, could break tomorrow.
For instance, there are people out there who have defined views in
SQL 2000 which goes:
SELECT TOP 100 PERCENT
..
ORDER BY
and they are happy because when they say:
SELECT * FROM view1
the see the data in order.
Then they move to SQL 2005 and get hit, because the optimizer is now
less likely to return the data in order. The truth was all the time
that without an ORDER BY, the order of the data is undefined.
See also
http://lab.msdn.microsoft.com/produ...b9-3dd863ae6b1c
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This bug report clears up the matter greatly. Thank you very much. I intend
to include this example in my blog as a warning ASAP.
I've searched the web for this issue, but found no usable references. Thanks
again.
ML
http://milambda.blogspot.com/

Concatenate All values to one select

I'm trying to get all the values in one field
i.e from Northwind
Select ShipName from orders
where ShippedDate = Cast(GetDate() as Varchar(11)
I need the output to be like as one value with char(13) + char(10)
Vins et als Chevalier
Toms Spezialitten
Hanari Carnes
Victuailles en stock
Suprmes dlices
Hanari Carnes
Chop-suey Chinese
Richter Supermarkt
Wellington Importadora
HILARION-Abastos
Ernst Handel
ect ...
Thanks
Stephen K. MiyasatoSee if this helps.
http://milambda.blogspot.com/2005/0...s-as-array.html
AMB
"Stephen K. Miyasato" wrote:

> I'm trying to get all the values in one field
>
> i.e from Northwind
> Select ShipName from orders
> where ShippedDate = Cast(GetDate() as Varchar(11)
> I need the output to be like as one value with char(13) + char(10)
> Vins et als Chevalier
> Toms Spezialit?ten
> Hanari Carnes
> Victuailles en stock
> Suprêmes délices
> Hanari Carnes
> Chop-suey Chinese
> Richter Supermarkt
> Wellington Importadora
> HILARION-Abastos
> Ernst Handel
> ect ...
> Thanks
> Stephen K. Miyasato
>
>|||You can do something like this:
declare @.res nvarchar(4000)
set @.res = ''
select @.res = @.res + ShipName + char(13) + char(10) from Orders where Shippe
dDate is null
print @.res
Stephen K. Miyasato wrote:
> I'm trying to get all the values in one field
> i.e from Northwind
> Select ShipName from orders
> where ShippedDate = Cast(GetDate() as Varchar(11)
> I need the output to be like as one value with char(13) + char(10)
> Vins et als Chevalier
> Toms Spezialitten
> Hanari Carnes
> Victuailles en stock
> Suprmes dlices
> Hanari Carnes
> Chop-suey Chinese
> Richter Supermarkt
> Wellington Importadora
> HILARION-Abastos
> Ernst Handel
> ect ...
> Thanks
> Stephen K. Miyasato
>|||Stephen K. Miyasato (miyasat@.flex.com) writes:
> I'm trying to get all the values in one field
> i.e from Northwind
> Select ShipName from orders
> where ShippedDate = Cast(GetDate() as Varchar(11)
> I need the output to be like as one value with char(13) + char(10)
> Vins et als Chevalier
> Toms Spezialitten
> Hanari Carnes
> Victuailles en stock
> Suprmes dlices
> Hanari Carnes
> Chop-suey Chinese
> Richter Supermarkt
> Wellington Importadora
> HILARION-Abastos
> Ernst Handel
> ect ...
Which version of SQL Server?
If you are on SQL 2000, you need to run a cursor. (The solutions posted
in the thread relies on undefined behaviour, and could give undesired
results.)
On SQL 2005 you can do as in this example:
select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I tried this on a field with datatype Ntext but could not get it to work.
I also tried cast(memo, VarChar(8000)) which failed to give the right
result.
Any ideas on how to convert ntext to make the solution below to work?
Thanks
Stephen K. Miyasato
"Sericinus hunter" <serhunt@.flash.net> wrote in message
news:e%Q0g.10712$%m4.3143@.newssvr33.news.prodigy.com...
> You can do something like this:
> declare @.res nvarchar(4000)
> set @.res = ''
> select @.res = @.res + ShipName + char(13) + char(10) from Orders where
> ShippedDate is null
> print @.res
> Stephen K. Miyasato wrote:|||Stephen K. Miyasato (miyasat@.flex.com) writes:
> I tried this on a field with datatype Ntext but could not get it to work.
> I also tried cast(memo, VarChar(8000)) which failed to give the right
> result.
> Any ideas on how to convert ntext to make the solution below to work?
So the really column you have is next? And the values are typically > 4000
characters?
This is not going be fun at all. I think my two prime suggestions
would be:
1) Do it client-side
2) Upgrade to SQL 2005 where you can use the new nvarchar(MAX) data type,
which does not have all the restrictions of the ntext data type.
The problem on SQL 2000 is that you cannot work with ntext variables,
so you would have to do this in a table column. And to make it even more
fun, you would have to use the UPDATETEXT command. You can read about it
in Books Online, but you will have to play around a bit to understand
how it works.
Why don't you do describe the actual business problem. Maybe there is a
much better solution?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The field are less then Varchar(4000) and I can use a cast function into a
#temp table
cast(memo as varChar(4000))))AS memo , DateEntered)
How does one iterate through the table looking for a var
DECLARE @.iFoundAt int
set @.iFoundAt = (Select CHARINDEX('Apr 18 2006' ,memo) from Flags where
PatNo = 3760
if @.iFoundAt > 0
BEGIN
-- iterate though table and add records where 'Apr 18 2006' only, found
END
Business problem. I have a list of Clinical reminders with each record
being a reminder.
Description memo DateEntered
Stool 4/18/2006 - Done 4/18/2006
Ha1c 4/16/2006 - 6.5 4/16//2006
I want to query the flags table and insert the text into a word processing
document on the client side.
Thanks
Stephen K. Miyasato
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A9D8AC3D2DEYazorman@.127.0.0.1...
> Stephen K. Miyasato (miyasat@.flex.com) writes:
> So the really column you have is next? And the values are typically > 4000
> characters?
> This is not going be fun at all. I think my two prime suggestions
> would be:
> 1) Do it client-side
> 2) Upgrade to SQL 2005 where you can use the new nvarchar(MAX) data type,
> which does not have all the restrictions of the ntext data type.
> The problem on SQL 2000 is that you cannot work with ntext variables,
> so you would have to do this in a table column. And to make it even more
> fun, you would have to use the UPDATETEXT command. You can read about it
> in Books Online, but you will have to play around a bit to understand
> how it works.
> Why don't you do describe the actual business problem. Maybe there is a
> much better solution?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks All,
I figured it out
Stephen K. Miyasato
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OGg6cc5YGHA.1180@.TK2MSFTNGP03.phx.gbl...
> The field are less then Varchar(4000) and I can use a cast function into a
> #temp table
> cast(memo as varChar(4000))))AS memo , DateEntered)
> How does one iterate through the table looking for a var
> DECLARE @.iFoundAt int
> set @.iFoundAt = (Select CHARINDEX('Apr 18 2006' ,memo) from Flags where
> PatNo = 3760
> if @.iFoundAt > 0
> BEGIN
> -- iterate though table and add records where 'Apr 18 2006' only, found
> END
> Business problem. I have a list of Clinical reminders with each record
> being a reminder.
> Description memo DateEntered
> Stool 4/18/2006 - Done 4/18/2006
> Ha1c 4/16/2006 - 6.5 4/16//2006
> I want to query the flags table and insert the text into a word processing
> document on the client side.
> Thanks
> Stephen K. Miyasato
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns97A9D8AC3D2DEYazorman@.127.0.0.1...
>sqlsql

concat selected values in column of a table with cr/lf

In a column of my table i will concat two selected values (Strings). I do
this with the &-Sign.
The Problem: Is it possible to make a carriage-return and line-feed in a
column ?
--
Message posted via http://www.sqlmonster.comDid you try "Text" & vbcrlf & "Test"?
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Holger Schulz via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:2d8df2df1adf408e87dfe4a194b2f8ef@.SQLMonster.com...
> In a column of my table i will concat two selected values (Strings). I do
> this with the &-Sign.
> The Problem: Is it possible to make a carriage-return and line-feed in a
> column ?
> --
> Message posted via http://www.sqlmonster.com|||Thanks, it works fine...
--
Message posted via http://www.sqlmonster.com

Concat key Query Question

I have 2 tables with the fields: FiscalYear, Account, Region, Program
I want to treat these values as if they are a concatenated key. I want to
compare
2 tables to see if the one table has any concatenated key in that table that
does not
exist in the other. I need to do this without modifiying the tables with ke
ys
extra fields etc. I want to do this with just Transact SQL and not
using other languages. Any sugestions?
Thanks - EdEd,
You do not need to concatenate columns to do this.
select *
from dbo.t1
where not exists (
select *
from dbo.t2
where
t2.FiscalYear = t1.FiscalYear
and te.Account = t1.Account
and t2.Region = t1.Region
and t2.Program = t1.Program
);
AMB
"Ed" wrote:

> I have 2 tables with the fields: FiscalYear, Account, Region, Program
> I want to treat these values as if they are a concatenated key. I want to
> compare
> 2 tables to see if the one table has any concatenated key in that table th
at
> does not
> exist in the other. I need to do this without modifiying the tables with
keys
> extra fields etc. I want to do this with just Transact SQL and not
> using other languages. Any sugestions?
> Thanks - Ed
>|||>> I have 2 tables with the fields [sic]: FiscalYear, Account, Region, Program <
<
Columns are not fields; you are going to screw up a lot things until
you learn that. Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential Integrity, data
types, etc. in your schema are. Sample data is also a good idea, along
with clear specifications. It is very hard to debug code when you do
not let us see it.
Then there is the question as to why you have two tables with the same
structure, in violation of some basic RDBMS rules? This is a pretty
good sign that you have serious atrtribute splitting problems and a
non-relational schema.
There is no such term in RDBMS, or in SQL. Did you mean a compound
key? You still think that data is physically contigous and stored as
text -- the COBOL model!
QL and not using other languages. Any sugestions? <<
The *right* answer is to combine these vague tables into a single table
with a column for the values of the attribute you used to split them.
The kludge is below -- it also gives some ideas about the ISO-11179
rules for data element names that you did not follow:
SELECT S1.*, S2.*
FROM SplitNamelessTable AS S1
FULL OUTER JOIN
SplitNamelessTable AS S2
ON S1.fiscalyear = S2.fiscalyear
AND S1.foobar_account = S2.foobar_account
AND S1.region_id = S1.region_id
AND S1.program_name = S2.program_name
WHERE COALESCE (S1.fiscalyear, S1.foobar_account, S1.region_id,
S1.program_name) IS NULL
OR COALESCE (S2.fiscalyear, S2.foobar_account, S2.region_id,
S2.program_name) IS NULL;
Since you did not bother to tell us about NULLs and how they affect
matching rules, data types and all that other *vital information*, this
is only a guess.
There is also a version with EXISTS() predicates that has been posted
several times.|||Well if the 2 tables are A and B, then its ( A Union B ) - (A Intersect B)
SQL Server 2005's readable version of Joe's Solution.
(Select * from A
UNION
Select * from B)
EXCEPT
(select * from A
INTERSECT
select * from B)
Untested, but should work :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||>> Well if the 2 tables are A and B, then its ( A Union B ) - (A Intersect B
) .. SQL Server 2005's readable version of Joe's Solution. <<
Ands the SQL-92 version would be
SELECT * FROM A OUTER UNION SELECT * FROM B;
but n obody has implemented the OUTER UNION.sqlsql

concat all col2 values for each col1, and add sum(col3) (was "query help")

Hi,
Can anybody help me to create a single query? I have this problem.

CREATE TABLE t1 (
col1 VARCHAR(100)
, col2 VARCHAR(100)
, col3 INT)

INSERT INTO t1 VALUES('A001','Tom',30)
INSERT INTO t1 VALUES('A001','Rick',40)
INSERT INTO t1 VALUES('A001','Harry',10)

INSERT INTO t1 VALUES('A002','Peter',50)
INSERT INTO t1 VALUES('A002','Sam',50)

INSERT INTO t1 VALUES('A003','Fred',50)

I want a resultset like this ...
i.e col1 col2(all the values would be represented in a single row for each col1) and sum(col3)

(Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)

A001 Tom Rick Harry 80 --sum(col3)
A002 Peter Sam NULL 100
A003 Fred NULL NULL 50

Any help would be greatly appreciated !!(Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)
Based on this the below works. I think it is about as efficient as it can be though verbose for the sake of transparency :)

SET NOCOUNT ON
CREATE TABLE t1 (
col1 VARCHAR(100)
, col2 VARCHAR(100)
, col3 INT)

INSERT INTO t1 VALUES('A001','Tom',30)
INSERT INTO t1 VALUES('A001','Rick',40)
INSERT INTO t1 VALUES('A001','Harry',10)
INSERT INTO t1 VALUES('A002','Peter',50)
INSERT INTO t1 VALUES('A002','Sam',50)
INSERT INTO t1 VALUES('A003','Fred',50)

SELECT Col1,
Col2a,
Col2b,
Col2c,
SUM(Col3) AS TheTotal
FROM --Pivot data
(SELECT TOP 100 PERCENT
Col1,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
ORDER BY
B.Col2) AS Col2a,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
AND B.Col2 NOT IN (SELECT TOP 1 Col2
FROM dbo.t1 AS C
WHERE C.Col1 = A.Col1
ORDER BY
C.Col2)
ORDER BY
B.Col2) AS Col2b,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
AND B.Col2 NOT IN (SELECT TOP 2 Col2
FROM dbo.t1 AS C
WHERE C.Col1 = A.Col1
ORDER BY
C.Col2)) AS Col2c,
Col3
FROM dbo.t1 AS A
ORDER BY
Col1,
Col2) AS DerT
GROUP BY
Col1,
Col2a,
Col2b,
Col2c

DROP TABLE t1

SET NOCOUNT OFF
HTH|||Based on this the below works. I think it is about as efficient as it can be though verbose for the sake of transparency :)

Awesome, as usual...thanks a ton Pootie!!:rolleyes:|||nevermind, you have a better solution above.|||As a possible alternative, blindman's neat function here could be adapted, resulting in a much simpler query:

SELECT col1, dbo.Concat_ICD(col1) as TheNames, Sum(col3) as TheTotal
FROM t1
GROUP BY col1

http://www.dbforums.com/showthread.php?t=1605725

This would not produce the visible NULL in the result, but I was presuming that wasn't a requirement.|||The function is your best solutions, because it works for any number of records.

By the way, I wish I could take credit for that function, but it is actually one of the many things I have learned from participating in this forum over that past few years.|||Actually that was the solution I hoped to use - it allows n values to be concatenated. However I read the requirement as the return putting the names into three columns rather than one. If this isn't a requirement then defo go with Blindman's solution.|||I wanted it in three different columns.So I used Pootie's one.
Anyways,Thanks everybody for their valuable info.|||for comparison purposes, here is the equivalent query in mysql --select col1
, group_concat(col2)
, sum(col3)
from daTable
group
by col1:)|||I'm calling you on that one Rudy. Did you read the requirements carefully?|||...but here is a shorter method of coding it for SQL Server:SET NOCOUNT ON
CREATE TABLE #t1
(col1 VARCHAR(100),
col2 VARCHAR(100),
col3 INT)

INSERT INTO #t1 VALUES('A001','Tom',30)
INSERT INTO #t1 VALUES('A001','Rick',40)
INSERT INTO #t1 VALUES('A001','Harry',10)
INSERT INTO #t1 VALUES('A002','Peter',50)
INSERT INTO #t1 VALUES('A002','Sam',50)
INSERT INTO #t1 VALUES('A003','Fred',50)

select A.col1,
min(A.col2) as name1,
min(B.col2) as name2,
min(C.col2) as name3,
max(coalesce(A.col3, 0) + coalesce(B.col3, 0) + coalesce(C.col3, 0)) as col3total
from #t1 A
left outer join #t1 B on A.col1 = B.col1 and A.col2 < B.col2
left outer join #t1 C on B.col1 = C.col1 and B.col2 < C.col2
group by A.col1

drop table #t1|||I'm calling you on that one Rudy. Did you read the requirements carefully?
oh, SHEEEEEEEESH, okay :S
select col1
, group_concat(col2 separator ' ')
, sum(col3)
from t1
group
by col1|||for comparison purposes, here is the equivalent query in mysql --select col1
, group_concat(col2)
, sum(col3)
from daTable
group
by col1:)

...and that would be just great if it was a real ANSI compliant databa...

Oh, never mind|||pot? meet kettle

kettle? meet pot

:p|||That was great,thank you Blindman.And thank you all of you for your help.

Computing hash values

Hi,
In hash joins, how the hash value is computed? For example in this query:
SET SHOWPLAN_ALL ON
select c.customerid ,o.orderid, o.shipcountry from
customers c right outer join orders o
on c.customerid=o.customerid
and o.shipcountry='germany'
How the fields those appear in HASH) predicate help to create hash values?
I think my problem is that I don't know that what the hash value is.
Thanks,
Leila
Hi Leila
For your query tuning, it shouldn't matter what the actual hash values are.
If possible, you should try to build an index that will allow SQL Server to
perform a different join technique than hashing.
Microsoft does not document any details of the hash functions they use for
processing hash join operations. If you want to know more about hashing in
general, read "The Art of Computer Programming -- Volume 3: Sorting and
Searching" by Donald Knuth.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leila" <lelas@.hotpop.com> wrote in message
news:%23W3h0HPoEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hi,
> In hash joins, how the hash value is computed? For example in this query:
> SET SHOWPLAN_ALL ON
> select c.customerid ,o.orderid, o.shipcountry from
> customers c right outer join orders o
> on c.customerid=o.customerid
> and o.shipcountry='germany'
> How the fields those appear in HASH) predicate help to create hash
> values?
> I think my problem is that I don't know that what the hash value is.
> Thanks,
> Leila
>
|||Hi Kalen,
Thanks for your suggestion.
I'm a little confused about the difference between Hash Match and Nested
Loops. As far as I learned from BOL, in Hash Match, the hash values are
moved from the base table to a new place in memory(called hash table), then
an operation like nested loop happens between hash table and another table.
In nested loops, no value is moved from the base table, instead the loop
begins (with no hash table in between) directly with other table.
It seems the only difference is the existence of hash table in between, is
that true?
Thanks again,
Leila
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:euCtqpPoEHA.3460@.TK2MSFTNGP10.phx.gbl...
> Hi Leila
> For your query tuning, it shouldn't matter what the actual hash values
are.
> If possible, you should try to build an index that will allow SQL Server
to[vbcol=seagreen]
> perform a different join technique than hashing.
> Microsoft does not document any details of the hash functions they use for
> processing hash join operations. If you want to know more about hashing in
> general, read "The Art of Computer Programming -- Volume 3: Sorting and
> Searching" by Donald Knuth.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <lelas@.hotpop.com> wrote in message
> news:%23W3h0HPoEHA.324@.TK2MSFTNGP11.phx.gbl...
query:
>
|||>Leila" <lelas@.hotpop.com> wrote in message
news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...

> I'm a little confused about the difference between Hash Match and Nested
> Loops. As far as I learned from BOL, in Hash Match, the hash values are
> moved from the base table to a new place in memory(called hash table),
then
> an operation like nested loop happens between hash table and another
table.
> In nested loops, no value is moved from the base table, instead the loop
> begins (with no hash table in between) directly with other table.
> It seems the only difference is the existence of hash table in between, is
> that true?
In a nested loop, the inner loop is executed once for each outer loop. In a
hash match, the top ("build") input is created, then the bottom ("probe")
input is matched against it. This means that the bottom table is only
scanned once.
|||The 'only' difference is a very expensive one.
If you have an index, SQL Server can take a value from the outer table and
use the index to find matching rows in the inner table.
With a hash match, which is used because there IS no useful index, the data
in the inner table is organized into a hash table, so that SQL Server can
find matching rows using the hash table instead of an index.
Al though the inner table is scanned only once, the process of building the
hash table is resource intensive, and the hash table uses a lot of memory
for a big table.
You're better off building a good index to make the nested loops possible.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leila" <lelas@.hotpop.com> wrote in message
news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
> Hi Kalen,
> Thanks for your suggestion.
> I'm a little confused about the difference between Hash Match and Nested
> Loops. As far as I learned from BOL, in Hash Match, the hash values are
> moved from the base table to a new place in memory(called hash table),
> then
> an operation like nested loop happens between hash table and another
> table.
> In nested loops, no value is moved from the base table, instead the loop
> begins (with no hash table in between) directly with other table.
> It seems the only difference is the existence of hash table in between, is
> that true?
> Thanks again,
> Leila
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:euCtqpPoEHA.3460@.TK2MSFTNGP10.phx.gbl...
> are.
> to
> query:
>
>
|||Hi Mark,
I cannot understand that how the matching can be performed with one scan?
Maybe because yet I don't know about the real contents of hash table (hash
values).
Could you please help me.
Thanks,
Leila
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:saGdncQrlOIWgc_cRVn-jA@.sti.net...[vbcol=seagreen]
> news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
> then
> table.
is
> In a nested loop, the inner loop is executed once for each outer loop. In
a
> hash match, the top ("build") input is created, then the bottom ("probe")
> input is matched against it. This means that the bottom table is only
> scanned once.
>
|||"Leila" <lelas@.hotpop.com> wrote in message
news:%23ipgFuQoEHA.2140@.TK2MSFTNGP11.phx.gbl...

> I cannot understand that how the matching can be performed with one scan?
> Maybe because yet I don't know about the real contents of hash table (hash
> values).
Kalen is a much better person to explain this than I am. However, to
clarify, there are two scans - one scan to create the hash table in the
first place from the contents of the upper or outer table, and another scan
to match the lower or inner table against this hash table.
For example:
select id from A join B on A.something = B.somethingElse
If a hash match were used, this would look at all the A.something values and
create a hash table from them. For example, if A.something = "Mark's the
best", there might be a hash value created from it like 123. Another row
might contain "Kate's better", and that might hash to a different number,
like 342.
Having created the "build" hash table from A, B is then scanned, creating
hash values from the B.somethingElse column. Each of those values is used as
a "probe" into the original hash table to see if there is a match - i.e., if
A.something really does equal B.somethingElse.
To answer your question, it doesn't matter what hash value is generated for
"Mark's the best". Hashing is just a way of reducing a large number of
possible values to a smaller number.
I hope this didn't make it worse!
|||"Leila" <lelas@.hotpop.com> wrote in message
news:uvRR81QoEHA.1160@.tk2msftngp13.phx.gbl...

> I think I got it! You mean the bottom table is scanned once (for creating
> hash table) and then nested loop is needed for matching rows. Is that
true?
We're so close - and I'm honestly looking forward to what Kalen has to say.

The top table is scanned once to create the table, then the bottom table is
scanned once (not in a nested loop) and matched against the table.
|||Thanks Kalen!
You mentioned 'the data in the inner table is organized into a hash table'.
I read in BOL 'the smaller of the two inputs is the build input'.
Are they different?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ODudYpQoEHA.260@.TK2MSFTNGP10.phx.gbl...
> The 'only' difference is a very expensive one.
> If you have an index, SQL Server can take a value from the outer table and
> use the index to find matching rows in the inner table.
> With a hash match, which is used because there IS no useful index, the
data
> in the inner table is organized into a hash table, so that SQL Server can
> find matching rows using the hash table instead of an index.
> Al though the inner table is scanned only once, the process of building
the[vbcol=seagreen]
> hash table is resource intensive, and the hash table uses a lot of memory
> for a big table.
> You're better off building a good index to make the nested loops possible.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <lelas@.hotpop.com> wrote in message
> news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
is[vbcol=seagreen]
Server
>
|||It really clarified the issue. Thank you very much indeed!
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:yvCdnQJbXZiYtM_cRVn-jA@.sti.net...[vbcol=seagreen]
> "Leila" <lelas@.hotpop.com> wrote in message
> news:%23ipgFuQoEHA.2140@.TK2MSFTNGP11.phx.gbl...
scan?[vbcol=seagreen]
(hash
> Kalen is a much better person to explain this than I am. However, to
> clarify, there are two scans - one scan to create the hash table in the
> first place from the contents of the upper or outer table, and another
scan
> to match the lower or inner table against this hash table.
> For example:
> select id from A join B on A.something = B.somethingElse
> If a hash match were used, this would look at all the A.something values
and
> create a hash table from them. For example, if A.something = "Mark's the
> best", there might be a hash value created from it like 123. Another row
> might contain "Kate's better", and that might hash to a different number,
> like 342.
> Having created the "build" hash table from A, B is then scanned, creating
> hash values from the B.somethingElse column. Each of those values is used
as
> a "probe" into the original hash table to see if there is a match - i.e.,
if
> A.something really does equal B.somethingElse.
> To answer your question, it doesn't matter what hash value is generated
for
> "Mark's the best". Hashing is just a way of reducing a large number of
> possible values to a smaller number.
> I hope this didn't make it worse!
>

Computing hash values

Hi,
In hash joins, how the hash value is computed? For example in this query:
SET SHOWPLAN_ALL ON
select c.customerid ,o.orderid, o.shipcountry from
customers c right outer join orders o
on c.customerid=o.customerid
and o.shipcountry='germany'
How the fields those appear in HASH:() predicate help to create hash values?
I think my problem is that I don't know that what the hash value is.
Thanks,
LeilaHi Leila
For your query tuning, it shouldn't matter what the actual hash values are.
If possible, you should try to build an index that will allow SQL Server to
perform a different join technique than hashing.
Microsoft does not document any details of the hash functions they use for
processing hash join operations. If you want to know more about hashing in
general, read "The Art of Computer Programming -- Volume 3: Sorting and
Searching" by Donald Knuth.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leila" <lelas@.hotpop.com> wrote in message
news:%23W3h0HPoEHA.324@.TK2MSFTNGP11.phx.gbl...
> Hi,
> In hash joins, how the hash value is computed? For example in this query:
> SET SHOWPLAN_ALL ON
> select c.customerid ,o.orderid, o.shipcountry from
> customers c right outer join orders o
> on c.customerid=o.customerid
> and o.shipcountry='germany'
> How the fields those appear in HASH:() predicate help to create hash
> values?
> I think my problem is that I don't know that what the hash value is.
> Thanks,
> Leila
>|||Hi Kalen,
Thanks for your suggestion.
I'm a little confused about the difference between Hash Match and Nested
Loops. As far as I learned from BOL, in Hash Match, the hash values are
moved from the base table to a new place in memory(called hash table), then
an operation like nested loop happens between hash table and another table.
In nested loops, no value is moved from the base table, instead the loop
begins (with no hash table in between) directly with other table.
It seems the only difference is the existence of hash table in between, is
that true?
Thanks again,
Leila
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:euCtqpPoEHA.3460@.TK2MSFTNGP10.phx.gbl...
> Hi Leila
> For your query tuning, it shouldn't matter what the actual hash values
are.
> If possible, you should try to build an index that will allow SQL Server
to
> perform a different join technique than hashing.
> Microsoft does not document any details of the hash functions they use for
> processing hash join operations. If you want to know more about hashing in
> general, read "The Art of Computer Programming -- Volume 3: Sorting and
> Searching" by Donald Knuth.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <lelas@.hotpop.com> wrote in message
> news:%23W3h0HPoEHA.324@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > In hash joins, how the hash value is computed? For example in this
query:
> >
> > SET SHOWPLAN_ALL ON
> > select c.customerid ,o.orderid, o.shipcountry from
> > customers c right outer join orders o
> > on c.customerid=o.customerid
> > and o.shipcountry='germany'
> >
> > How the fields those appear in HASH:() predicate help to create hash
> > values?
> > I think my problem is that I don't know that what the hash value is.
> > Thanks,
> > Leila
> >
> >
>|||>Leila" <lelas@.hotpop.com> wrote in message
news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
> I'm a little confused about the difference between Hash Match and Nested
> Loops. As far as I learned from BOL, in Hash Match, the hash values are
> moved from the base table to a new place in memory(called hash table),
then
> an operation like nested loop happens between hash table and another
table.
> In nested loops, no value is moved from the base table, instead the loop
> begins (with no hash table in between) directly with other table.
> It seems the only difference is the existence of hash table in between, is
> that true?
In a nested loop, the inner loop is executed once for each outer loop. In a
hash match, the top ("build") input is created, then the bottom ("probe")
input is matched against it. This means that the bottom table is only
scanned once.|||The 'only' difference is a very expensive one.
If you have an index, SQL Server can take a value from the outer table and
use the index to find matching rows in the inner table.
With a hash match, which is used because there IS no useful index, the data
in the inner table is organized into a hash table, so that SQL Server can
find matching rows using the hash table instead of an index.
Al though the inner table is scanned only once, the process of building the
hash table is resource intensive, and the hash table uses a lot of memory
for a big table.
You're better off building a good index to make the nested loops possible.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leila" <lelas@.hotpop.com> wrote in message
news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
> Hi Kalen,
> Thanks for your suggestion.
> I'm a little confused about the difference between Hash Match and Nested
> Loops. As far as I learned from BOL, in Hash Match, the hash values are
> moved from the base table to a new place in memory(called hash table),
> then
> an operation like nested loop happens between hash table and another
> table.
> In nested loops, no value is moved from the base table, instead the loop
> begins (with no hash table in between) directly with other table.
> It seems the only difference is the existence of hash table in between, is
> that true?
> Thanks again,
> Leila
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:euCtqpPoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>> Hi Leila
>> For your query tuning, it shouldn't matter what the actual hash values
> are.
>> If possible, you should try to build an index that will allow SQL Server
> to
>> perform a different join technique than hashing.
>> Microsoft does not document any details of the hash functions they use
>> for
>> processing hash join operations. If you want to know more about hashing
>> in
>> general, read "The Art of Computer Programming -- Volume 3: Sorting and
>> Searching" by Donald Knuth.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <lelas@.hotpop.com> wrote in message
>> news:%23W3h0HPoEHA.324@.TK2MSFTNGP11.phx.gbl...
>> > Hi,
>> > In hash joins, how the hash value is computed? For example in this
> query:
>> >
>> > SET SHOWPLAN_ALL ON
>> > select c.customerid ,o.orderid, o.shipcountry from
>> > customers c right outer join orders o
>> > on c.customerid=o.customerid
>> > and o.shipcountry='germany'
>> >
>> > How the fields those appear in HASH:() predicate help to create hash
>> > values?
>> > I think my problem is that I don't know that what the hash value is.
>> > Thanks,
>> > Leila
>> >
>> >
>>
>
>|||Hi Mark,
I cannot understand that how the matching can be performed with one scan?
Maybe because yet I don't know about the real contents of hash table (hash
values).
Could you please help me.
Thanks,
Leila
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:saGdncQrlOIWgc_cRVn-jA@.sti.net...
> >Leila" <lelas@.hotpop.com> wrote in message
> news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
> > I'm a little confused about the difference between Hash Match and Nested
> > Loops. As far as I learned from BOL, in Hash Match, the hash values are
> > moved from the base table to a new place in memory(called hash table),
> then
> > an operation like nested loop happens between hash table and another
> table.
> > In nested loops, no value is moved from the base table, instead the loop
> > begins (with no hash table in between) directly with other table.
> > It seems the only difference is the existence of hash table in between,
is
> > that true?
> In a nested loop, the inner loop is executed once for each outer loop. In
a
> hash match, the top ("build") input is created, then the bottom ("probe")
> input is matched against it. This means that the bottom table is only
> scanned once.
>|||Thanks Mark,
I think I got it! You mean the bottom table is scanned once (for creating
hash table) and then nested loop is needed for matching rows. Is that true?
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:saGdncQrlOIWgc_cRVn-jA@.sti.net...
> >Leila" <lelas@.hotpop.com> wrote in message
> news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
> > I'm a little confused about the difference between Hash Match and Nested
> > Loops. As far as I learned from BOL, in Hash Match, the hash values are
> > moved from the base table to a new place in memory(called hash table),
> then
> > an operation like nested loop happens between hash table and another
> table.
> > In nested loops, no value is moved from the base table, instead the loop
> > begins (with no hash table in between) directly with other table.
> > It seems the only difference is the existence of hash table in between,
is
> > that true?
> In a nested loop, the inner loop is executed once for each outer loop. In
a
> hash match, the top ("build") input is created, then the bottom ("probe")
> input is matched against it. This means that the bottom table is only
> scanned once.
>|||"Leila" <lelas@.hotpop.com> wrote in message
news:%23ipgFuQoEHA.2140@.TK2MSFTNGP11.phx.gbl...
> I cannot understand that how the matching can be performed with one scan?
> Maybe because yet I don't know about the real contents of hash table (hash
> values).
Kalen is a much better person to explain this than I am. However, to
clarify, there are two scans - one scan to create the hash table in the
first place from the contents of the upper or outer table, and another scan
to match the lower or inner table against this hash table.
For example:
select id from A join B on A.something = B.somethingElse
If a hash match were used, this would look at all the A.something values and
create a hash table from them. For example, if A.something = "Mark's the
best", there might be a hash value created from it like 123. Another row
might contain "Kate's better", and that might hash to a different number,
like 342.
Having created the "build" hash table from A, B is then scanned, creating
hash values from the B.somethingElse column. Each of those values is used as
a "probe" into the original hash table to see if there is a match - i.e., if
A.something really does equal B.somethingElse.
To answer your question, it doesn't matter what hash value is generated for
"Mark's the best". Hashing is just a way of reducing a large number of
possible values to a smaller number.
I hope this didn't make it worse!|||"Leila" <lelas@.hotpop.com> wrote in message
news:uvRR81QoEHA.1160@.tk2msftngp13.phx.gbl...
> I think I got it! You mean the bottom table is scanned once (for creating
> hash table) and then nested loop is needed for matching rows. Is that
true?
We're so close - and I'm honestly looking forward to what Kalen has to say.
:)
The top table is scanned once to create the table, then the bottom table is
scanned once (not in a nested loop) and matched against the table.|||Thanks Kalen!
You mentioned 'the data in the inner table is organized into a hash table'.
I read in BOL 'the smaller of the two inputs is the build input'.
Are they different?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ODudYpQoEHA.260@.TK2MSFTNGP10.phx.gbl...
> The 'only' difference is a very expensive one.
> If you have an index, SQL Server can take a value from the outer table and
> use the index to find matching rows in the inner table.
> With a hash match, which is used because there IS no useful index, the
data
> in the inner table is organized into a hash table, so that SQL Server can
> find matching rows using the hash table instead of an index.
> Al though the inner table is scanned only once, the process of building
the
> hash table is resource intensive, and the hash table uses a lot of memory
> for a big table.
> You're better off building a good index to make the nested loops possible.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <lelas@.hotpop.com> wrote in message
> news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
> > Hi Kalen,
> > Thanks for your suggestion.
> > I'm a little confused about the difference between Hash Match and Nested
> > Loops. As far as I learned from BOL, in Hash Match, the hash values are
> > moved from the base table to a new place in memory(called hash table),
> > then
> > an operation like nested loop happens between hash table and another
> > table.
> > In nested loops, no value is moved from the base table, instead the loop
> > begins (with no hash table in between) directly with other table.
> > It seems the only difference is the existence of hash table in between,
is
> > that true?
> > Thanks again,
> > Leila
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:euCtqpPoEHA.3460@.TK2MSFTNGP10.phx.gbl...
> >> Hi Leila
> >>
> >> For your query tuning, it shouldn't matter what the actual hash values
> > are.
> >> If possible, you should try to build an index that will allow SQL
Server
> > to
> >> perform a different join technique than hashing.
> >>
> >> Microsoft does not document any details of the hash functions they use
> >> for
> >> processing hash join operations. If you want to know more about hashing
> >> in
> >> general, read "The Art of Computer Programming -- Volume 3: Sorting and
> >> Searching" by Donald Knuth.
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Leila" <lelas@.hotpop.com> wrote in message
> >> news:%23W3h0HPoEHA.324@.TK2MSFTNGP11.phx.gbl...
> >> > Hi,
> >> > In hash joins, how the hash value is computed? For example in this
> > query:
> >> >
> >> > SET SHOWPLAN_ALL ON
> >> > select c.customerid ,o.orderid, o.shipcountry from
> >> > customers c right outer join orders o
> >> > on c.customerid=o.customerid
> >> > and o.shipcountry='germany'
> >> >
> >> > How the fields those appear in HASH:() predicate help to create hash
> >> > values?
> >> > I think my problem is that I don't know that what the hash value is.
> >> > Thanks,
> >> > Leila
> >> >
> >> >
> >>
> >>
> >
> >
> >
> >
>|||It really clarified the issue. Thank you very much indeed!
"Mark Wilden" <mark@.mwilden.com> wrote in message
news:yvCdnQJbXZiYtM_cRVn-jA@.sti.net...
> "Leila" <lelas@.hotpop.com> wrote in message
> news:%23ipgFuQoEHA.2140@.TK2MSFTNGP11.phx.gbl...
> > I cannot understand that how the matching can be performed with one
scan?
> > Maybe because yet I don't know about the real contents of hash table
(hash
> > values).
> Kalen is a much better person to explain this than I am. However, to
> clarify, there are two scans - one scan to create the hash table in the
> first place from the contents of the upper or outer table, and another
scan
> to match the lower or inner table against this hash table.
> For example:
> select id from A join B on A.something = B.somethingElse
> If a hash match were used, this would look at all the A.something values
and
> create a hash table from them. For example, if A.something = "Mark's the
> best", there might be a hash value created from it like 123. Another row
> might contain "Kate's better", and that might hash to a different number,
> like 342.
> Having created the "build" hash table from A, B is then scanned, creating
> hash values from the B.somethingElse column. Each of those values is used
as
> a "probe" into the original hash table to see if there is a match - i.e.,
if
> A.something really does equal B.somethingElse.
> To answer your question, it doesn't matter what hash value is generated
for
> "Mark's the best". Hashing is just a way of reducing a large number of
> possible values to a smaller number.
> I hope this didn't make it worse!
>|||The 'inner' table is whichever one is chosen by the SQL Server optimizer to
build the hash table. Typically this will be the smaller one, but not
always.
For BOL to say the smaller of the two is the build input is a bit of an
overgeneralization.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leila" <lelas@.hotpop.com> wrote in message
news:eejiM5QoEHA.3788@.TK2MSFTNGP10.phx.gbl...
> Thanks Kalen!
> You mentioned 'the data in the inner table is organized into a hash
> table'.
> I read in BOL 'the smaller of the two inputs is the build input'.
> Are they different?
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:ODudYpQoEHA.260@.TK2MSFTNGP10.phx.gbl...
>> The 'only' difference is a very expensive one.
>> If you have an index, SQL Server can take a value from the outer table
>> and
>> use the index to find matching rows in the inner table.
>> With a hash match, which is used because there IS no useful index, the
> data
>> in the inner table is organized into a hash table, so that SQL Server can
>> find matching rows using the hash table instead of an index.
>> Al though the inner table is scanned only once, the process of building
> the
>> hash table is resource intensive, and the hash table uses a lot of memory
>> for a big table.
>> You're better off building a good index to make the nested loops
>> possible.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <lelas@.hotpop.com> wrote in message
>> news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
>> > Hi Kalen,
>> > Thanks for your suggestion.
>> > I'm a little confused about the difference between Hash Match and
>> > Nested
>> > Loops. As far as I learned from BOL, in Hash Match, the hash values are
>> > moved from the base table to a new place in memory(called hash table),
>> > then
>> > an operation like nested loop happens between hash table and another
>> > table.
>> > In nested loops, no value is moved from the base table, instead the
>> > loop
>> > begins (with no hash table in between) directly with other table.
>> > It seems the only difference is the existence of hash table in between,
> is
>> > that true?
>> > Thanks again,
>> > Leila
>> >
>> >
>> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> > news:euCtqpPoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>> >> Hi Leila
>> >>
>> >> For your query tuning, it shouldn't matter what the actual hash values
>> > are.
>> >> If possible, you should try to build an index that will allow SQL
> Server
>> > to
>> >> perform a different join technique than hashing.
>> >>
>> >> Microsoft does not document any details of the hash functions they use
>> >> for
>> >> processing hash join operations. If you want to know more about
>> >> hashing
>> >> in
>> >> general, read "The Art of Computer Programming -- Volume 3: Sorting
>> >> and
>> >> Searching" by Donald Knuth.
>> >>
>> >> --
>> >> HTH
>> >> --
>> >> Kalen Delaney
>> >> SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Leila" <lelas@.hotpop.com> wrote in message
>> >> news:%23W3h0HPoEHA.324@.TK2MSFTNGP11.phx.gbl...
>> >> > Hi,
>> >> > In hash joins, how the hash value is computed? For example in this
>> > query:
>> >> >
>> >> > SET SHOWPLAN_ALL ON
>> >> > select c.customerid ,o.orderid, o.shipcountry from
>> >> > customers c right outer join orders o
>> >> > on c.customerid=o.customerid
>> >> > and o.shipcountry='germany'
>> >> >
>> >> > How the fields those appear in HASH:() predicate help to create hash
>> >> > values?
>> >> > I think my problem is that I don't know that what the hash value is.
>> >> > Thanks,
>> >> > Leila
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>> >
>> >
>>
>|||Kalen,
When the hash table is ready, will there be something like nested loop to
match rows? Because Mark described that the bottom table is
scanned once (not in a nested loop).
Leila
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u26#jDRoEHA.2900@.TK2MSFTNGP12.phx.gbl...
> The 'inner' table is whichever one is chosen by the SQL Server optimizer
to
> build the hash table. Typically this will be the smaller one, but not
> always.
> For BOL to say the smaller of the two is the build input is a bit of an
> overgeneralization.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <lelas@.hotpop.com> wrote in message
> news:eejiM5QoEHA.3788@.TK2MSFTNGP10.phx.gbl...
> > Thanks Kalen!
> > You mentioned 'the data in the inner table is organized into a hash
> > table'.
> > I read in BOL 'the smaller of the two inputs is the build input'.
> > Are they different?
> >
> >
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:ODudYpQoEHA.260@.TK2MSFTNGP10.phx.gbl...
> >> The 'only' difference is a very expensive one.
> >> If you have an index, SQL Server can take a value from the outer table
> >> and
> >> use the index to find matching rows in the inner table.
> >>
> >> With a hash match, which is used because there IS no useful index, the
> > data
> >> in the inner table is organized into a hash table, so that SQL Server
can
> >> find matching rows using the hash table instead of an index.
> >> Al though the inner table is scanned only once, the process of building
> > the
> >> hash table is resource intensive, and the hash table uses a lot of
memory
> >> for a big table.
> >>
> >> You're better off building a good index to make the nested loops
> >> possible.
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Leila" <lelas@.hotpop.com> wrote in message
> >> news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
> >> > Hi Kalen,
> >> > Thanks for your suggestion.
> >> > I'm a little confused about the difference between Hash Match and
> >> > Nested
> >> > Loops. As far as I learned from BOL, in Hash Match, the hash values
are
> >> > moved from the base table to a new place in memory(called hash
table),
> >> > then
> >> > an operation like nested loop happens between hash table and another
> >> > table.
> >> > In nested loops, no value is moved from the base table, instead the
> >> > loop
> >> > begins (with no hash table in between) directly with other table.
> >> > It seems the only difference is the existence of hash table in
between,
> > is
> >> > that true?
> >> > Thanks again,
> >> > Leila
> >> >
> >> >
> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> >> > news:euCtqpPoEHA.3460@.TK2MSFTNGP10.phx.gbl...
> >> >> Hi Leila
> >> >>
> >> >> For your query tuning, it shouldn't matter what the actual hash
values
> >> > are.
> >> >> If possible, you should try to build an index that will allow SQL
> > Server
> >> > to
> >> >> perform a different join technique than hashing.
> >> >>
> >> >> Microsoft does not document any details of the hash functions they
use
> >> >> for
> >> >> processing hash join operations. If you want to know more about
> >> >> hashing
> >> >> in
> >> >> general, read "The Art of Computer Programming -- Volume 3: Sorting
> >> >> and
> >> >> Searching" by Donald Knuth.
> >> >>
> >> >> --
> >> >> HTH
> >> >> --
> >> >> Kalen Delaney
> >> >> SQL Server MVP
> >> >> www.SolidQualityLearning.com
> >> >>
> >> >>
> >> >> "Leila" <lelas@.hotpop.com> wrote in message
> >> >> news:%23W3h0HPoEHA.324@.TK2MSFTNGP11.phx.gbl...
> >> >> > Hi,
> >> >> > In hash joins, how the hash value is computed? For example in this
> >> > query:
> >> >> >
> >> >> > SET SHOWPLAN_ALL ON
> >> >> > select c.customerid ,o.orderid, o.shipcountry from
> >> >> > customers c right outer join orders o
> >> >> > on c.customerid=o.customerid
> >> >> > and o.shipcountry='germany'
> >> >> >
> >> >> > How the fields those appear in HASH:() predicate help to create
hash
> >> >> > values?
> >> >> > I think my problem is that I don't know that what the hash value
is.
> >> >> > Thanks,
> >> >> > Leila
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||A nested loop is when the inner table is processed completely for each row
of the outer table.
For hash joins the inner table is read once to build the hash table, and
then not touched again. Then each row of the outer table leads to a single
access of the hash table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leila" <lelas@.hotpop.com> wrote in message
news:OfY1jORoEHA.3760@.TK2MSFTNGP12.phx.gbl...
> Kalen,
> When the hash table is ready, will there be something like nested loop to
> match rows? Because Mark described that the bottom table is
> scanned once (not in a nested loop).
> Leila
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u26#jDRoEHA.2900@.TK2MSFTNGP12.phx.gbl...
>> The 'inner' table is whichever one is chosen by the SQL Server optimizer
> to
>> build the hash table. Typically this will be the smaller one, but not
>> always.
>> For BOL to say the smaller of the two is the build input is a bit of an
>> overgeneralization.
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Leila" <lelas@.hotpop.com> wrote in message
>> news:eejiM5QoEHA.3788@.TK2MSFTNGP10.phx.gbl...
>> > Thanks Kalen!
>> > You mentioned 'the data in the inner table is organized into a hash
>> > table'.
>> > I read in BOL 'the smaller of the two inputs is the build input'.
>> > Are they different?
>> >
>> >
>> >
>> >
>> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> > news:ODudYpQoEHA.260@.TK2MSFTNGP10.phx.gbl...
>> >> The 'only' difference is a very expensive one.
>> >> If you have an index, SQL Server can take a value from the outer table
>> >> and
>> >> use the index to find matching rows in the inner table.
>> >>
>> >> With a hash match, which is used because there IS no useful index, the
>> > data
>> >> in the inner table is organized into a hash table, so that SQL Server
> can
>> >> find matching rows using the hash table instead of an index.
>> >> Al though the inner table is scanned only once, the process of
>> >> building
>> > the
>> >> hash table is resource intensive, and the hash table uses a lot of
> memory
>> >> for a big table.
>> >>
>> >> You're better off building a good index to make the nested loops
>> >> possible.
>> >>
>> >> --
>> >> HTH
>> >> --
>> >> Kalen Delaney
>> >> SQL Server MVP
>> >> www.SolidQualityLearning.com
>> >>
>> >>
>> >> "Leila" <lelas@.hotpop.com> wrote in message
>> >> news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
>> >> > Hi Kalen,
>> >> > Thanks for your suggestion.
>> >> > I'm a little confused about the difference between Hash Match and
>> >> > Nested
>> >> > Loops. As far as I learned from BOL, in Hash Match, the hash values
> are
>> >> > moved from the base table to a new place in memory(called hash
> table),
>> >> > then
>> >> > an operation like nested loop happens between hash table and another
>> >> > table.
>> >> > In nested loops, no value is moved from the base table, instead the
>> >> > loop
>> >> > begins (with no hash table in between) directly with other table.
>> >> > It seems the only difference is the existence of hash table in
> between,
>> > is
>> >> > that true?
>> >> > Thanks again,
>> >> > Leila
>> >> >
>> >> >
>> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> >> > news:euCtqpPoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>> >> >> Hi Leila
>> >> >>
>> >> >> For your query tuning, it shouldn't matter what the actual hash
> values
>> >> > are.
>> >> >> If possible, you should try to build an index that will allow SQL
>> > Server
>> >> > to
>> >> >> perform a different join technique than hashing.
>> >> >>
>> >> >> Microsoft does not document any details of the hash functions they
> use
>> >> >> for
>> >> >> processing hash join operations. If you want to know more about
>> >> >> hashing
>> >> >> in
>> >> >> general, read "The Art of Computer Programming -- Volume 3: Sorting
>> >> >> and
>> >> >> Searching" by Donald Knuth.
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >> --
>> >> >> Kalen Delaney
>> >> >> SQL Server MVP
>> >> >> www.SolidQualityLearning.com
>> >> >>
>> >> >>
>> >> >> "Leila" <lelas@.hotpop.com> wrote in message
>> >> >> news:%23W3h0HPoEHA.324@.TK2MSFTNGP11.phx.gbl...
>> >> >> > Hi,
>> >> >> > In hash joins, how the hash value is computed? For example in
>> >> >> > this
>> >> > query:
>> >> >> >
>> >> >> > SET SHOWPLAN_ALL ON
>> >> >> > select c.customerid ,o.orderid, o.shipcountry from
>> >> >> > customers c right outer join orders o
>> >> >> > on c.customerid=o.customerid
>> >> >> > and o.shipcountry='germany'
>> >> >> >
>> >> >> > How the fields those appear in HASH:() predicate help to create
> hash
>> >> >> > values?
>> >> >> > I think my problem is that I don't know that what the hash value
> is.
>> >> >> > Thanks,
>> >> >> > Leila
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Does the hash table have an strucnture like index? If it doesn't, I think
nested loop is inevitable for matching rows between hash table and the probe
table.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eQJ6HbRoEHA.2108@.TK2MSFTNGP10.phx.gbl...
> A nested loop is when the inner table is processed completely for each
row
> of the outer table.
> For hash joins the inner table is read once to build the hash table, and
> then not touched again. Then each row of the outer table leads to a single
> access of the hash table.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <lelas@.hotpop.com> wrote in message
> news:OfY1jORoEHA.3760@.TK2MSFTNGP12.phx.gbl...
> > Kalen,
> > When the hash table is ready, will there be something like nested loop
to
> > match rows? Because Mark described that the bottom table is
> > scanned once (not in a nested loop).
> > Leila
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:u26#jDRoEHA.2900@.TK2MSFTNGP12.phx.gbl...
> >> The 'inner' table is whichever one is chosen by the SQL Server
optimizer
> > to
> >> build the hash table. Typically this will be the smaller one, but not
> >> always.
> >> For BOL to say the smaller of the two is the build input is a bit of an
> >> overgeneralization.
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "Leila" <lelas@.hotpop.com> wrote in message
> >> news:eejiM5QoEHA.3788@.TK2MSFTNGP10.phx.gbl...
> >> > Thanks Kalen!
> >> > You mentioned 'the data in the inner table is organized into a hash
> >> > table'.
> >> > I read in BOL 'the smaller of the two inputs is the build input'.
> >> > Are they different?
> >> >
> >> >
> >> >
> >> >
> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> >> > news:ODudYpQoEHA.260@.TK2MSFTNGP10.phx.gbl...
> >> >> The 'only' difference is a very expensive one.
> >> >> If you have an index, SQL Server can take a value from the outer
table
> >> >> and
> >> >> use the index to find matching rows in the inner table.
> >> >>
> >> >> With a hash match, which is used because there IS no useful index,
the
> >> > data
> >> >> in the inner table is organized into a hash table, so that SQL
Server
> > can
> >> >> find matching rows using the hash table instead of an index.
> >> >> Al though the inner table is scanned only once, the process of
> >> >> building
> >> > the
> >> >> hash table is resource intensive, and the hash table uses a lot of
> > memory
> >> >> for a big table.
> >> >>
> >> >> You're better off building a good index to make the nested loops
> >> >> possible.
> >> >>
> >> >> --
> >> >> HTH
> >> >> --
> >> >> Kalen Delaney
> >> >> SQL Server MVP
> >> >> www.SolidQualityLearning.com
> >> >>
> >> >>
> >> >> "Leila" <lelas@.hotpop.com> wrote in message
> >> >> news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
> >> >> > Hi Kalen,
> >> >> > Thanks for your suggestion.
> >> >> > I'm a little confused about the difference between Hash Match and
> >> >> > Nested
> >> >> > Loops. As far as I learned from BOL, in Hash Match, the hash
values
> > are
> >> >> > moved from the base table to a new place in memory(called hash
> > table),
> >> >> > then
> >> >> > an operation like nested loop happens between hash table and
another
> >> >> > table.
> >> >> > In nested loops, no value is moved from the base table, instead
the
> >> >> > loop
> >> >> > begins (with no hash table in between) directly with other table.
> >> >> > It seems the only difference is the existence of hash table in
> > between,
> >> > is
> >> >> > that true?
> >> >> > Thanks again,
> >> >> > Leila
> >> >> >
> >> >> >
> >> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> >> >> > news:euCtqpPoEHA.3460@.TK2MSFTNGP10.phx.gbl...
> >> >> >> Hi Leila
> >> >> >>
> >> >> >> For your query tuning, it shouldn't matter what the actual hash
> > values
> >> >> > are.
> >> >> >> If possible, you should try to build an index that will allow SQL
> >> > Server
> >> >> > to
> >> >> >> perform a different join technique than hashing.
> >> >> >>
> >> >> >> Microsoft does not document any details of the hash functions
they
> > use
> >> >> >> for
> >> >> >> processing hash join operations. If you want to know more about
> >> >> >> hashing
> >> >> >> in
> >> >> >> general, read "The Art of Computer Programming -- Volume 3:
Sorting
> >> >> >> and
> >> >> >> Searching" by Donald Knuth.
> >> >> >>
> >> >> >> --
> >> >> >> HTH
> >> >> >> --
> >> >> >> Kalen Delaney
> >> >> >> SQL Server MVP
> >> >> >> www.SolidQualityLearning.com
> >> >> >>
> >> >> >>
> >> >> >> "Leila" <lelas@.hotpop.com> wrote in message
> >> >> >> news:%23W3h0HPoEHA.324@.TK2MSFTNGP11.phx.gbl...
> >> >> >> > Hi,
> >> >> >> > In hash joins, how the hash value is computed? For example in
> >> >> >> > this
> >> >> > query:
> >> >> >> >
> >> >> >> > SET SHOWPLAN_ALL ON
> >> >> >> > select c.customerid ,o.orderid, o.shipcountry from
> >> >> >> > customers c right outer join orders o
> >> >> >> > on c.customerid=o.customerid
> >> >> >> > and o.shipcountry='germany'
> >> >> >> >
> >> >> >> > How the fields those appear in HASH:() predicate help to create
> > hash
> >> >> >> > values?
> >> >> >> > I think my problem is that I don't know that what the hash
value
> > is.
> >> >> >> > Thanks,
> >> >> >> > Leila
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||For each row in the probe table, a hash value is calculated based on the join key. Then SQL Server
looks in the hash bucked from the build table to see if there is any match. The key (no pun
intended) here is that the build table is splitted up into a lot of buckets, and for the other
table, SQL server only have to look in a specific bucket to find if there's a match.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <lelas@.hotpop.com> wrote in message news:uRd8FNWoEHA.3488@.TK2MSFTNGP12.phx.gbl...
> Does the hash table have an strucnture like index? If it doesn't, I think
> nested loop is inevitable for matching rows between hash table and the probe
> table.
>
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eQJ6HbRoEHA.2108@.TK2MSFTNGP10.phx.gbl...
> > A nested loop is when the inner table is processed completely for each
> row
> > of the outer table.
> >
> > For hash joins the inner table is read once to build the hash table, and
> > then not touched again. Then each row of the outer table leads to a single
> > access of the hash table.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Leila" <lelas@.hotpop.com> wrote in message
> > news:OfY1jORoEHA.3760@.TK2MSFTNGP12.phx.gbl...
> > > Kalen,
> > > When the hash table is ready, will there be something like nested loop
> to
> > > match rows? Because Mark described that the bottom table is
> > > scanned once (not in a nested loop).
> > > Leila
> > >
> > >
> > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > > news:u26#jDRoEHA.2900@.TK2MSFTNGP12.phx.gbl...
> > >> The 'inner' table is whichever one is chosen by the SQL Server
> optimizer
> > > to
> > >> build the hash table. Typically this will be the smaller one, but not
> > >> always.
> > >> For BOL to say the smaller of the two is the build input is a bit of an
> > >> overgeneralization.
> > >>
> > >> --
> > >> HTH
> > >> --
> > >> Kalen Delaney
> > >> SQL Server MVP
> > >> www.SolidQualityLearning.com
> > >>
> > >>
> > >> "Leila" <lelas@.hotpop.com> wrote in message
> > >> news:eejiM5QoEHA.3788@.TK2MSFTNGP10.phx.gbl...
> > >> > Thanks Kalen!
> > >> > You mentioned 'the data in the inner table is organized into a hash
> > >> > table'.
> > >> > I read in BOL 'the smaller of the two inputs is the build input'.
> > >> > Are they different?
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > >> > news:ODudYpQoEHA.260@.TK2MSFTNGP10.phx.gbl...
> > >> >> The 'only' difference is a very expensive one.
> > >> >> If you have an index, SQL Server can take a value from the outer
> table
> > >> >> and
> > >> >> use the index to find matching rows in the inner table.
> > >> >>
> > >> >> With a hash match, which is used because there IS no useful index,
> the
> > >> > data
> > >> >> in the inner table is organized into a hash table, so that SQL
> Server
> > > can
> > >> >> find matching rows using the hash table instead of an index.
> > >> >> Al though the inner table is scanned only once, the process of
> > >> >> building
> > >> > the
> > >> >> hash table is resource intensive, and the hash table uses a lot of
> > > memory
> > >> >> for a big table.
> > >> >>
> > >> >> You're better off building a good index to make the nested loops
> > >> >> possible.
> > >> >>
> > >> >> --
> > >> >> HTH
> > >> >> --
> > >> >> Kalen Delaney
> > >> >> SQL Server MVP
> > >> >> www.SolidQualityLearning.com
> > >> >>
> > >> >>
> > >> >> "Leila" <lelas@.hotpop.com> wrote in message
> > >> >> news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
> > >> >> > Hi Kalen,
> > >> >> > Thanks for your suggestion.
> > >> >> > I'm a little confused about the difference between Hash Match and
> > >> >> > Nested
> > >> >> > Loops. As far as I learned from BOL, in Hash Match, the hash
> values
> > > are
> > >> >> > moved from the base table to a new place in memory(called hash
> > > table),
> > >> >> > then
> > >> >> > an operation like nested loop happens between hash table and
> another
> > >> >> > table.
> > >> >> > In nested loops, no value is moved from the base table, instead
> the
> > >> >> > loop
> > >> >> > begins (with no hash table in between) directly with other table.
> > >> >> > It seems the only difference is the existence of hash table in
> > > between,
> > >> > is
> > >> >> > that true?
> > >> >> > Thanks again,
> > >> >> > Leila
> > >> >> >
> > >> >> >
> > >> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > >> >> > news:euCtqpPoEHA.3460@.TK2MSFTNGP10.phx.gbl...
> > >> >> >> Hi Leila
> > >> >> >>
> > >> >> >> For your query tuning, it shouldn't matter what the actual hash
> > > values
> > >> >> > are.
> > >> >> >> If possible, you should try to build an index that will allow SQL
> > >> > Server
> > >> >> > to
> > >> >> >> perform a different join technique than hashing.
> > >> >> >>
> > >> >> >> Microsoft does not document any details of the hash functions
> they
> > > use
> > >> >> >> for
> > >> >> >> processing hash join operations. If you want to know more about
> > >> >> >> hashing
> > >> >> >> in
> > >> >> >> general, read "The Art of Computer Programming -- Volume 3:
> Sorting
> > >> >> >> and
> > >> >> >> Searching" by Donald Knuth.
> > >> >> >>
> > >> >> >> --
> > >> >> >> HTH
> > >> >> >> --
> > >> >> >> Kalen Delaney
> > >> >> >> SQL Server MVP
> > >> >> >> www.SolidQualityLearning.com
> > >> >> >>
> > >> >> >>
> > >> >> >> "Leila" <lelas@.hotpop.com> wrote in message
> > >> >> >> news:%23W3h0HPoEHA.324@.TK2MSFTNGP11.phx.gbl...
> > >> >> >> > Hi,
> > >> >> >> > In hash joins, how the hash value is computed? For example in
> > >> >> >> > this
> > >> >> > query:
> > >> >> >> >
> > >> >> >> > SET SHOWPLAN_ALL ON
> > >> >> >> > select c.customerid ,o.orderid, o.shipcountry from
> > >> >> >> > customers c right outer join orders o
> > >> >> >> > on c.customerid=o.customerid
> > >> >> >> > and o.shipcountry='germany'
> > >> >> >> >
> > >> >> >> > How the fields those appear in HASH:() predicate help to create
> > > hash
> > >> >> >> > values?
> > >> >> >> > I think my problem is that I don't know that what the hash
> value
> > > is.
> > >> >> >> > Thanks,
> > >> >> >> > Leila
> > >> >> >> >
> > >> >> >> >
> > >> >> >>
> > >> >> >>
> > >> >> >
> > >> >> >
> > >> >> >
> > >> >> >
> > >> >>
> > >> >>
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>|||Thanks Tibor!
What I cannot understand is that what the meaning of "calculating hash value
based on join key" is.
Because join key is only the name of two fields plus an operator between
them, it doesn't have any value itself (to be calculated).
Leila
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eZi#TaWoEHA.1776@.TK2MSFTNGP14.phx.gbl...
> For each row in the probe table, a hash value is calculated based on the
join key. Then SQL Server
> looks in the hash bucked from the build table to see if there is any
match. The key (no pun
> intended) here is that the build table is splitted up into a lot of
buckets, and for the other
> table, SQL server only have to look in a specific bucket to find if
there's a match.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leila" <lelas@.hotpop.com> wrote in message
news:uRd8FNWoEHA.3488@.TK2MSFTNGP12.phx.gbl...
> > Does the hash table have an strucnture like index? If it doesn't, I
think
> > nested loop is inevitable for matching rows between hash table and the
probe
> > table.
> >
> >
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:eQJ6HbRoEHA.2108@.TK2MSFTNGP10.phx.gbl...
> > > A nested loop is when the inner table is processed completely for
each
> > row
> > > of the outer table.
> > >
> > > For hash joins the inner table is read once to build the hash table,
and
> > > then not touched again. Then each row of the outer table leads to a
single
> > > access of the hash table.
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Leila" <lelas@.hotpop.com> wrote in message
> > > news:OfY1jORoEHA.3760@.TK2MSFTNGP12.phx.gbl...
> > > > Kalen,
> > > > When the hash table is ready, will there be something like nested
loop
> > to
> > > > match rows? Because Mark described that the bottom table is
> > > > scanned once (not in a nested loop).
> > > > Leila
> > > >
> > > >
> > > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > > > news:u26#jDRoEHA.2900@.TK2MSFTNGP12.phx.gbl...
> > > >> The 'inner' table is whichever one is chosen by the SQL Server
> > optimizer
> > > > to
> > > >> build the hash table. Typically this will be the smaller one, but
not
> > > >> always.
> > > >> For BOL to say the smaller of the two is the build input is a bit
of an
> > > >> overgeneralization.
> > > >>
> > > >> --
> > > >> HTH
> > > >> --
> > > >> Kalen Delaney
> > > >> SQL Server MVP
> > > >> www.SolidQualityLearning.com
> > > >>
> > > >>
> > > >> "Leila" <lelas@.hotpop.com> wrote in message
> > > >> news:eejiM5QoEHA.3788@.TK2MSFTNGP10.phx.gbl...
> > > >> > Thanks Kalen!
> > > >> > You mentioned 'the data in the inner table is organized into a
hash
> > > >> > table'.
> > > >> > I read in BOL 'the smaller of the two inputs is the build input'.
> > > >> > Are they different?
> > > >> >
> > > >> >
> > > >> >
> > > >> >
> > > >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > > >> > news:ODudYpQoEHA.260@.TK2MSFTNGP10.phx.gbl...
> > > >> >> The 'only' difference is a very expensive one.
> > > >> >> If you have an index, SQL Server can take a value from the outer
> > table
> > > >> >> and
> > > >> >> use the index to find matching rows in the inner table.
> > > >> >>
> > > >> >> With a hash match, which is used because there IS no useful
index,
> > the
> > > >> > data
> > > >> >> in the inner table is organized into a hash table, so that SQL
> > Server
> > > > can
> > > >> >> find matching rows using the hash table instead of an index.
> > > >> >> Al though the inner table is scanned only once, the process of
> > > >> >> building
> > > >> > the
> > > >> >> hash table is resource intensive, and the hash table uses a lot
of
> > > > memory
> > > >> >> for a big table.
> > > >> >>
> > > >> >> You're better off building a good index to make the nested loops
> > > >> >> possible.
> > > >> >>
> > > >> >> --
> > > >> >> HTH
> > > >> >> --
> > > >> >> Kalen Delaney
> > > >> >> SQL Server MVP
> > > >> >> www.SolidQualityLearning.com
> > > >> >>
> > > >> >>
> > > >> >> "Leila" <lelas@.hotpop.com> wrote in message
> > > >> >> news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
> > > >> >> > Hi Kalen,
> > > >> >> > Thanks for your suggestion.
> > > >> >> > I'm a little confused about the difference between Hash Match
and
> > > >> >> > Nested
> > > >> >> > Loops. As far as I learned from BOL, in Hash Match, the hash
> > values
> > > > are
> > > >> >> > moved from the base table to a new place in memory(called hash
> > > > table),
> > > >> >> > then
> > > >> >> > an operation like nested loop happens between hash table and
> > another
> > > >> >> > table.
> > > >> >> > In nested loops, no value is moved from the base table,
instead
> > the
> > > >> >> > loop
> > > >> >> > begins (with no hash table in between) directly with other
table.
> > > >> >> > It seems the only difference is the existence of hash table in
> > > > between,
> > > >> > is
> > > >> >> > that true?
> > > >> >> > Thanks again,
> > > >> >> > Leila
> > > >> >> >
> > > >> >> >
> > > >> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in
message
> > > >> >> > news:euCtqpPoEHA.3460@.TK2MSFTNGP10.phx.gbl...
> > > >> >> >> Hi Leila
> > > >> >> >>
> > > >> >> >> For your query tuning, it shouldn't matter what the actual
hash
> > > > values
> > > >> >> > are.
> > > >> >> >> If possible, you should try to build an index that will allow
SQL
> > > >> > Server
> > > >> >> > to
> > > >> >> >> perform a different join technique than hashing.
> > > >> >> >>
> > > >> >> >> Microsoft does not document any details of the hash functions
> > they
> > > > use
> > > >> >> >> for
> > > >> >> >> processing hash join operations. If you want to know more
about
> > > >> >> >> hashing
> > > >> >> >> in
> > > >> >> >> general, read "The Art of Computer Programming -- Volume 3:
> > Sorting
> > > >> >> >> and
> > > >> >> >> Searching" by Donald Knuth.
> > > >> >> >>
> > > >> >> >> --
> > > >> >> >> HTH
> > > >> >> >> --
> > > >> >> >> Kalen Delaney
> > > >> >> >> SQL Server MVP
> > > >> >> >> www.SolidQualityLearning.com
> > > >> >> >>
> > > >> >> >>
> > > >> >> >> "Leila" <lelas@.hotpop.com> wrote in message
> > > >> >> >> news:%23W3h0HPoEHA.324@.TK2MSFTNGP11.phx.gbl...
> > > >> >> >> > Hi,
> > > >> >> >> > In hash joins, how the hash value is computed? For example
in
> > > >> >> >> > this
> > > >> >> > query:
> > > >> >> >> >
> > > >> >> >> > SET SHOWPLAN_ALL ON
> > > >> >> >> > select c.customerid ,o.orderid, o.shipcountry from
> > > >> >> >> > customers c right outer join orders o
> > > >> >> >> > on c.customerid=o.customerid
> > > >> >> >> > and o.shipcountry='germany'
> > > >> >> >> >
> > > >> >> >> > How the fields those appear in HASH:() predicate help to
create
> > > > hash
> > > >> >> >> > values?
> > > >> >> >> > I think my problem is that I don't know that what the hash
> > value
> > > > is.
> > > >> >> >> > Thanks,
> > > >> >> >> > Leila
> > > >> >> >> >
> > > >> >> >> >
> > > >> >> >>
> > > >> >> >>
> > > >> >> >
> > > >> >> >
> > > >> >> >
> > > >> >> >
> > > >> >>
> > > >> >>
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > >
> > >
> >
> >
>|||You don't need to understand it to tune your queries.
If you want to understand what hashing is all about, I suggest you take a
look at the reference at the beginning of the thread, or use google to
search for generic informaiton about hashing.
A join key is a column in one table that is matched with a column in another
table, Both tables then have join keys.
It sounds like you're describing a 'join expression'.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leila" <lelas@.hotpop.com> wrote in message
news:e6846sWoEHA.3792@.TK2MSFTNGP11.phx.gbl...
> Thanks Tibor!
> What I cannot understand is that what the meaning of "calculating hash
> value
> based on join key" is.
> Because join key is only the name of two fields plus an operator between
> them, it doesn't have any value itself (to be calculated).
> Leila
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:eZi#TaWoEHA.1776@.TK2MSFTNGP14.phx.gbl...
>> For each row in the probe table, a hash value is calculated based on the
> join key. Then SQL Server
>> looks in the hash bucked from the build table to see if there is any
> match. The key (no pun
>> intended) here is that the build table is splitted up into a lot of
> buckets, and for the other
>> table, SQL server only have to look in a specific bucket to find if
> there's a match.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Leila" <lelas@.hotpop.com> wrote in message
> news:uRd8FNWoEHA.3488@.TK2MSFTNGP12.phx.gbl...
>> > Does the hash table have an strucnture like index? If it doesn't, I
> think
>> > nested loop is inevitable for matching rows between hash table and the
> probe
>> > table.
>> >
>> >
>> >
>> >
>> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> > news:eQJ6HbRoEHA.2108@.TK2MSFTNGP10.phx.gbl...
>> > > A nested loop is when the inner table is processed completely for
> each
>> > row
>> > > of the outer table.
>> > >
>> > > For hash joins the inner table is read once to build the hash table,
> and
>> > > then not touched again. Then each row of the outer table leads to a
> single
>> > > access of the hash table.
>> > >
>> > > --
>> > > HTH
>> > > --
>> > > Kalen Delaney
>> > > SQL Server MVP
>> > > www.SolidQualityLearning.com
>> > >
>> > >
>> > > "Leila" <lelas@.hotpop.com> wrote in message
>> > > news:OfY1jORoEHA.3760@.TK2MSFTNGP12.phx.gbl...
>> > > > Kalen,
>> > > > When the hash table is ready, will there be something like nested
> loop
>> > to
>> > > > match rows? Because Mark described that the bottom table is
>> > > > scanned once (not in a nested loop).
>> > > > Leila
>> > > >
>> > > >
>> > > > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> > > > news:u26#jDRoEHA.2900@.TK2MSFTNGP12.phx.gbl...
>> > > >> The 'inner' table is whichever one is chosen by the SQL Server
>> > optimizer
>> > > > to
>> > > >> build the hash table. Typically this will be the smaller one, but
> not
>> > > >> always.
>> > > >> For BOL to say the smaller of the two is the build input is a bit
> of an
>> > > >> overgeneralization.
>> > > >>
>> > > >> --
>> > > >> HTH
>> > > >> --
>> > > >> Kalen Delaney
>> > > >> SQL Server MVP
>> > > >> www.SolidQualityLearning.com
>> > > >>
>> > > >>
>> > > >> "Leila" <lelas@.hotpop.com> wrote in message
>> > > >> news:eejiM5QoEHA.3788@.TK2MSFTNGP10.phx.gbl...
>> > > >> > Thanks Kalen!
>> > > >> > You mentioned 'the data in the inner table is organized into a
> hash
>> > > >> > table'.
>> > > >> > I read in BOL 'the smaller of the two inputs is the build
>> > > >> > input'.
>> > > >> > Are they different?
>> > > >> >
>> > > >> >
>> > > >> >
>> > > >> >
>> > > >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> > > >> > news:ODudYpQoEHA.260@.TK2MSFTNGP10.phx.gbl...
>> > > >> >> The 'only' difference is a very expensive one.
>> > > >> >> If you have an index, SQL Server can take a value from the
>> > > >> >> outer
>> > table
>> > > >> >> and
>> > > >> >> use the index to find matching rows in the inner table.
>> > > >> >>
>> > > >> >> With a hash match, which is used because there IS no useful
> index,
>> > the
>> > > >> > data
>> > > >> >> in the inner table is organized into a hash table, so that SQL
>> > Server
>> > > > can
>> > > >> >> find matching rows using the hash table instead of an index.
>> > > >> >> Al though the inner table is scanned only once, the process of
>> > > >> >> building
>> > > >> > the
>> > > >> >> hash table is resource intensive, and the hash table uses a lot
> of
>> > > > memory
>> > > >> >> for a big table.
>> > > >> >>
>> > > >> >> You're better off building a good index to make the nested
>> > > >> >> loops
>> > > >> >> possible.
>> > > >> >>
>> > > >> >> --
>> > > >> >> HTH
>> > > >> >> --
>> > > >> >> Kalen Delaney
>> > > >> >> SQL Server MVP
>> > > >> >> www.SolidQualityLearning.com
>> > > >> >>
>> > > >> >>
>> > > >> >> "Leila" <lelas@.hotpop.com> wrote in message
>> > > >> >> news:%23t9lNSQoEHA.2340@.TK2MSFTNGP10.phx.gbl...
>> > > >> >> > Hi Kalen,
>> > > >> >> > Thanks for your suggestion.
>> > > >> >> > I'm a little confused about the difference between Hash Match
> and
>> > > >> >> > Nested
>> > > >> >> > Loops. As far as I learned from BOL, in Hash Match, the hash
>> > values
>> > > > are
>> > > >> >> > moved from the base table to a new place in memory(called
>> > > >> >> > hash
>> > > > table),
>> > > >> >> > then
>> > > >> >> > an operation like nested loop happens between hash table and
>> > another
>> > > >> >> > table.
>> > > >> >> > In nested loops, no value is moved from the base table,
> instead
>> > the
>> > > >> >> > loop
>> > > >> >> > begins (with no hash table in between) directly with other
> table.
>> > > >> >> > It seems the only difference is the existence of hash table
>> > > >> >> > in
>> > > > between,
>> > > >> > is
>> > > >> >> > that true?
>> > > >> >> > Thanks again,
>> > > >> >> > Leila
>> > > >> >> >
>> > > >> >> >
>> > > >> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in
> message
>> > > >> >> > news:euCtqpPoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>> > > >> >> >> Hi Leila
>> > > >> >> >>
>> > > >> >> >> For your query tuning, it shouldn't matter what the actual
> hash
>> > > > values
>> > > >> >> > are.
>> > > >> >> >> If possible, you should try to build an index that will
>> > > >> >> >> allow
> SQL
>> > > >> > Server
>> > > >> >> > to
>> > > >> >> >> perform a different join technique than hashing.
>> > > >> >> >>
>> > > >> >> >> Microsoft does not document any details of the hash
>> > > >> >> >> functions
>> > they
>> > > > use
>> > > >> >> >> for
>> > > >> >> >> processing hash join operations. If you want to know more
> about
>> > > >> >> >> hashing
>> > > >> >> >> in
>> > > >> >> >> general, read "The Art of Computer Programming -- Volume 3:
>> > Sorting
>> > > >> >> >> and
>> > > >> >> >> Searching" by Donald Knuth.
>> > > >> >> >>
>> > > >> >> >> --
>> > > >> >> >> HTH
>> > > >> >> >> --
>> > > >> >> >> Kalen Delaney
>> > > >> >> >> SQL Server MVP
>> > > >> >> >> www.SolidQualityLearning.com
>> > > >> >> >>
>> > > >> >> >>
>> > > >> >> >> "Leila" <lelas@.hotpop.com> wrote in message
>> > > >> >> >> news:%23W3h0HPoEHA.324@.TK2MSFTNGP11.phx.gbl...
>> > > >> >> >> > Hi,
>> > > >> >> >> > In hash joins, how the hash value is computed? For example
> in
>> > > >> >> >> > this
>> > > >> >> > query:
>> > > >> >> >> >
>> > > >> >> >> > SET SHOWPLAN_ALL ON
>> > > >> >> >> > select c.customerid ,o.orderid, o.shipcountry from
>> > > >> >> >> > customers c right outer join orders o
>> > > >> >> >> > on c.customerid=o.customerid
>> > > >> >> >> > and o.shipcountry='germany'
>> > > >> >> >> >
>> > > >> >> >> > How the fields those appear in HASH:() predicate help to
> create
>> > > > hash
>> > > >> >> >> > values?
>> > > >> >> >> > I think my problem is that I don't know that what the hash
>> > value
>> > > > is.
>> > > >> >> >> > Thanks,
>> > > >> >> >> > Leila
>> > > >> >> >> >
>> > > >> >> >> >
>> > > >> >> >>
>> > > >> >> >>
>> > > >> >> >
>> > > >> >> >
>> > > >> >> >
>> > > >> >> >
>> > > >> >>
>> > > >> >>
>> > > >> >
>> > > >> >
>> > > >>
>> > > >>
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>