14 YEARS ago,  my god!  Always makes me feel warm inside when then notification of such a blast from the past lands in my inbox.

Maybe its time not just to...

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.