Concatenate strings from different rows

Hello.

I have a table like:

Id Name Description OrderId
1 Microsoft This is a 0
1 Microsoft huge company. 1

I need to create a select query that will concatenate description for both entries and output it like

Microsoft - This is a huge company.

Try using the USE XML PATH () as part of the select statement; maybe something like:

Code Snippet

declare @.testData table
( id integer,
[Name] varchar(10),
Description varchar(15),
orderId integer
)
insert into @.testData
select 1, 'Microsoft', 'This is a', 0 union all
select 1, 'Microsoft', 'huge company.', 1

select distinct
[Name] + ' - ' +
( select description + ' ' as [text()]
from @.testData b
where a.id = b.id
order by orderId
for xml path ('')
) as outputString
from @.testData a

/*
outputString
--
Microsoft - This is a huge company.
*/

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.
For example:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?
FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
Thanks!
Have you tried using a variable
Something like
DECLARE @.String varchar(100)
SET @.String = 'do' + 'ing'
SET @.String = ' FORMSOF (INFLECTIONAL,"' + @.String + '")'
--PRINT @.String
SELECT ..........................
WHERE CONTAINS(dbo.Dolch.vchWord, @.String)
Andy
"HumanJHawkins" wrote:

> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused "CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>
|||Would this work for you?
declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthl ink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string
It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.
For example:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?
FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
Thanks!Have you tried using a variable
Something like
DECLARE @.String varchar(100)
SET @.String = 'do' + 'ing'
SET @.String = ' FORMSOF (INFLECTIONAL,"' + @.String + '")'
--PRINT @.String
SELECT ..........................
WHERE CONTAINS(dbo.Dolch.vchWord, @.String)
Andy
"HumanJHawkins" wrote:

> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings togethe
r,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused "CombinedLexicon
s"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>|||Would this work for you?
declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthlink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string
It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.sqlsql

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.

For example:

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?

FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

Thanks!Would this work for you?

declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS[List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthl ink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS[List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string

It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.

Concatenate single quote...

Hi,
I am trying to concatenate single quotation mark (ie- ' ) in the select query but not succeed.
If I concatenate word India's, how should I have to write the select query..?
Its just query I have not concern with any specific database.select a='India''s', b='India'+'''s'

a b
--- ---
India's India's

(1 row(s) affected)|||Thanx buddy...

Concatenate Query Results into a String most effectively

Hi all,
I want to concatenate the results from a query in a string in the most
performance-friendly manner. E.g. executing
SELECT Code, Price
FROM Inventory
returns
ABC, 120
EFG, 200
HIJ, 245.50
...
Instead of returning the result in the format above, I want it to look like
this
ABC, 120/ EFG, 200/ HIJ, 245.50/ ...
returned as a string, or a column.
The normal way is to use cursor, and loop all the rows and concatenate the
values in each row into the string. BUT I read cursors are not
performance-friendly.
So what is the most performance-friendly SQL statement(s) that I can write
(preferably in one statement) to do the above?
Please kindly advise. TQ very much in advance.Hi
Why not doung such things on the client side'?
CREATE TABLE #Test
(
col CHAR(1)NOT NULL,
col1 DECIMAL(5,2) NOT NULL
)
INSERT INTO #Test VALUES('A',120)
INSERT INTO #Test VALUES('B',155)
INSERT INTO #Test VALUES('C',20.33)
DECLARE @.st VARCHAR(100)
SET @.st=''
SELECT @.st=@.st+col+','+CAST(col1 AS VARCHAR(10))+'/' FROM #Test
SELECT @.st
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:F18E50B2-F6D0-4FA8-BC2D-2242935F01FE@.microsoft.com...
> Hi all,
> I want to concatenate the results from a query in a string in the most
> performance-friendly manner. E.g. executing
> SELECT Code, Price
> FROM Inventory
> returns
> ABC, 120
> EFG, 200
> HIJ, 245.50
> ...
> Instead of returning the result in the format above, I want it to look
like
> this
> ABC, 120/ EFG, 200/ HIJ, 245.50/ ...
> returned as a string, or a column.
> The normal way is to use cursor, and loop all the rows and concatenate the
> values in each row into the string. BUT I read cursors are not
> performance-friendly.
> So what is the most performance-friendly SQL statement(s) that I can write
> (preferably in one statement) to do the above?
> Please kindly advise. TQ very much in advance.|||Hi,
Thanks. Cool solution. Never think abt that!
"Uri Dimant" wrote:

> Hi
> Why not doung such things on the client side'?
Well I am customizing an existing program... So try to keep my changes
minimum and yet still get good perforamance. ;)
Furthermore doing such things on client side would mean I have to write
additional VB codes and Crystal Report to reformat the whole thing. SQL
statements still so much more powerful.

>
> CREATE TABLE #Test
> (
> col CHAR(1)NOT NULL,
> col1 DECIMAL(5,2) NOT NULL
> )
> INSERT INTO #Test VALUES('A',120)
> INSERT INTO #Test VALUES('B',155)
> INSERT INTO #Test VALUES('C',20.33)
> DECLARE @.st VARCHAR(100)
> SET @.st=''
> SELECT @.st=@.st+col+','+CAST(col1 AS VARCHAR(10))+'/' FROM #Test
> SELECT @.st
>
>
> "HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
> news:F18E50B2-F6D0-4FA8-BC2D-2242935F01FE@.microsoft.com...
> like
>
>

Concatenate Multiple Rows?

I can't figure out how to write an SQL query that concatenates one field from a list of records having other parameters in common. It is easier to show than explain. Take the data set:

Spec T_R Section
A008 23w 1
A008 23w 2
A008 23w 4

I need a query that returns a single record/row like this:

Spec T_R Section
A008 23w 1, 2, 4

Any help would be appreciated.I've had this problem more times than I can count. While I was writing my SQL Tutorial (http://www.bitesizeinc.net/index.php/sql.html), I ran across this function for MySQL :

group_concat(field)

Which concatenates the grouped results into a string. If you are using Oracle, you'll need a stored procedure...

-Chrissqlsql

Concatenate field based on unique id. (Follow up)

thanks for your earlier reply.

If i want to order the IDs by a Timestamp column in descending order how do i do it. I couldn't do it in Inner query.
right now it gives in random order. Is there any other way to get it?

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, createTS, count(*) as seq
from your_table as t1 join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment, createTS
order by createTS desc -> gives error
) as t3
group by t3.id;

Got it working. I used 'top 100 percent" and used the ORDER BY in inner query.
Thanks.|||Note that using TOP 100 PERCENT is still not guaranteed to work. It depends on the query plan and even more so in SQL Server 2005. The use of ORDER BY clause is specific to a scope only and in your example to the derived table. Generally, you should not rely on the order in which the rows are processed in a SELECT statement. You should basically consider a SELECT statement source as an unordered set of rows. In your example, you can achieve the results by changing the condition:

t2.Comment <= t1.Comment

to

t2.CreateTs <= t1.CreateTs

This assumes that time stamp value is unique per id and this would guarantee that the sequence number is based on sorting the values in ascending order. You can incorporate additional conditions to handle matching time stamps and different comments. As I said before, doing these type of operations in SQL is not the right approach. These can be done very easily on the client side and with less work / assumptions.

Concatenate

In the SQL Query.. how do i Concatenate ?

I want to concatenate 2 of the same column and table..

example...
tableA with column id and y
tableB with column id and x and group

SELECT d.y + dd.y as PERM
FROM tableA d, tableA dd, tableB c
WHERE d.id = c.id
AND dd.id = c.groupThis is a very very difficult thing to figure out how to do.

One approach would be to look at the online documentation. While this requires advanced skills, I will try to walk your through it.

1) Open Internet Explorer (or your favorite browser)
2) Navigate to http://msdn.microsoft.com
3) Enter in a search of "sql concatenate"
4) Click on the first hyper link provided.
5) Read the page that is displayed.

In case #1 through #4 are too difficult, click here (http://msdn2.microsoft.com/en-us/library/aa276862(SQL.80).aspx) (the blue underlined part is what you click on).

In case reading a page of material is too time consuming:

USE pubs
SELECT (au_lname + ', ' + au_fname) AS Name
FROM authors
ORDER BY au_lname ASC, au_fname ASC

And finally in case the sample is too hard to follow....

Just put parenthesis around the expression!!!!!!|||well... the problem is SQL Query in Crystal Report seems not allowing me to put the a and b in the select part...

what i need to to concatenae one table of the same column ~~~

what if like your example... but instead of
(au_lname + ', ' + au_fname) AS Name

what i want is......
(au_lname + ', ' + au_lname) AS Name|||well... the problem is SQL Query in Crystal Report seems not allowing me to put the a and b in the select part...

what i need to to concatenae one table of the same column ~~~

what if like your example... but instead of
(au_lname + ', ' + au_fname) AS Name

what i want is......
(au_lname + ', ' + au_lname) AS Name

Those two look exactly the same to me... (but I have been here 19+ hours...)|||hhahaa...
i see... hmmm...

the difference is instead of Lname and Fname...

i want both Lname...

your not sleepy huh ?sqlsql

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