The blog was down a bit today – sorry for all who got to see CLR stack traces pushed through HTTP (I *will* do a post on that sometime ;) …

A reader sent in a question about my previous RAND() post (here) and wanted to know if other functions were pre-evaluated.  The specific example was about DATEADD(), but my answer will apply to most functions.

The short answer is “DATEADD() is not pre-evaluated once per query in the same way as RAND() is”.

Most functions in SQL Server are evaluated once per row, just as you’d expect.  Even most non-deterministic functions are executed once per row.  The key reason for this is that often these functions are run over column data from a row in a table, and the value can change for each row.

So, this dateadd function is executed once per row: 

select DATEADD("dd", 5, col1) from t1

Here’s part of the showplan_xml output for this query
            <ColumnReference Column=”Expr1004″ />
                    <ScalarOperator ScalarString=”dateadd(day,(5),[t1].[dbo].[t1].[col1])”>
                      <Intrinsic FunctionName=”dateadd”>
                        <ScalarOperator>
                          <Const ConstValue=”(4)” />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue=”(5)” />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database=”[t1]” Schema=”[dbo]” Table=”[t1]” Column=”col1″ />
                          </Identifier>
                        </ScalarOperator>
                      </Intrinsic>
                    </ScalarOperator>

And here is what you see in the showplan_xml for RAND(): 

select RAND() from t1

                    <ScalarOperator ScalarString=”rand()”>
                      <Identifier>
                        <ColumnReference Column=”ConstExpr1005″>
                          <ScalarOperator>
                            <Intrinsic FunctionName=”rand”>
                              <ScalarOperator>
                                <Const ConstValue=”" />
                              </ScalarOperator>
                            </Intrinsic>
                          </ScalarOperator>
                        </ColumnReference>
                      </Identifier>
                    </ScalarOperator>

Now, those of you paying close attention will notice that the _name_ of the column is “ConstExprXXXX” instead of “ExprXXXX”.

I don’t think that I’d rely on the name meaning much, and I don’t even recall if there are other things that use ConstExprXXXX names.  It might be a good indicator that you are getting warmer if you are looking for which functions are executed once at the start of the query.

If you use the query patterns that I posted and that others posted in comments, you can determine what is happening for each function you wish to investigate.

You can probably look through the list of public functions (or get fancy and run strings.exe on the sql server binary) to look for function names that you can evaluate to see if they are showing up as ConstExpr or non-ConstExpr.  One that comes to mind is getdate() – it’s non-deterministic and you likely don’t want the results to change during a long-running query.  I’ll leave you guys to go find any others – I don’t believe that there lots of these types of functions.

As a side note for all you readers out there, I won’t be talking about internal details of the system beyond what is published or what is discussed publicly.  So, you won’t be hearing from me about SQL Server’s secret plans for including bullhorns in every box of SQL Server or how the logging feature is really made from Area 51 technologies.  However, I will tell you guys the basics of database and QP design and show you the *external* places where you can look to see what information Microsoft makes available to you.  Most of the time I plan to point you to some experiment you can run that will tell you, with a little context, enough to answer whatever question you have.

Have a great weekend all – I’m back to painting a room in my house.

Conor Cunningham

Related Posts