As you have found out the for each loop does not provide ordering out of the box.

In the past i have used a script component to provide the ordered file...

OK, this effect is *probably* what you are seeing..

declare @x integer=0

while(@x<3)begin

declare @tab table

(

col1 integer

)

insert into...

I wouldnt worry about deleting the data from a temp table or table var just let SQL Server clean up when it drop out of scope.

And, No the data does...

Use the OUTPUT clause and the $action column https://msdn.microsoft.com/en-us/library/ms177564.aspx you can then aggregate over that.

Note: I have found that it is a real performance hit and only do this if...

Velveeta22 (2/11/2015)

---

Unfortunately this query is the only part of the transaction that I can grab. The rest is buried in .NET code that I do not have access to....

nwtsqlserv (2/11/2015)

---

Hi I have one more question. I am using table valued parameter to pass the whole list to the stored proc. But now I want to insert this table...

Yup, cursors and any form of looping / iterative is to be avoided in SQL if at all possible ( and that advice hasn't changed since year dot )...

Other than going through the unnecessary overhead of concatenating the string on...

Trace / SQLProfiler ( at least in 2014, cant test earlier ) has a RowCount column.

Create a serverside trace https://msdn.microsoft.com/en-us/library/cc293613.aspx

throw the results into a staging table and then filer...

Using a string is a bit wrong here,

Using a table valued parameter https://msdn.microsoft.com/en-us/library/bb510489.aspx would be a better solution in my book.

I dont understand why this cannot be a view ?

If you can SELECT from a table you can SELECT from a view.

Just make sure you ORDER BY on the SELECT...

Generated a connect item for this issue, i dont think it can be right...

https://connect.microsoft.com/SQLServer/feedback/details/807755/querying-a-snapshot-database-issues-a-vast-quantity-more-physical-reads-than-the-regular-user-database

Glad it not only me who thinks that this is weird 🙂

From :

select @@version

CREATE DATABASE AdventureWorks2012ss ON

( NAME = AdventureWorks2012_Data, FILENAME =

'C:\Data\AdventureWorks2012_data.ss' )

AS SNAPSHOT OF AdventureWorks2012;

go

set statistics io...

Thanks Kevin,

This is on 3000, can see anything in the release notes that is directly related, applying CU3 now.

Edited, try that again now...