The leaf levels of the clustered index contain the data itself, but the tree construction of the index levels above that is similar to the non-clustered index (my understanding, others...

30 minutes is a long time for the volume of data you are talking about:

Check to see if the data is spread across the disks appropriately and that disk contention...

Could be a candidate for a PIVOT, but hard to tell without table definitions, sample data, and all the stuff the first link on your signature line asks for 😀

Please post the create statement for the tables and the indexes (data types are important). If this is an existing query, posting the actual execution plan would also be...

A quick example:

declare @x xml

select @x = BulkColumn from openrowset (

Bulk 'C:\Documents and Settings\drwebb\My Documents\cost center compare\TBCostCenterHierarchy.xml',Single_blob)...

A quick example:

DECLARE @XML XML = '<FTHeader>

<Transaction>

<TID>12345</TID>

<Name>John Smith</Name>

...

An excellent set of articles on all kinds of XML processing. Well worth the time to read:

http://www.sqlservercentral.com/articles/SS2K5+-+XML/3022/

Just a guess. This will give you the common ids and a count of how many tables they were found in.

IF OBJECT_ID('TempDb..#Temp1') IS NOT NULL

DROP TABLE #Temp1

CREATE TABLE #Temp1(ID...

So you only want the ids that are in common between the result sets? Since #3 didn't return any results, that one is not counted?

I'm not sure the results...

Not quite sure what you mean. Can you post some of the XML you have that would exhibit the problem and the results you'd want?

The proc Lynn gave you returns different values when zip code/state/city combinations that are in the table and zip code/state/city combinations that are not in the table are passed...

What do you want the proc to do if an invalid combination is passed in?

What do you want it to do when a valid combination is passed in?

On the job step that does the backup, is it set to "Run As' some other account?

Just a couple of points.

It doesn't bother you that you did someone's homework for them without asking them to contribute to the work? Zip codes have leading '0' in...

When I run the data you gave us above through the SQL I sent you, I get

the attached results. If this is correct, the the SQL I sent...