THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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:

 http://sqlblog.com/blogs/andrew_kelly/archive/2008/02/27/when-getdate-is-not-a-constant.aspx

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
go
CREATE FUNCTION dbo.test$wait()

RETURNS DATETIME
AS
   BEGIN
       DECLARE @I INT SET @I = 1
       WHILE @I < 10000
         BEGIN
           SET @I = @I + 1
         END
       RETURN (GETDATE())
   END
GO
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

Comments

 

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
 

Seraph2047@hotmail.com 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:

http://www.vapor-max.com/ Women'S Nike Air Max

http://www.vapor-max.com/ Womens Nike Air Max

http://www.vapor-max.com/ Womens 2017 Nike Shoes

http://www.vapor-max.com/ Vapors

http://www.vapor-max.com/ Vapormax Pas Cher

http://www.vapor-max.com/ Vapormax Nike

http://www.vapor-max.com/ Vapormax Flyknit

http://www.vapor-max.com/ Vapormax 2018

http://www.vapor-max.com/ Vapormax

http://www.vapor-max.com/ Vapor Max Nike

http://www.vapor-max.com/ Vapor Max

http://www.vapor-max.com/ Vapor

http://www.vapor-max.com/ Nikes Shoes

http://www.vapor-max.com/ Nikes

http://www.vapor-max.com/ Nike Vapour Max

http://www.vapor-max.com/ Nike Vapormax Triple Black

http://www.vapor-max.com/ Nike Vapormax Price

http://www.vapor-max.com/ Nike Vapormax Flyknit

http://www.vapor-max.com/ Nike Vapormax

http://www.vapor-max.com/ Nike Vapor Max Shoes

http://www.vapor-max.com/ Nike Vapor Max

http://www.vapor-max.com/ Nike Vapor Fly 4

http://www.vapor-max.com/ Nike Vapor

http://www.vapor-max.com/ Air Vapormax

http://www.vapor-max.com/ Air Vapor Max

http://www.vapor-max.com/ Air Nike

http://www.vapor-max.com/ Air Maxvapor

http://www.vapor-max.com/ Air Max Zero

http://www.vapor-max.com/ Air Max Vapormax

http://www.vapor-max.com/ Air Max Vapor Max

http://www.vapor-max.com/ Air Max Vapor

http://www.nikeairvapormax.top/ Nikes

http://www.nikeairvapormax.top/ Nike Vapour Max

http://www.nikeairvapormax.top/ Nike Vapormax Triple Black

http://www.nikeairvapormax.top/ Nike Vapormax Price

http://www.nikeairvapormax.top/ Nike Vapormax Flyknit

http://www.nikeairvapormax.top/ Nike Vapormax

http://www.nikeairvapormax.top/ Nike Vapor Max Shoes

http://www.nikeairvapormax.top/ Nike Vapor Max

http://www.nikeairvapormax.top/ Nike Vapor Fly 4

http://www.nikeairvapormax.top/ Nike Vapor

http://www.vapormax2018.org/ Women'S Nike Air Max

http://www.vapormax2018.org/ Womens Nike Air Max

http://www.vapormax2018.org/ Womens 2017 Nike Shoes

http://www.vapormax2018.org/ Vapors

http://www.vapormax2018.org/ Vapormax Pas Cher

http://www.vapormax2018.org/ Vapormax Nike

http://www.vapormax2018.org/ Vapormax Flyknit

http://www.vapormax2018.org/ Vapormax 2018

http://www.vapormax2018.org/ Vapormax

http://www.vapormax2018.org/ Vapor Max Nike

http://www.vapormax2018.org/ Vapor Max

http://www.vapormax2018.org/ Vapor

http://www.vapormax2018.org/ 2018 Vapor Max

http://www.yeezyshoes.top/ Yeezys Shoes

http://www.yeezyshoes.top/ Yeezy V2  

http://www.yeezyshoes.top/ Yeezy Shoes  

http://www.newyeezyv2.com/ Zebra Yeezy

http://www.newyeezyv2.com/ Yeezys Boost 350 V2

http://www.newyeezyv2.com/ New Yeezy V2

http://www.newyeezyv2.com/ New Yeezy  

http://www.yeezyboost350v2outlet.com/ Yeezys Boost 350 V2

http://www.yeezyboost350v2outlet.com/ Yeezys Boost 350

http://www.yeezyboost350v2outlet.com/ Yeezy Boost 350 V2 Outlet  

http://www.newyeezyv2.org/ Yeezy Zebra

http://www.newyeezyv2.org/ New Yeezys

http://www.newyeezyv2.org/ New Yeezy V2

http://www.newyeezyv2.org/ New Yeezy  

http://www.ukmonclercoat.co.uk/ Uk Moncler Coat

http://www.ukmonclercoat.co.uk/ Uk Moncler

http://www.monclerukonline.co.uk/ Moncler Uk Online

http://www.monclerukonline.co.uk/ Moncler Uk

http://www.moncler-ukoutlet.co.uk/ Moncler Uk Outlet

http://www.moncler-ukoutlet.co.uk/ Moncler Uk

http://www.monclerjacketoutlets.co.uk/ Moncler Jacket Outlets

http://www.monclerjacketoutlets.co.uk/ Moncler Jacket

http://www.monclerukjackets.co.uk/ Moncler Uk Jackets

http://www.monclerukjackets.co.uk/ Moncler Jackets

http://www.canadagooseuk.org/ Goose Down Jackets

http://www.canadagooseuk.org/ Canada Goose Uk

http://www.usnfljerseys.us/ Nfl Jerseys

http://www.usnfljerseys.us/ Nfl Jersey Cheap

http://www.usnfljerseys.us/ Nfl Jersey

http://www.usnfljerseys.org/ Nfl Jersey Cheap

http://www.usnfljerseys.org/ Nfl Jersey

http://www.usnfljerseys.org/ Nfl Cheap Jerseys

http://www.uscheapnfljersey.com/ Cheap Nfl Jerseys

http://www.uscheapnfljersey.com/ Cheap Nfl Jersey

http://www.nfljerseyscheapsales.com/ Nhl Jerseys 2018

http://www.nfljerseyscheapsales.com/ Nfl Jerseys For Sale

http://www.nfljerseyscheapsales.com/ Nfl Jerseys Cheap

http://www.cheapnba.top/ Nba Jerseys Cheap

http://www.cheapnba.top/ Nba Jerseys 2018

http://www.cheapnba.top/ Nba Jerseys

http://www.uscheapjerseys.us.com/ Cheap Jerseys

http://www.uscheapjerseys.us.com/ Cheap Jersey 2018 Us

http://www.uscheapjerseys.us.com/ Cheap China Jerseys

http://russia2018jersers.com/ 2018

http://russia2018jersers.com/ Worldcup 2018

http://russia2018jersers.com/ World Cup Soccer 2018

http://russia2018jersers.com/ World Cup Russia 2018

http://www.footballjerseycheap.com/ World Cup Russia

http://www.footballjerseycheap.com/ World Cup Football 2018

http://www.footballjerseycheap.com/ World Cup Football

http://www.footballjerseycheap.com/ World Cup 2018

http://www.warriors-gs.com/ Warriors Jersey

http://www.warriors-gs.com/ Nba Warriors

http://www.warriors-gs.com/ Golden State Warriors

http://www.michael-kors-bags.us/ Michael Kors Factory Sale

http://www.michael-kors-bags.us/ Michael Kors bags

http://www.michael-kors-bags.us/ Michael Kors

http://www.jordan-air.us/ Jordan Air

http://www.jordan-air.us/ Cheap Jordan Shoes

http://www.jordan-air.us/ Air Jordan Shoes

http://www.jordan-air.us/ Air Jordan

http://www.asics.us.com/ Asics Shoes

http://www.asics.us.com/ Asics

http://www.yeezyv2sales.org/ Yeezy Zebra

http://www.yeezyv2sales.org/ Yeezy Yellow  

http://www.yeezyv2sales.org/ Yeezy V2 Boost

http://www.yeezyv2sales.org/ Yeezy V2  

http://www.clearancefitflop.com/ Cheap Fitflop

http://www.clearancefitflop.com/ Fitflop Clearance

http://www.clearancefitflop.com/ Fitflop Sale Clearance

http://www.clearancefitflop.com/ Discount Fitflop

http://www.clearancefitflop.com/ Fitflop Outlet

http://www.clearancefitflop.com/ Fitflop Clearance Outlet

http://www.clearancefitflop.com/ Clearance Fitflop

http://www.yeezy500.org/ Yeezy 700

http://www.yeezy500.org/ Yeezy 500

http://www.yeezy500.org/ Yeezy 350 V2

http://www.yeezysv2.co.uk/ Yeezy Yellow

http://www.yeezysv2.co.uk/ Yeezy V2 Boost

http://www.yeezysv2.co.uk/ Yeezy V2

http://www.yeezysv2.co.uk/ Yeezy Uk

http://www.yeezysv2.co.uk/ Yeezy Cream

http://www.yeezysv2.co.uk/ Yeezy Boost V2

http://www.yeezyv2.top/ Zebra Yeezys

http://www.yeezyv2.top/ Yeezy V2 Boost

http://www.yeezyv2.top/ Yeezy V2

http://www.yeezyv2.top/ Yeezy 350 V2

http://www.yeezyv2.top/ Yeezy 2018

http://www.pandorajewellerywholesale.co.uk/ Cheap Pandora Charms

http://www.pandorajewellerywholesale.co.uk/ Pandora Charms Sale Clearance

http://www.pandorajewellerywholesale.co.uk/ Pandora Charms

http://www.pandorajewellerywholesale.co.uk/ Wholesale Pandora Jewellery

http://www.pandorajewellerywholesale.co.uk/ Pandora Wholesale

http://www.pandorajewellerywholesale.co.uk/ Pandora Jewellery Wholesale

http://www.pandorajewellerywholesale.co.uk/ Pandora Jewelry

http://www.pandorajewellerywholesale.co.uk/ Pandora Outlet

http://www.pandorajewellerywholesale.co.uk/ Pandora Charms Outlet

http://www.pandorajewellerywholesale.co.uk/ Pandora Rings

http://www.pandorajewellerywholesale.co.uk/ Pandora Bracelets

http://www.canadagoosejacketuk.co.uk/ Canada Goose

http://www.canadagoosejacketuk.co.uk/ Canada Goose UK

http://www.canadagoosejacketuk.co.uk/ Down Jackets For Women

http://www.canadagoosejacketuk.co.uk/ Canada Goose UK Jacket

http://www.canadagoosejacketuk.co.uk/ Canada Goose Jacket UK

http://www.canadagoosejacketuk.co.uk/ Canada Goose Jacket

July 10, 2018 3:02 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement