If no ORDER BY clause is included in the ROW_NUMBER() function, the row numbers generated are arbitrary. I should also say this will apply if you order using a...

So far, in my experience, the solution which has performed quickest for this kind of operation is the self join with a proper covering index, it just always seems to...

Books online has some pretty good information on importing files, using both T-SQL, bcp and SSIS:-

http://msdn.microsoft.com/en-us/library/ms187042.aspx - direct page for BULK INSERT, bcp, OPENROWSET

http://msdn.microsoft.com/en-us/library/ms141026.aspx - page for SSIS, this is...

The CONVERT_IMPLICIT is occurring because in your case sensitive index, "SOMETEXT" is not the same as "SomeText" or "sometext". So it has to convert the text, thus ruining your index....

Well as soon as you alter the collation, you're changing the sort order from the way in which the index is currently sorted, and so you will no longer be...

The problem looks to be the predicate for your scan is using CONVERT_IMPLICIT for your dealer_id and account_no.

I'm guessing this is being caused by the difference in collation between the...

Cheers, a lot of reading there so I'll go away and absorb it and test; will post back with results once I've done that!

Ah I'll bear that in mind for the future! Thanks very much for all the help, it's been brilliant. 😀

Just tried it, and had a massive improvement on performance - both queries ran in under 12 seconds.

The indexes were only created by me as I thought they'd help; there...

OK, I've run the two queries below:-

Cross apply, takes 19 seconds to return 27,968 rows:-

SELECT idvd.value('CaseNoIndividual[1]', 'int') AS CaseNoOtherNames

, idvd.value('FirstName[1]', 'varchar(100)') AS FirstName

, idvd.value('Surname[1]', 'varchar(100)') AS SurName

, idvd.query('OtherNames/OtherName').value('count(*)', 'int') AS...

Not quite; I load the file into the dbo.eiirXML_TEST table, then perform the shredding from there.

The query to populate the dbo.eiirIndividualDetails_TEST table takes 15 seconds, the query to populate the...

And the two execution plans.

Forgot to add total number of rows for the full document:-

Individual Details - 289166

Other Names - 294767

The code to create the schema collection, objects and with a small sample xml is attached; just getting the query plans now.

Will do, give me a few minutes and I'll get everything together.

Performance wise it's not too bad; the first query takes around 15 seconds to produce ~300k rows.

However, there's a slight tweak that needs making to the second query in order...