SQL Server – Strange Join Syntax in View Designer

sql serverssmssyntaxt-sqlview

I have a data warehouse with a lot of views built in with the SSMS view designer.
Viewing the syntax, the FROM clauses are unintelligible to me because the ON statements do not follow straight after the relevant JOIN <table>. Often the principal table of the query is RIGHT joined and a random "type" table is specified in the FROM. So I have a situation like:

SELECT *
FROM tableC
LEFT JOIN TableB
RIGHT JOIN TableA
ON TableA.ID = TableB.ID
ON TableB.TypeID = TableC.TypeID 
WHERE ....

Changing the order of the various JOIN and ON clauses to what I would have hand written changes the results in unexpected ways. And it would seem that most SQL prettyfiers choke on this ugly syntax but SSMS doesn't. I haven't been able to find rhyme, reason or documentation to the help unravel what is going on.

Any suggestions or links for figuring out and fixing view designer queries please?

Best Answer

This is just Standard SQL join syntax with the optional parentheses removed:

SELECT *
FROM tableC
LEFT JOIN 
(
    TableB
    RIGHT JOIN TableA
        ON TableA.ID = TableB.ID
)
ON TableB.TypeID = TableC.TypeID

If you don't like the syntax generated by the SSMS view designer (which is buggy and rarely updated anyway), simply write the views by hand using regular T-SQL.

Some people refer to this syntax style as 'nested join syntax'. Example blog post.

Related Solutions

Peculiar Oracle outer join syntax case

I tried to perform the mechanical process. I hope I remember it right.

This leads to:

SELECT ...
FROM A
         join B on A.A_ID = B.A_ID
    left join C on B.B_ID = C.A_ID and B.B_KEY = C.B_KEY and 'CONSTANT' = C.X_ID
    left join D on C.C_ID = D.C_ID
    left join E on B.A_ID = E.A_ID and B.B_KEY = E.B_KEY

In short I think Leigh Riffel's answer is correct.

Note

in old days the rule to memorize was: oracle where A.a = B.b (+) becomes A.a *= B.b in SQL-Server old syntax the plus goes to the opposite side and becomes a star, which means A left join B on A.a = B.b

Sql-server – more helpful way to detect lock acquire/release stats in SQL Profiler

Would that actually cause a table lock on the entire table, even though i'm only selecting certain rows into a table variable?

Lock escalation. If your 'select' is not ... selective, if it has to scan large portions of a table then the engine may escalate to table locks. See Lock Escalation (Database Engine).

I can see there are 2 Lock Escalations occurring, does that mean row locks have been escalated to table locks?

Are they lock escalation events, or just lock escalation attempts? If they succeeded then the transaction has locked the entire table.

Can someone give me some tips on how to trace this deadlock down further?

Approaching this from the individual locks POV is not very productive. The typical investigation focuses on capturing and analyzing the deadlock graph. See Analyzing Deadlocks with SQL Server Profiler. Capture the deadlock graph and post it here and maybe we can help. Upload the deadlock graph XML, not the picture of the deadlock, see How to: Save Deadlock Graphs (SQL Server Profiler).

Deadlock graph:

Spid 58 has page 7:1:11066 in IX mode and is blocking spid 61 that wants it in S mode. Spid 61 has page 7:1:1932345 in S mode and is blocking spid 58 who has an IU mode lock on it but wants it convert it to IX. Things are complicated by the addition of parallelism and a long list of 'me too' waiters, but the fundamental issue can be reduced to the one I described above.

This is a typical pattern of lack of indexes. You have an updater, XWingNew.dbo.UpdatePostsCleanedUriUniqueUri which is issuing an update that likely scans the entire XWingNew.dbo.Posts table. At the same time you have several readers likely scanning the entire table too in some linq generated query that was truncated in the XML: SELECT [UnionAll3].[C2] AS [C1], [UnionAll3].[C3] AS [C2], [UnionAll3].[C4] AS [C3], [UnionAll3].[C5] AS [C4],... . Because both the update and the select issue table scans they are pretty much guaranteed to deadlock when the update attempts to convert the IU page locks it acquired in the scan phase to IX locks needed to update the qualified rows.

You have two alternatives to pursue right now:

fix your application. The queries are clearly not tuned and there was no thought given to the indexing strategies. You will not only fix the deadlock problem, but your app will also be much faster.
the easy cop out: enable read committed snapshot. This will not fix the application flaws, but will hide them and eliminate the deadlocks.

Best Answer

Related Solutions

Peculiar Oracle outer join syntax case

Sql-server – more helpful way to detect lock acquire/release stats in SQL Profiler

Related Question