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 All values to one select

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

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

Concat rows into string

I have a simple select statement - select groupname FROM Groups
This returns 30 rows. What I want is return everything as comma
seperated string like "group1, group2, group3..."
But I don't want to use function or cursor. Is there anyway? In on
SQL 2000.
thanksPlease have a look at this example:
http://p2p.wrox.com/topic.asp?TOPIC_ID=57982
Cheers,
Paul Ibison

Concat rows into string

I have a simple select statement - select groupname FROM Groups
This returns 30 rows. What I want is return everything as comma
seperated string like "group1, group2, group3..."
But I don't want to use function or cursor. Is there anyway? In on
SQL 2000.
thanksPlease have a look at this example:
http://p2p.wrox.com/topic.asp?TOPIC_ID=57982
Cheers,
Paul Ibison

Concat columns

I have to do some paging stuff. I am doing the follow in a proc.
Select top 10 * from customer
where @.SearchKey <= LastName+FirstName+CustomerNumber
Question is what index to I build on the database to be able to search
this effectively?
Thank you.
ChrisFirst don't select * - specify the columns you want returned.
Second your condition is probably always going to result in a table scan
because you're not searching columns, you're comparing 2 atomic values for
each record. One value being the variable and the other being the
concatenation of the 3 columns.
Better to:
SELECT col1, col2, colN
FROM dbo.customer
WHERE
LastName >= @.SearchKey
OR FirstName >= @.SearchKey
OR CustomerNumber >= @.SearchKey
You could then build some indexes on any or all of those 3 columns.
"Chris" wrote:

> I have to do some paging stuff. I am doing the follow in a proc.
> Select top 10 * from customer
> where @.SearchKey <= LastName+FirstName+CustomerNumber
> Question is what index to I build on the database to be able to search
> this effectively?
> Thank you.
> Chris
>|||Chris,
SELECT TOP 10 <COLUMN LIST>
FROM CUSTOMER
WHERE CUSTOMERNUMBER = @.CUSTOMERNUMBER --ASSUMING EACH CUSTOMER IS UNIQUE
BY THIS KEY
--CREATE INDEX ON CUSTOMERNUMBER
HTH
Jerry
"Chris" <no@.spam.com> wrote in message
news:eIE$cwP0FHA.3336@.TK2MSFTNGP12.phx.gbl...
>I have to do some paging stuff. I am doing the follow in a proc.
> Select top 10 * from customer
> where @.SearchKey <= LastName+FirstName+CustomerNumber
> Question is what index to I build on the database to be able to search
> this effectively?
> Thank you.
> Chris|||>> Question is what index to I build on the database to be able to search
Try this and see if it makes a difference. Create a computed column like:
concat AS last_name + first_name + cust_nbr.
Then cluster the computed column like:
CREATE CLUSTERED INDEX Idx ON Customers( concat ASC )
Note that this will bias the table access for queries involving this
specific predicate and could potentially slowdown other data manipulation
operations.
Anith|||Jerry Spivey wrote:
> Chris,
> SELECT TOP 10 <COLUMN LIST>
> FROM CUSTOMER
> WHERE CUSTOMERNUMBER = @.CUSTOMERNUMBER --ASSUMING EACH CUSTOMER IS UNIQUE
> BY THIS KEY
> --CREATE INDEX ON CUSTOMERNUMBER
> HTH
> Jerry
> "Chris" <no@.spam.com> wrote in message
> news:eIE$cwP0FHA.3336@.TK2MSFTNGP12.phx.gbl...
>
>
>
That doesn't solve my problem. I'm trying to search for the person
alphabetically after the person I passed in. you way just scans for the
customer id.
Chris|||OK...perhaps you should say that in your original post next time.
"Chris" <no@.spam.com> wrote in message
news:eL$5goQ0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Jerry Spivey wrote:
> That doesn't solve my problem. I'm trying to search for the person
> alphabetically after the person I passed in. you way just scans for the
> customer id.
> Chris|||Try:
CREATE PROC #APROC
@.LASTNAME VARCHAR(20)
AS
SELECT <COLUMN LIST>
FROM AUTHORS
WHERE LASTNAME > @.LASTNAME
ORDER BY LASTNAME
--CONSIDER CREATING A CLUSTERED INDEX ON LASTNAME
--DROP PROC #APROC
HTH
Jerry
"Chris" <no@.spam.com> wrote in message
news:eL$5goQ0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Jerry Spivey wrote:
> That doesn't solve my problem. I'm trying to search for the person
> alphabetically after the person I passed in. you way just scans for the
> customer id.
> Chris|||KH wrote:
> First don't select * - specify the columns you want returned.
> Second your condition is probably always going to result in a table scan
> because you're not searching columns, you're comparing 2 atomic values for
> each record. One value being the variable and the other being the
> concatenation of the 3 columns.
> Better to:
> SELECT col1, col2, colN
> FROM dbo.customer
> WHERE
> LastName >= @.SearchKey
> OR FirstName >= @.SearchKey
> OR CustomerNumber >= @.SearchKey
> You could then build some indexes on any or all of those 3 columns.
>
> "Chris" wrote:
>
This won't work.. If I have the name 'Smith, Joe 1234' your solution
will give me as long as their name is getter than Smith or have a first
name greater than joe (i.e. "Apple, Zebra" would be allowed)
Chris|||On Fri, 14 Oct 2005 16:16:20 -0400, Chris wrote:

>I have to do some paging stuff. I am doing the follow in a proc.
>Select top 10 * from customer
>where @.SearchKey <= LastName+FirstName+CustomerNumber
>Question is what index to I build on the database to be able to search
>this effectively?
>Thank you.
>Chris
Hi Chris,
Anith's suggestion to add a computed column and index it is good. But if
that's for some reason unwanted, then try the query below. The extra AND
might look redundant, but your version is unable to use an index on the
LastName column, and the redundant extra AND does enable some
preselection using that index (if there is one, that is).
SELECT TOP 10 Column1, column2, ... -- Don't use SELECT *
FROM customer
WHERE @.SearchKey <= LastName+FirstName+CustomerNumber
AND @.SearchKey <= LastName
By the way, are you aware that using TOP without ORDER BY will yield
undefined results?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you have 'Smith, Joe 1234' your solution won't work either, as (I'm
assuming your data doesn't have the spaces and commans in it) concating the
fields will yield 'SmithJoe1234' which would probably fail the condition in
most cases.
Why are you using less than operators for string comparison anyways?
You should be dealing with things like that before it gets to the database.
"Chris" wrote:

> KH wrote:
> This won't work.. If I have the name 'Smith, Joe 1234' your solution
> will give me as long as their name is getter than Smith or have a first
> name greater than joe (i.e. "Apple, Zebra" would be allowed)
> Chris
>

CONCAT + expression

how can i CONCAT 2 columns & () ?
SELECT CONCAT(A,B) AS C From myTAble
but I want to get A (B)
dog (red)
thank youare they both strings?

if so SELECT COALESCE(A,'') + '(' + COALESCE(B,'') + ')'|||yes string(100)

Concat

Hi
I want to Concat Rows of a Column in a Table with One "Select" Order (I mean without use cursor)

Example:

Table1:
Column1
----
'a'
'b'
'c'

Result:
'abc'

Please answer me

thanks alotif you're using MySQL, use the GROUP_CONCAT function

if you're using Sybase ASE, use the LIST function

otherwise, a cursor is actually not a bad idea, because other database systems don't have a similar aggregate functionsqlsql

Con Cat In Ate

I have a first name and a last name, in two different fields. I use a simple bit of SQL to show them together.

SELECT
{fn concat(dbo.table_employee.first_name , dbo.table_employee.last_name)}
FROM
dbo.table_employee

Gives me results like BradWilliams and DarWilliams and such.

Simple enough. But, it would be nice to put a space between first name and last name. And that is sending me for a loop. Any advise?

Thanks,
DanDan
why do it at SQL level - do it at the presentation / UI level, unless you have a direct need - ie the export from the SQL goes to another process.

how do you handle null forenames. depending on the SQL engine you are using you could write a function to do this.|||SELECT
{fn concat (dbo.table_employee.first_name,
{fn concat (' ', dbo.table_employee.last_name)})}
FROM
dbo.table_employee|||Perfect, Ida, that got it, thank you|||I wouldn't go so far as 'perfect'
say Fname= "Dan", SName="Srobe"
SQL returns: "Dan_Srobe"
say Fname= SName="Dansrobe"
SQL returns: "_Dansrobe", when I'm guessing you would want "Dansrobe" with no space "_".
It becomes more of a problem if you want to build a name from say Title,FName,SName,Qualifications where you could end up with
"__Dansrobe_"

still if it works appropriately then maybe it is "perfect" after all.

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