THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

When a Function is indeed a Constant

In my last blog post:

I mentioned that I ran across a situation in which GETDATE() used in a SELECT statement occasionally returned more than 1 value for the result set. That sparked quite a debate amongst SQL Server programmers who belonged to one of two camps in how they believed this really should behave.  For instance if you run the following SQL statement:

           SELECT GETDATE() AS [The Datetime] FROM sys.sysobjects

Should you expect all the rows to have the same DateTime value or would you expect a different value for each row? Now in this simple example it would be easy to say that the query was so fast that there was not enough elapsed time between rows to give a different DateTime value and you could very well be correct. But this next example calls a UDF that introduces some lag as a result of the WHILE loop and returns GETDATE(). If you run that example you will definately see differences in the return from the function. But what about the plain GETDATE()?  Will it be the same value for all rows or similar to the UDF output? After all they are both spitting out GETDATE() right?  Well try it and see.

USE tempdb
CREATE FUNCTION dbo.test$wait()

       DECLARE @I INT SET @I = 1
       WHILE @I < 10000
           SET @I = @I + 1
SELECT GETDATE() AS [Getdate],dbo.test$wait() AS [UDF]
    FROM master.sys.sysobjects

As I am sure most of you will see it turns out that GETDATE() or any other non-deterministic runtime constant scalar function will indeed act as a constant for the life of the query execution. What does that mean exactly?  Well in a nutshell it means these functions will be evaluated once at the beginning of the query execution and that value will be used for all resulting rows.  You will always get the same value and if you don't you have a bug. This behavior was confirmed today by one of the SQL Server developers who maintains this code and guarantees this behavior.

While this may not be news to many of you (I was always under the impression this was the intended behavior) but it is to many others. There has been a lot of code written over the years that expects functions like this to return a potentially different value for each row in a single Select statement. That assumption is wrong and I wanted to get the word out there in order to minimize any future coding errors in this regard. As you can see in my original blog post that there is at least 1 older revision of SQL Server in which this was not the case, but don't let that fool you into thinking that is the intended behavior. Keep this behavior in mind as you code along in the future.

Published Saturday, March 1, 2008 12:19 PM by Andrew Kelly

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Andrew Kelly : When GETDATE() is not a constant said:

March 1, 2008 11:25 AM

EdVassie said:

I found that Getdate() returned multiple values in a INSERT .. SELECT statement for a number of fix levels in SQL Server 2000.  As I needed a consistant time on all rows that were inserted, I just did SET @var = Getdate() before the INSERT, and used @var to provide the required time in the main query.

March 3, 2008 4:39 AM

jerryhung said:

I never thought about this issue until your last blog post

Luckily, my recent insert of 300,000+ records all share the exact same getdate() value, EVERY TIME (RevisionDate = getdate() inside a transaction)

However, I have changed my insert code to use a variable instead, to make sure I don't get surprises.

March 3, 2008 3:28 PM said:

How To Use MYSQL's "limit" function in Microsoft Sql server?

for expmple:

a database have 1,000,000 record,

I want to read record at order 1000 - 1100.

in MySQL:  

"Select * from Table1 limit 1000,100" ;

I serach some blog and technic webpage how to read at mssql,

in MsSQL:

"Select * from (Select top 1100 * form Table1 order by DESC) as T1 order by ASC"

Bnt this expression is too slowly!!! Question: how to optimize? thanks.

March 24, 2008 3:35 AM

JRStern said:

Yup.  Just ran into the non-consistent getdate() today my own self, on a (now) retro version of SQL 2005 32-bit:

Microsoft SQL Server 2005 - 9.00.3282.00 (Intel X86)

Aug  5 2008 01:01:05

Copyright (c) 1988-2005 Microsoft Corporation

Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Thanks for confirminng I'm not crazy - or at least keeping me company!

April 28, 2010 9:31 PM

Paul White: Page Free Space said:

The humble Compute Scalar is one of the least well-understood of the execution plan operators, and usually

September 4, 2012 6:22 PM

loemrntdherid1 said:

May 20, 2018 8:21 PM

Vapor Max said: Women'S Nike Air Max Womens Nike Air Max Womens 2017 Nike Shoes Vapors Vapormax Pas Cher Vapormax Nike Vapormax Flyknit Vapormax 2018 Vapormax Vapor Max Nike Vapor Max Vapor Nikes Shoes Nikes Nike Vapour Max Nike Vapormax Triple Black Nike Vapormax Price Nike Vapormax Flyknit Nike Vapormax Nike Vapor Max Shoes Nike Vapor Max Nike Vapor Fly 4 Nike Vapor Air Vapormax Air Vapor Max Air Nike Air Maxvapor Air Max Zero Air Max Vapormax Air Max Vapor Max Air Max Vapor Nikes Nike Vapour Max Nike Vapormax Triple Black Nike Vapormax Price Nike Vapormax Flyknit Nike Vapormax Nike Vapor Max Shoes Nike Vapor Max Nike Vapor Fly 4 Nike Vapor Women'S Nike Air Max Womens Nike Air Max Womens 2017 Nike Shoes Vapors Vapormax Pas Cher Vapormax Nike Vapormax Flyknit Vapormax 2018 Vapormax Vapor Max Nike Vapor Max Vapor 2018 Vapor Max Yeezys Shoes Yeezy V2 Yeezy Shoes Zebra Yeezy Yeezys Boost 350 V2 New Yeezy V2 New Yeezy Yeezys Boost 350 V2 Yeezys Boost 350 Yeezy Boost 350 V2 Outlet Yeezy Zebra New Yeezys New Yeezy V2 New Yeezy Uk Moncler Coat Uk Moncler Moncler Uk Online Moncler Uk Moncler Uk Outlet Moncler Uk Moncler Jacket Outlets Moncler Jacket Moncler Uk Jackets Moncler Jackets Goose Down Jackets Canada Goose Uk Nfl Jerseys Nfl Jersey Cheap Nfl Jersey Nfl Jersey Cheap Nfl Jersey Nfl Cheap Jerseys Cheap Nfl Jerseys Cheap Nfl Jersey Nhl Jerseys 2018 Nfl Jerseys For Sale Nfl Jerseys Cheap Nba Jerseys Cheap Nba Jerseys 2018 Nba Jerseys Cheap Jerseys Cheap Jersey 2018 Us Cheap China Jerseys 2018 Worldcup 2018 World Cup Soccer 2018 World Cup Russia 2018 World Cup Russia World Cup Football 2018 World Cup Football World Cup 2018 Warriors Jersey Nba Warriors Golden State Warriors Michael Kors Factory Sale Michael Kors bags Michael Kors Jordan Air Cheap Jordan Shoes Air Jordan Shoes Air Jordan Asics Shoes Asics Yeezy Zebra Yeezy Yellow Yeezy V2 Boost Yeezy V2 Cheap Fitflop Fitflop Clearance Fitflop Sale Clearance Discount Fitflop Fitflop Outlet Fitflop Clearance Outlet Clearance Fitflop Yeezy 700 Yeezy 500 Yeezy 350 V2 Yeezy Yellow Yeezy V2 Boost Yeezy V2 Yeezy Uk Yeezy Cream Yeezy Boost V2 Zebra Yeezys Yeezy V2 Boost Yeezy V2 Yeezy 350 V2 Yeezy 2018 Cheap Pandora Charms Pandora Charms Sale Clearance Pandora Charms Wholesale Pandora Jewellery Pandora Wholesale Pandora Jewellery Wholesale Pandora Jewelry Pandora Outlet Pandora Charms Outlet Pandora Rings Pandora Bracelets Canada Goose Canada Goose UK Down Jackets For Women Canada Goose UK Jacket Canada Goose Jacket UK Canada Goose Jacket

July 10, 2018 3:02 AM

Yeezy Supply said: Jordan 11 Win Like 82 Nike Outlet Store Nike Outlet Store Yeezy Yeezy Moncler UK Yeezy Boost 350 Yeezy Boost 350 V2 Birkenstock Oakley Outlet Nike Outlet Online"> nike clearance Off White Jordan 1 Nike Air Max Yeezy Boost 350 Air Max 97 jordan 12 bordeaux Yeezys Nike Shoes Nike Outlet Air Max 2017 Moncler Jackets Yeezy Boost 350 V2 Jordan 12 Win Like 82 Jordan 11 Win Like 82 jordan 4 Nike Clearance Moncler UK Yeezy Nike Outlet Store Online Shoping Birkenstock Shoes jordan 12 ovo Nike Air Max 2017

July 21, 2018 6:47 PM

dongdong8 said:



July 23, 2018 11:44 PM

yaoxuemei said:


August 15, 2018 2:15 AM

qqq said:


August 16, 2018 12:31 AM

chenjinyan said:


August 22, 2018 11:14 PM

shenyuhang said:


August 23, 2018 10:29 PM

chenlixiang said:



September 18, 2018 10:00 PM

wwwe said:


<H1><a href="" title="yeezy boost 350 v2"><strong>yeezy boost 350 v2</strong></a></H1>

<H1><a href="" title="westbrook shoes"><strong>westbrook shoes</strong></a></H1>

<H1><a href="" title="stephen curry shoes"><strong>stephen curry shoes</strong></a></H1>

<H1><a href=""">" title="louboutin shoes"><strong>louboutin shoes</strong></a></H1>

<H1><a href="" title="patriots jerseys"><strong>patriots jerseys</strong></a></H1>

<H1><a href="" title="kobe byrant shoes"><strong>kobe byrant shoes</strong></a></H1>


<H1><a href=""><strong>nike air force 1</strong></a></H1>

<H1><a href=""><strong>michael kors handbags</strong></a></H1>

<H1><a href=""><strong>cartier bracelet</strong></a></H1>

<H1><a href=""><strong>off white clothing</strong></a></H1>


[url=][b]louboutin shoes[/b][/url]

[url=][b]nike hyperdunk 2017[/b][/url]

[url=][b]nike basketball shoes[/b][/url]

[url=][b]adidas pure boost[/b][/url]


<a href=""><strong>adidas superstar</strong></a>

<a href=""><strong>nike lebron 11</strong></a>

<a href=""><strong>nike air max 90</strong></a>

<a href=""><strong>adidas nmd</strong></a>


October 5, 2018 12:35 AM

kakakaoo said:

October 8, 2018 2:09 AM

chenjinyan said:


October 9, 2018 6:41 PM

chenqiuying said:


October 10, 2018 6:20 PM

kakakaoo said:


November 8, 2018 1:53 AM said:"> moncler men jackets,"> golden goose francy,"> moncler hats,"> air jordan 8,"> golden goose superstar,"> yeezy boost 500,"> golden goose starter,"> golden goose starter,"> moncler men vests,"> golden goose may,"> nike kyrie 3,"> golden goose v star,"> moncler kids jackets,"> moncler scarf,"> nike kyrie 4,"> golden goose francy,"> golden goose ball star,"> golden goose man,"> moncler women vests,"> air jordan 6, golden goose,"> air jordan 6,"> air jordan 10,"> adidas yeezy boost 350, golden goose starter,"> golden goose superstar, golden goose may,"> moncler kids coats,"> ugg fingerless glove,"> moncler women coats,"> air jordan 1,"> golden goose hi star, lebron 16, kyrie 4,"> air jordan 5,"> nike air yeezy,"> valentino pumps,"> moncler men coats, golden goose slide,"> valentino ballerina flats,"> golden goose v star, golden goose francy,"> yeezy boost 350 v2, nike lebron 15,"> golden goose super star,"> golden goose francy, ugg,"> golden goose mid star,"> golden goose mid star, golden goose,"> golden goose superstar, balenciaga,"> moncler accessories sale,"> moncler men jackets,"> golden goose may,"> yeezy boost 350,"> air jordan 13,"> golden goose superstar,"> air jordan 12,"> air jordan 9,"> golden goose starter,"> valentino sandals,"> golden goose slide,"> air jordan 4,"> nike air yeezy,"> air jordan 10,"> air jordan 3, golden goose, golden goose francy, golden goose, golden goose woman,"> air jordan 12,"> moncler women vests,"> air jordan 8, golden goose 2.12, golden goose francy, golden goose v star,"> moncler women jackets, golden goose v star, balenciaga city bags,"> valentino sneakers, balenciaga shoulder bags, golden goose slide,"> moncler hats,"> yeezy boost infant,"> gs air jordans, ugg earmuff, golden goose ball star, golden goose v star,"> nike air foamposite,"> air jordan 11, jordan 12 bulls, golden goose mid star, moncler,"> air jordan 11, golden goose mid star, nike air foamposite, nike lebron 16, golden goose slide, adidas yeezy boost 350 infant, golden goose slide,"> moncler women jackets, moncler, ugg cardy glove,"> moncler kids, air jordan 13,"> moncler men vests,"> yeezy boost 700,"> air jordan 9,"> valentino slides, golden goose, lebron james jerseys, golden goose ball star, balenciaga tote bags, golden goose ball star,"> air jordan 5, golden goose mid star, air jordan 3, adidas yeezy boost 350 v2,"> moncler men coats, moncler kids vests, yeezy boost, air jordan xxxi, air jordan 12 bulls, balenciaga caps, air jordan 4, nike lebron 15, air jordan gs, golden goose ball star,"> moncler women coats, golden goose slide, ugg turn cuff glove, moncler scarves hats, golden goose hi star, valentino, golden goose mid star, golden goose superstar, golden goose kids, moncler, golden goose v star, balenciaga shoes, golden goose starter

January 15, 2019 6:28 AM

Leave a Comment


This Blog


Privacy Statement