Transact SQL – Warning – No Join Predicates in “SQL Statement – Select Clause” Scaler Functions


Here is one that I wish I can say a lot more about and give solid SQL tables and data on.

But, though I have tried gallantly for the last few days, I have yet to be able to re-produce in a clean room environment.


I am here trying to troubleshoot slow queries and narrowed things down to one specific query that I will like to specifically discuss here.

Sample Query

           , tblOrder.[storeNumber]
           , dbo.udf_DistributorDropOffCenter
                                       , tblDistributor.[distributorName]

  from   [dbo].[order] tblOrder

            inner join [dbo].[orderDetail] tblOrderDetail

                on tblOrder.[orderNumber] = tblOrderDetail.[orderNumber]

            inner join [dbo].[orderDetail] tblOrderDetail

                on tblOrder.[orderNumber] = tblOrderDetail.[orderNumber]

            inner join [dbo].[distribuor] tblDistributor

                on tblOrderDetail.[distributorID] 
                      = tblDistributor.[distributorID]


Quick knows:

  • So what we have is a pretty deep Relational Database Model
  • Though, I spent time really trying to understand the SQL Plan, could not isolate the problem until I started dropping columns of the Select Clause.  I was then able to isolate the problem to the inter-exchange between the Query and the Scaler function

Scaler Function

  • As part of the columns we are retrieving, we have included a call to a multi-argument Scaler function
  • The function expects a few arguments spinkled across a few tables
  • Unfortunately, some of the primary keys are composite and so just passing along one PK column is not always sufficient; we have to pass in more than one attribute to fully identify the row we want referenced

Possible Resolutions

  • Determine whether you have passed along all the arguments that will ensure row uniqueness
  • Review your Scaler function and re-write it as Table Value Function.  Scalers can only return a single cell.  On the the other hand, TVFs can return more than 1 cell; in terms of multiple rows and columns
  • If you want to stay with Scaler Functions, see whether you can return your column as a comma separated set of columns — please Google for comma separated values in SQL Server
  • Stress test your Main SQL and see whether you you have repeating groups that you can avoid using “Group By”
    select tblA.[id], count(*) 
    from   dbo.tblA tblA 
              inner join dbo.tblB tblB
                on = 
    group by 
    having count(*) > 1
    order by count(*) desc
  • Stress test your Scaler Function using the sample given above


Aforementioned, I wish I can say more about this.  But, the actual code lines are buried deep within some proprietary code; which in turn is very domain specific.

Like Morgan Heritage  (“Man in Love” –, I have tried twice, yet I can not reproduce in my LAB Environment.

And, so I posted here to acknowledge the work we feed upon.  

Here is my public thanks to Grant Fritchey (Scary DBA) – , Paul White, and the rest of the public “committers”:


“As you quickly forget where you got it from, unless you quickly acknowledge who, where, and when”





Leave a Reply

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

You are commenting using your 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