SQL Server Performance Forum – Threads Archive

Two non-critical performance related questions

Hello,
Two little questions that have been bugging me for some time, as a developer. There’s nothing urgent here but if someone could give some lights I’d be grateful!
1) Recursive assignment For the sake of curiosity, I have tried the following T-SQL code:
—————–
declare @s varchar (1024)
select @s = ” select @s = @s + someStringColumn
from SomeTable
order by someField select @s
—————– with SomeTable holding several records. Much to my surprise the result was the "sum" of all strings in the table (according to the books, the "select @var =" statement is supposed to return the last value). Is this normal behavior (it works too with Sybase ASE 12)? Anyway, it can be handy!
What does it give in terms of performance & locking, as compared with a cursor for example?
How does it compare with the sum() function when used on numeric types? When applicable, it could be a nice replacement to the product() simulator from the website (the one with log10() and power()).
2) Self-linked server And the second, unrelated question: suppose you have linked an SQL Server instance to itself. Is there any overhead when using remote object naming (e.g. MYSELF.DATABASE.dbo.Table instead of DATABASE.dbo.Table)? I can see 3 possibilities here:

• SQL Server is smart enough (or has all necessary information) to understand this is local access, and ignores the server qualifier;
• the OS is smarter than SQL Server: though the query incurs no network traffic, the engine passes it down the OLE DB layer and back up;
• dumbest scenario: the query travels through OLE DB and the loopback device, and then the other way round.

What is your opinion on this one (it may be neither of the 3, these are the only obvious to me)?
Thanks for your answers! Thomas

---

I’m not a developer, but I think + in your select work as string concatenation.
So, wait for developer members post.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Yep, that’s my understanding too! What surprises me actually is that the engine iterates over the contents of the table although it’s an assignment statement. With the explanation in the books I had expected the code to behave like: select @s2 = someStringColumn
from SomeTable
order by someField select @s = @s + @s2 The actual result (i.e. concatenation) is a clue that even in case of a simple assignment from a single row, the iteration takes place. The docs state that it returns the "last result", I guess there’s an iteration implied here — that’s consistent. However I feel it would be faster to return the first result only. When row order is not predictable, getting the "last result" is as bad as getting the "first result", so in both cases fetching the expected row is the developer’s responsibility… Maybe it’s an ANSI requirement or something.

---

About your second question I’m not sure how a loopback linked server will work but according to BOL it should only be used for testing when on a single network. So if it should only be used for testing, I assume it will behave as if it was a remote linked server and not perform any optimizations otherwise you really wouldn’t be doing any "real" tests of linked servers. On thing though is that distributed transactions does not work when used with loopback linked servers.

---

Yes I agree with Argyle, I cant prove it, but what I would expect is that a remote server linked to itself will go right down to the TCP/IP layer and the fact that WE know its a local connection will not be optimised by sql server. <br /><br />But in my opinion, this is not a reason to stop using it. There are some nice tricks you can do using loopback aliases, and the ‘network connection’ will not suffer from latency and obviously has enormous bandwidth <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />

---

Hi, Thanks for your replies! Concerning topic #1 (which I called "recursive assignment"), some searching on Google groups gives the definitive answer: the behaviour is unspecified. Allegedly, it’s not even predictable and the result may not be what one expects if the table has indices or if you use an "order by" clause. Thomas

---

We’re already very glad we can use "recursive assignment" to avoid cursors when concatenating values from multiple records. It’s a great feature for reporting. Of course you can use SELECT to assign a single value to a variable, but you had better be sure that the row set returned by the entire SELECT statement contains only one record. (By the way, this often means that you have to understand the meaning of the data, not just the structure.) You also have the option to use:
SET @var = (SELECT field FROM MyTable)
This may give you the "subquery returned more than 1 record" error message, depending on the number of rows returned by the SELECT statement. I would say that this behaviour is totally predictable, you just need to to have a clear understanding of what SELECT does, as opposed to SET.

---

]]>