The reason for the loop join is not the table variable. It is the subselect for the XML-output. It doesn't matter which kind of tables you take, when using the...

I would NOT recommend use SHRINKDB "just for fun". Of course is clears all unused disk space but it also can have it's negative impact, from maximum fragmented indexes to...

This really sounds familiar to me 😀

WolfgangE (11/30/2012)

---

I know this behaviour and I think it's a little bit strange. It seems the identity insert can only be set to on...

I know this behaviour and I think it's a little bit strange. It seems the identity insert can only be set to on for one table at a time.

So the...

A further possibility could be hidden within the indexes. Do indexes have much fragmentation or are they reorganized regularly? Fragmented indexes can have a lot of impact on the their...

One idea:

;with cteConfig as (

select *,

Number = dense_rank() over ( order by [Date] )

from tempserverConfig

)

select *

from cteConfig c inner join

cteConfig prev on

prev.Number + 1 = c.Number and

prev.ServerName = c.ServerName...

hmmmmm...... what is LUN? what is SAN? And no, I HAVE NOT GOOGLED yet. I know I should have to, but if the terms are used here, why not explain...

ok, I could make an example. As expected, the where- and from-task has changed a little bit, but not the loop join for the subselect.

not really, restricted access and so on...

But I don't think this makes sense anyway. It won't change the fact that the subselect makes a lookup for any outer row.

Maybe I...

The table variable contains just a few rows, maybe 200 for the "large" processes. But the join sometimes results in more than 30.000 rows.

In general this question can be answered using a general example:

If your query can use an index for...

You can simply divide the values in the select list. In this example I return the time-values in seconds.

declare

@DividerMicroSecondsToMinutes int = 60000000

select total_elapsed_time / @DividerMicroSecondsToMinutes

from sys.dm_exec_query_stats

Same...

this one works:

set @sql = 'exec '+ QUOTENAME(@dbname) + '..sp_executesql N''create schema MyNewSchema'''

exec (@sql)

It should work if you include the USE-Statement in the dynamic SQL-String for creating the schema.

set @sql = 'use ' + QUOTENAME(@dbname) + ' create schema MyNewSchema';

exec (...

Here is a nice article which shows you why one would use this expression:

http://www.sqlservercentral.com/articles/IsNumeric/71512/