concatenate a text data type

Hi,
How to concatenate a text data type value with another text data type
value or varchar data type value.
Regards
KrishnaKrishna (krishna_hot@.hotmail.com) writes:
> How to concatenate a text data type value with another text data type
> value or varchar data type value.
You will have to look into UPDATETEXT.
Note that you cannot assign variables of the type text at all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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
>> > > >> >> >> >
>> > > >> >> >> >
>> > > >> >> >>
>> > > >> >> >>
>> > > >> >> >
>> > > >> >> >
>> > > >> >> >
>> > > >> >> >
>> > > >> >>
>> > > >> >>
>> > > >> >
>> > > >> >
>> > > >>
>> > > >>
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>

Computing and assigning a value to a textbox from other data regions

I am new to SSRS, so perhaps its a trivial question. I was wondering that since all controls have names in the report, is it possible to programatically access values of different textboxes, do some computation and then assign to another text box? I know how to do it using the Aggregate functions and operators, but am not sure if I can access values from textboxes within two different tables and assign the computed value to a third text box on the page (not belonging to any table or other control).

somethig like.... txtTotal.Value = FormatCurrency(txtSalesTotal.Value) - txtDiscount.Value));

Any ideas?

DNG.

People!!!!!!!!!!!

How can I add values in two text boxes and display in the third one? Apparently I can't do this simple thing in SSRS or may be I am missing something? I remember you can do this in Crystal by accessing textbox control and can access their values to be used elsewhere on the page. Can we create variables, where I can store the value of a text box and use it later in other text boxes?

Plz. help!!!

DNG

|||

Let's see if I understand what you are asking. TextBox1 has the sum of values from DataSet1. TextBox2 has the sum of values from DataSet2. These text boxes are not in a table. To sum the value you would need the following in the expression of textbox3:

Code Snippet

=reportitems!TextBox1.Value + reportitems!TextBox2.Value

Hope this helps.

Simone

|||If any of these textboxes are in tables (which I believe you may have mentioned) then you can only reference items within the same scope and you will receive errors.|||

I should add that in the above case, you can use the expression contained within the first two text boxes to give you the value in the 3rd.

ex. TextBox3 expression =

Code Snippet

=Sum(Fields!Value.Value, "dsTest") + Sum(Fields!Value.Value, "dsTest2")

Simone|||Thanks for your help.sqlsql

computed value

I've added a column to my table as a computed value, pretty much only to
populate it for the existing data. I want to do this, then drop the compute
d
value, make it's new column NOT NULL and lay over it a unique constraint.
Can I drop the computed value w/out dropping the column?
-- Lynn"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:4052E967-8301-4F0D-A2FF-C721068FC0B3@.microsoft.com...
> I've added a column to my table as a computed value, pretty much only to
> populate it for the existing data. I want to do this, then drop the
> computed
> value, make it's new column NOT NULL and lay over it a unique constraint.
> Can I drop the computed value w/out dropping the column?
> -- Lynn
Why not add it as NOT NULL and default it to zero, or x or whatever.
Then run and update on the column and compute your new values.
Better yet, why are you storing a computed value anyhow? Why not just use a
SELECT statement or a view and compute the value on the fly?
Rick Sawtell|||A computed column or one being used in a computed column cannot be altered,
nor can a computed column be updated.
What are you traing to do? If you need to change the behaviour of a computed
column, you need to drop it first. You cannot add a non-nullable column
without specifying a default value.
However, you can create a temporary table to store the values of the
computed column, drop the column, add a new column (it needs to be nullable)
,
then fill it with previous values, and alter it to make it non-nullable.
And if you post DDL and sample data, we can help you build a script to
achieve all this.
ML|||Thank you both. I think I realized I was overthinking this one a bit. It
doesn't have to be computed. I put MsgID on as varchar(64) NOT NULL, update
d
it for existing data w/this:
UPDATE tableA...
SET MsgID = endpoint+(convert(varchar(8),[exectime],
112) + [ordernumber])
GO
Then I changed it to NOT NULL and created the unique constraint. All
w/existing data in the table. What do you guys think?
-- Lynn
"ML" wrote:

> A computed column or one being used in a computed column cannot be altered
,
> nor can a computed column be updated.
> What are you traing to do? If you need to change the behaviour of a comput
ed
> column, you need to drop it first. You cannot add a non-nullable column
> without specifying a default value.
> However, you can create a temporary table to store the values of the
> computed column, drop the column, add a new column (it needs to be nullabl
e),
> then fill it with previous values, and alter it to make it non-nullable.
> And if you post DDL and sample data, we can help you build a script to
> achieve all this.
>
> ML|||Does it work as it is supposed to work? :)
Looks like you've nailed it.
ML|||I don't know yet, it's still running now. I am sure hoping we're good on
this one...
-- Lynn
"ML" wrote:

> Does it work as it is supposed to work? :)
> Looks like you've nailed it.
>
> ML|||Worked beautifully. Thank you guys for looking into this w/me.
-- Lynn
"ML" wrote:

> Does it work as it is supposed to work? :)
> Looks like you've nailed it.
>
> ML|||On Wed, 7 Sep 2005 12:01:02 -0700, Lynn wrote:

>Thank you both. I think I realized I was overthinking this one a bit. It
>doesn't have to be computed. I put MsgID on as varchar(64) NOT NULL, updat
ed
>it for existing data w/this:
>UPDATE tableA...
>SET MsgID = endpoint+(convert(varchar(8),[exectime],
112) + [ordernumber])
>GO
>Then I changed it to NOT NULL and created the unique constraint. All
>w/existing data in the table. What do you guys think?
Hi Lynn,
If the MsgID column will always be the concatenation of these three
other columns, than I wouldn't store it like this. Create a view that
does the concatenation if you prefer the ease of use.
For enforcing uniqueness, just do
ALTER TABLE tableA
ADD CONSTRAINT MyUniq UNIQUE (endpoint, exectime, ordernumber)
No need to add an extra column for this.
Of course, if the MsgID for NEW rows in the database will be filled with
other values, and this concatenation is just the "starting" value for
existing rows, then the above doesn't apply.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Indexing that view might also be of help - that way the concatenated values
are stored as if the view were a table. Views aren't cached permanently.
However, as Hugo already stated, this only applies if the values need to be
generated by concatenation *every time*.
MLsqlsql