Transact SQL – Warning – No Join Predicate – Sourced to SQL as the ultimate free text scratch pad

Introduce

We all like SQL as it is fully expressive and lets us quickly get something down and add to it as we know more.

Sample SQL

Sample SQL – Good

Let us say we need to list object, column, and column Type data.  And, write a quick join on sys.objects, sys.columns, and sys.types:


select top 5 
	     tblObject.name
	   , tblColumn.name
	   , tblColumnType.name

from   sys.objects tblObject

	   inner join sys.columns tblColumn

		on tblObject.object_id = tblColumn.object_id

	  inner join sys.types tblColumnType

		on tblColumn.user_type_id = tblColumnType.user_type_id

where   tblObject.type = 'U'

Query Plan for Good SQL

sqlFreeText-Good

Sample SQL – Bad SQL

Drinking \ Glory days SQL.


select top 5 
	          tblObject.name
		, tblColumn.name
		, tblColumnType.name

from   sys.objects tblObject

	  inner join sys.columns tblColumn

		on tblObject.object_id = tblColumn.object_id

	inner join sys.types tblColumnType

		on tblObject.object_id = tblColumn.object_id

where   tblObject.type = 'U'

Query Plan for “Drinki(i)ng \ Glory Days” SQL

sqlFreeText-Bad

Quick Explanation:

If you look at the SQL, you will see that that the join clause on sys.types does not even reference the table name;  sys.types in this case:


inner join sys.types tblColumnType

		on tblObject.object_id = tblColumn.object_id

The Join on operators was a rush job and it was just a repetition of the preceding join clause.

Differences in Query Plan

Good SQL

In “Good SQL”, the “Nested Loops” operator has the “Outer References” operator that tells us which object is serving as the Outer element.

sqlFreeText-Good-Operator-NestedLoop-OuterReferences

Bad SQL

In “Bad SQL”, the “Nested Loops” operator does not have the “Outer References” element.

sqlFreeText-Bad-Operator-NestedLoop-OuterReferences

That is all!

One thought on “Transact SQL – Warning – No Join Predicate – Sourced to SQL as the ultimate free text scratch pad

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s