FOR XML query with more than 2 levels of data

I'm having trouble getting a FOR XML query to get the relationships correct when there are 3 levels of data.

In this example, I have 3 tables, GG_Grandpas, DD_Dads, KK_Kids. As you would expect, the Dads table is a child of the Grandpas table, and the Kids table is a child of the Dads table.

I'm using the Bush family in this example, these are the relationships:
- George SR
-- George JR
-- Jenna
-- Barbara
-- Jeb
-- Jeb JR
-- Noelle

These statements will create and populate the tables for the example with the above relationships:

SET NOCOUNT ON
DROP TABLE KK_Kids, DD_Dads, GG_Grandpas
CREATE TABLE GG_Grandpas ( GG_Grandpa_Key varchar(20) NOT NULL, GG_GrandpaName varchar(20))
CREATE TABLE DD_Dads ( DD_Dad_Key varchar(20) NOT NULL, DD_Grandpa_Key varchar(20) NOT NULL, DD_DadName varchar(20))
CREATE TABLE KK_Kids ( KK_Kid_Key varchar(20) NOT NULL, KK_Dad_Key varchar(20) NOT NULL, KK_KidName varchar(20))

ALTER TABLE GG_Grandpas ADD CONSTRAINT PK_GG PRIMARY KEY (GG_Grandpa_Key)
ALTER TABLE DD_Dads ADD CONSTRAINT PK_DD PRIMARY KEY (DD_Dad_Key)
ALTER TABLE KK_Kids ADD CONSTRAINT PK_KK PRIMARY KEY (KK_Kid_Key)
ALTER TABLE DD_Dads ADD CONSTRAINT FK_DD FOREIGN KEY (DD_Grandpa_Key) REFERENCES GG_Grandpas (GG_Grandpa_Key)
ALTER TABLE KK_Kids ADD CONSTRAINT FK_KK FOREIGN KEY (KK_Dad_Key) REFERENCES DD_Dads (DD_Dad_Key)

INSERT INTO GG_Grandpas VALUES ('GG_GEORGESR_KEY', 'GEORGE SR')
INSERT INTO DD_Dads VALUES ('DD_GEORGEJR_KEY', 'GG_GEORGESR_KEY', 'GEORGE JR')
INSERT INTO DD_Dads VALUES ('DD_JEB_KEY', 'GG_GEORGESR_KEY', 'JEB')
INSERT INTO KK_Kids VALUES ( 'KK_Jenna_Key', 'DD_GEORGEJR_KEY', 'Jenna' )
INSERT INTO KK_Kids VALUES ( 'KK_Barbara_Key', 'DD_GEORGEJR_KEY', 'Barbara' )
INSERT INTO KK_Kids VALUES ( 'KK_Noelle_Key', 'DD_JEB_KEY', 'Noelle' )
INSERT INTO KK_Kids VALUES ( 'KK_JebJR_Key', 'DD_JEB_KEY', 'Jeb Junior' )

So the question is, how do I get it to maintain the proper relationships between the records when I do an FOR XML query? Here is the query I am trying to get to work. Right now it puts all the Kids under a single Dad, rather than having them under their correct dads.
I am getting this, which is not what I want:

- George SR
-- George JR
-- Jeb
-- Jenna
-- Barbara
-- Jeb JR
-- Noelle

SELECT 1 as Tag,
NULL as Parent,
GG_GrandpaName as [GG_Grandpas!1!GG_GrandpaName],
GG_Grandpa_Key as [GG_Grandpas!1!GG_Grandpa_Key!id],
NULL as [DD_Dads!2!DD_DadName],
NULL as [DD_Dads!2!DD_Dad_Key!id],
NULL as [DD_Dads!2!DD_Grandpa_Key!idref],
NULL as [KK_Kids!3!KK_KidName],
NULL as [KK_Kids!3!KK_Dad_Key!idref]
FROM GG_Grandpas
UNION ALL
SELECT 2 ,
1 ,
NULL ,
GG_Grandpa_Key ,
DD_DadName ,
DD_Dad_Key ,
DD_Grandpa_Key ,
NULL ,
NULL
FROM GG_Grandpas, DD_Dads
WHERE GG_Grandpa_Key = DD_Grandpa_Key
UNION ALL
SELECT 3 ,
2 ,
NULL ,
GG_Grandpa_Key ,
NULL ,
DD_Dad_Key ,
NULL ,
KK_KidName ,
KK_Dad_Key
FROM GG_Grandpas, DD_Dads , KK_Kids
WHERE GG_Grandpa_Key = DD_Grandpa_Key
AND DD_Dad_Key = KK_Dad_Key

FOR XML EXPLICIT

I've tried it all different ways, but no luck so far.
Any ideas?I'm having trouble getting a FOR XML query to get the relationships correct when there are 3 levels of data.

Check this out..

SELECT dbo.GG_Grandpas.GG_GrandpaName, dbo.DD_Dads.DD_DadName, dbo.KK_Kids.KK_KidName
FROM dbo.DD_Dads

INNER JOIN dbo.GG_Grandpas
ON dbo.DD_Dads.DD_Grandpa_Key = dbo.GG_Grandpas.GG_Grandpa_Key

INNER JOIN dbo.KK_Kids
ON dbo.DD_Dads.DD_Dad_Key = dbo.KK_Kids.KK_Dad_Key

GROUP BY dbo.GG_Grandpas.GG_GrandpaName, dbo.DD_Dads.DD_DadName, dbo.KK_Kids.KK_KidName

for xml auto

result in xml

<dbo.GG_Grandpas GG_GrandpaName="GEORGE SR">
<dbo.DD_Dads DD_DadName="GEORGE JR">
<dbo.KK_Kids KK_KidName="Barbara" />
<dbo.KK_Kids KK_KidName="Jenna" />
</dbo.DD_Dads>
<dbo.DD_Dads DD_DadName="JEB">
<dbo.KK_Kids KK_KidName="Jeb Junior" />
<dbo.KK_Kids KK_KidName="Noelle" />
</dbo.DD_Dads>
</dbo.GG_Grandpas>

if you need those ids just include those ...|||You can't do a GROUP BY with a FOR XML query, at least not in the version I'm running. I get this message:
GROUP BY and aggregate functions are currently not supported with FOR XML AUTO.

Turns out a simple query does work for my example though:

SELECT GG_GrandpaName, DD_DadName, KK_KidName
FROM GG_Grandpas
LEFT OUTER JOIN DD_Dads ON DD_Grandpa_Key = GG_Grandpa_Key
LEFT OUTER JOIN KK_Kids ON DD_Dads.DD_Dad_Key = KK_Kids.KK_Dad_Key
for xml auto , elements

I think I simplified it too much for my example though, because it's still not working for my real world case.|||I believe I have it now. The trick is in the orderby clause. You have to order the results such that the children fall right after their parents in the result table or else it won't get the relationships correct.
I added another child to my previous example so that there is a separate Sons and Daughters table to fit my realworld problem better. This example might be easier to follow than the one in BOL, so I thought I'd post it.

Here is the code to setup the example:

SET NOCOUNT ON
DROP TABLE SS_Sons, DD_Daughters, FF_Fathers, GG_Grandpas

CREATE TABLE GG_Grandpas ( GG_Grandpa_Key varchar(20) NOT NULL, GG_Name varchar(20))
CREATE TABLE FF_Fathers ( FF_Father_Key varchar(20) NOT NULL, FF_Grandpa_Key varchar(20) NOT NULL, FF_Name varchar(20))
CREATE TABLE SS_Sons ( SS_Son_Key varchar(20) NOT NULL, SS_Father_Key varchar(20) NOT NULL, SS_Name varchar(20))
CREATE TABLE DD_Daughters ( DD_Daughter_Key varchar(20) NOT NULL, DD_Father_Key varchar(20) NOT NULL, DD_Name varchar(20))

ALTER TABLE GG_Grandpas ADD CONSTRAINT PK_GG PRIMARY KEY (GG_Grandpa_Key)
ALTER TABLE FF_Fathers ADD CONSTRAINT PK_FF PRIMARY KEY (FF_Father_Key)
ALTER TABLE SS_Sons ADD CONSTRAINT PK_SS PRIMARY KEY (SS_Son_Key)
ALTER TABLE DD_Daughters ADD CONSTRAINT PK_DD PRIMARY KEY (DD_Daughter_Key)

ALTER TABLE FF_Fathers ADD CONSTRAINT FK_FF FOREIGN KEY (FF_Grandpa_Key) REFERENCES GG_Grandpas (GG_Grandpa_Key)
ALTER TABLE SS_Sons ADD CONSTRAINT FK_SS FOREIGN KEY (SS_Father_Key) REFERENCES FF_Fathers (FF_Father_Key)
ALTER TABLE DD_Daughters ADD CONSTRAINT FK_DD FOREIGN KEY (DD_Father_Key) REFERENCES FF_Fathers (FF_Father_Key)

INSERT INTO GG_Grandpas VALUES ('GG_GEORGESR_KEY', 'GEORGE H')
INSERT INTO FF_Fathers VALUES ('FF_GEORGEJR_KEY', 'GG_GEORGESR_KEY', 'GEORGE W')
INSERT INTO FF_Fathers VALUES ('FF_JEB_KEY', 'GG_GEORGESR_KEY', 'JEB')
INSERT INTO SS_Sons VALUES ( 'SS_JebJR_Key', 'FF_JEB_KEY', 'Jeb Junior' )
INSERT INTO DD_Daughters VALUES ( 'DD_Jenna_Key', 'FF_GEORGEJR_KEY', 'Jenna' )
INSERT INTO DD_Daughters VALUES ( 'DD_Barbara_Key', 'FF_GEORGEJR_KEY', 'Barbara' )
INSERT INTO DD_Daughters VALUES ( 'DD_Noelle_Key', 'FF_JEB_KEY', 'Noelle' )

and here is the select statement:

SELECT 1 AS Tag,
NULL as Parent,
GG_Name as [GrandPas!1!GrandpaName!element],
NULL as [Fathers!2!FatherName!element],
NULL as [Sons!3!SonName!element],
NULL as [Daughters!4!DaughterName!element]
FROM GG_Grandpas
UNION ALL
SELECT 2 AS Tag,
1 as Parent,
GG_Name ,
FF_Name ,
NULL ,
NULL
FROM GG_Grandpas
LEFT OUTER JOIN FF_Fathers ON ( FF_Grandpa_Key = GG_Grandpa_Key )
UNION ALL
SELECT 3 AS Tag,
2 as Parent,
GG_Name ,
FF_Name ,
SS_Name ,
NULL
FROM GG_Grandpas
LEFT OUTER JOIN FF_Fathers ON ( FF_Grandpa_Key = GG_Grandpa_Key )
LEFT OUTER JOIN SS_Sons ON (SS_Father_Key = FF_Father_Key)
UNION ALL
SELECT 4 AS Tag,
2 as Parent,
GG_Name ,
FF_Name ,
NULL ,
DD_Name
FROM GG_Grandpas
LEFT OUTER JOIN FF_Fathers ON ( FF_Grandpa_Key = GG_Grandpa_Key )
LEFT OUTER JOIN DD_Daughters ON (DD_Father_Key = FF_Father_Key)
ORDER BY [GrandPas!1!GrandpaName!element], [Fathers!2!FatherName!element], [Sons!3!SonName!element], [Daughters!4!DaughterName!element]
FOR XML EXPLICIT|||You can't do a GROUP BY with a FOR XML query, at least not in the version I'm running. I get this message:
GROUP BY and aggregate functions are currently not supported with FOR XML AUTO.

Which version are you running? I have no problem in group by clause,Mine is SQL SERVER 2005 EXPRESS edition.I just gave you the result in xml also...so there is no error in that...|||I'm running 2000, not 2005:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

for xml explicit or xsl

I need to create a big xml report (about 60 elements, 9 levels deep). The data is in a single sql server table. For this I have been given an xsd file that the report must match in format.
What is my best option. As far as I know, I need either a for xml explicit query or I need to create an xsl document. Is this correct?
Thanks for any advice
Asim.
At 9 levels deep, I'd be inclined to go for a FOR XML AUTO query and then
apply an XSLT stylesheet. I haven't got any performance data to back this
up, but that's what my gut instinct tells me.
Anyone else with any actual hard-evidence to confirm / refute this?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:32876616-7A58-44B9-886D-A27744EF991F@.microsoft.com...
> I need to create a big xml report (about 60 elements, 9 levels deep). The
data is in a single sql server table. For this I have been given an xsd file
that the report must match in format.
> What is my best option. As far as I know, I need either a for xml explicit
query or I need to create an xsl document. Is this correct?
> Thanks for any advice
> Asim.
>
|||Thank you for the input.
Any pointers to where I could start creating a xslt file. Never did that. Any tools.
Can the xsd file be used in any way.
Asim.
|||Nine levels is actually not that bad for EXPLICIT mode queries from a perf
issue but hard to maintain.
Yukon's nesting capabilities would be better than either from a
programmabilty point of view.
Best regards
Michael
"Graeme Malcolm (Content Master Ltd.)" <graemem_cm@.hotmail.com> wrote in
message news:uT3FR8kIEHA.3832@.TK2MSFTNGP12.phx.gbl...
> At 9 levels deep, I'd be inclined to go for a FOR XML AUTO query and then
> apply an XSLT stylesheet. I haven't got any performance data to back this
> up, but that's what my gut instinct tells me.
> Anyone else with any actual hard-evidence to confirm / refute this?
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> "Asim" <anonymous@.discussions.microsoft.com> wrote in message
> news:32876616-7A58-44B9-886D-A27744EF991F@.microsoft.com...
> data is in a single sql server table. For this I have been given an xsd
> file
> that the report must match in format.
> query or I need to create an xsl document. Is this correct?
>
|||And to give some further perf information:
FOR XML AUTO may not provide you the right shape for postprocessing, but if
it does, the XSLT post processing will offload some of the shaping effort
from the server (thus may improve server-side throughput), but may be less
efficient end-to-end because of needing to serialize and reparse and reshape
the data (instead of shaping it directly using FOR XML explicit).
So from a performance point of view, I would think that FOR XML explicit is
in many cases more performant.
Best regards
Michael
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e48WQumIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> Nine levels is actually not that bad for EXPLICIT mode queries from a perf
> issue but hard to maintain.
> Yukon's nesting capabilities would be better than either from a
> programmabilty point of view.
> Best regards
> Michael
> "Graeme Malcolm (Content Master Ltd.)" <graemem_cm@.hotmail.com> wrote in
> message news:uT3FR8kIEHA.3832@.TK2MSFTNGP12.phx.gbl...
>
|||Michael thats good to know that Yukon will have better nesting solution for xml. Maybe I should do this report using xml explicit and convert to Yukon as soon as the Beta is out. This report is part of a larger B2B project and doesn't go into production u
ntil August. When will the Yukon Beta be available to the general public.
Also, I have been thinking to write an "abstract" SP that can create the xmpl explicit query dynamically. Has something like this been done? Any samples.
|||You have been most helpful.
One more question. Is there a way to return multiple records from the root table in a single xml explicit query. Below is a sample script and output to illustrate what I mean. In the query, if I include a where clause (transactionID = @.ID) all is fine but
can I return multiple transactionIDs with correctly formed xml. Again thanks for your time.
create table tblXMLTest
(
transactionId int not null primary key,
quantity varchar(100) null,
)
INSERT tblXMLTest(transactionId, quantity)
select 1, '20'
INSERT tblXMLTest(transactionId, quantity)
select 2, '25'
dbcc traceon(257)
SELECT
1 as Tag,
null as Parent,
transactionId as [rootRecord!1!transactionId!element],
null as [baseSegment!2!quantity!element]
FROM tblXMLTest
union all
SELECT
2 as Tag,
1 as Parent,
null as [rootRecord!1!transactionId!element],
quantity as [baseSegment!2!quantity!element]
FROM tblXMLTest
for xml explicit
drop table tblXMLTest
The above produces the output:
<rootRecord><transactionId>1</transactionId></rootRecord><rootRecord><transactionId>2</transactionId><baseSegment><quantity>20</quantity></baseSegment><baseSegment><quantity>25</quantity></baseSegment></rootRecord>
The desired output is:
<rootRecord><transactionId>1</transactionId><baseSegment><quantity>20</quantity></baseSegment></rootRecord><rootRecord><transactionId>2</transactionId><baseSegment><quantity>25</quantity></baseSegment></rootRecord>
Asim.
|||The Yukon Beta should be coming out this summer. The following webpage
should allow you to nominate yourself to the beta program.
Can you send me your email alias? I then can also ask internally.
Best regards
Michael
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:367BA9FB-6018-4B61-BA0A-ED6D1A56D087@.microsoft.com...
> Michael thats good to know that Yukon will have better nesting solution
> for xml. Maybe I should do this report using xml explicit and convert to
> Yukon as soon as the Beta is out. This report is part of a larger B2B
> project and doesn't go into production until August. When will the Yukon
> Beta be available to the general public.
> Also, I have been thinking to write an "abstract" SP that can create the
> xmpl explicit query dynamically. Has something like this been done? Any
> samples.
|||My email address is: asim.ahmed@.etrade.com and I work as a DBA for Etrade Financial's professional Trading division.
It would be great if I can get the Yukon Beta. By the way, your message did not include the link.
Also, I wrote a little procedure to dynamically create and run a xml explicit query, so am not worried about management as much. The only limitation is the 8000 character limit for the xml query.
I can post the script if you are interested.
Asim.
|||Thanks for the address. And sorry for not pasting the address. Here it is:
http://www.microsoft.com/sql/evaluat...ominations.asp
Best regards
Michael
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:EDDFF680-C785-446E-BE87-AB93531A47B1@.microsoft.com...
> My email address is: asim.ahmed at etrade.com and I work as a DBA for
> Etrade Financial's professional Trading division.
> It would be great if I can get the Yukon Beta. By the way, your message
> did not include the link.
> Also, I wrote a little procedure to dynamically create and run a xml
> explicit query, so am not worried about management as much. The only
> limitation is the 8000 character limit for the xml query.
> I can post the script if you are interested.
> Asim.
>

FOR XML EXPLICIT - Gaps in result set

I have a query that returns four levels of nested data. The problem is that in the data is missing in sporadic places in the result set. Is there a size limitation in the result set?
SQL Server is returning what looks to be multiple result sets to the client. The "gaps" in data seem to coincide with the end of one result set and the start of the next result set.
Is there an option I can set to return one result set?
Any help with this would be greatly appreciated.
Here is my query:
/************************************************** ************
Return the results as an XML string.
************************************************** *************/
Exec ('Select 1 as Tag,
Null as Parent,
t.[Name] As [GrandParent!1!Name!xml],
Null As [Parents!2!Name!xml],
Null As [Children!3!Name!xml],
Null As [Children!3!Description!xml],
Null As [GrandChildren!4!Name!xml],
Null As [GrandChildren!4!Description!xml]
From GrandParent t
Union All
Select 2 As Tag,
1 As Parent,
t.[Name],
p.[Name] As [Parents!2!Name!xml],
Null As [Children!3!Name!xml],
Null As [Children!3!Description!xml],
Null As [GrandChildren!4!Name!xml],
Null As [GrandChildren!4!Description!xml]
From GrandParent t, Parents p
Where t.MyID = p.ParentID
Union All
Select 3 As Tag,
2 As Parent,
t.[Name],
p.[Name] As [Parents!2!Name!xml],
c.[Name] As [Children!3!Name!xml],
c.[Description] As [Children!3!Description!xml],
Null As [GrandChildren!4!Name!xml],
Null As [GrandChildren!4!Description!xml]
From GrandParent t, Parents p, Children c
Union All
Select 4 As Tag,
3 As Parent,
t.[Name],
p.[Name] As [Parents!2!Name!xml],
c.[Name] As [Children!3!Name!xml],
c.[Description] As [Children!3!Description!xml],
g.[Name] As [GrandChildren!4!Name!xml],
g.[Description] As [GrandChildren!4!Description!xml]
From GrandParent t, Parents p, Children c, GrandChildren g
Order By [GrandParent!1!Name!xml], [Parents!2!Name!xml], [Children!3!Name!xml], [GrandChildren!4!Name!xml]
For XML Explicit')
A sample of the results follows (Unfortunately my formating did not come thru in the post so I manually indented. My comments are preceeded by ***):
<GrandParent><Name>Investment Practice</Name><Parents><Name>AAA - Asset Allocation Analysis/Strategy</Name><Children><Name>Industries Followed</Name><Description>Industries Followed</Description><GrandChildren><Name>CNS1 - Asset Allocation Strategy</Name>
***This is a different GrandChild Node The data between The start of the previous Grandchildren node(CNS1) and the "
n-cyclical Consumer Goods</Name> is gone. ***
<
n-cyclical Consumer Goods</Name><Description>EAI5</Description></GrandChildren><GrandChildren><Name>EAI6 - Health Care / Non-cyclical Services</Name><Description>EAI6</Description></GrandChildren><GrandChildren><Name>EAI7 - Financials</Name><Description>E
A
***This is a different GrandChild Node The data between The start of the previous Grandchildren node(EAI7) and the "me> is gone. ***
me>FIS1 - Treasuries/Sovereign/Agencies/TIPS</Name><Description>FIS1</Description></GrandChildren>
***This is what a GrandChild node should look like.
<GrandChildren><Name>FIS2 - Corporate - Investment Grader</Name><Description>FIS2</Description></GrandChildren><GrandChildren><Name>FIS3 - Mortgage Backed/XXX Portfolio</Name><Description>RMS4</Description></GrandChildren>
Further investigation reveals that the result is being broken in to 256 character result sets with gaps in the data between result sets.
Is there a conguration setting to increase the size? I haven't been able to find anything yet.
Can anyone help?
|||It finally dawned on me to check the options in Query Analyzer and I was able to change the default column width to the maximum of 8192.
This looks better. I am still getting multiple result sets, but I don't see any gaps (yet).
|||QA uses ODBC which is not supporting the FOR XML stream output well. You
should use ADO, OLEDB or ADO.net in order to programmatically retrieve FOR
XML results from the database.
Best regards
Michael
"Casey Loranger" <anonymous@.discussions.microsoft.com> wrote in message
news:FC5B9E3D-1177-4576-837E-C2C761FF0919@.microsoft.com...
> It finally dawned on me to check the options in Query Analyzer and I was
> able to change the default column width to the maximum of 8192.
> This looks better. I am still getting multiple result sets, but I don't
> see any gaps (yet).
>

For non-serialized does key lock lock more than one row?

For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall
"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David
|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
index
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>

For non-serialized does key lock lock more than one row?

For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
index[vbcol=seagreen]
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>

For non-serialized does key lock lock more than one row?

For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> > For Sql 2000, for isolation levels not serialized, can a key lock
> > (especially created by an inserted row), involving a nonprimary key
index
> > end up locking more than one row?
> >
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>