|
|
You're in the
sql server programming
group:Weird..
sql server programming:
check this out. create view vtest as select rand() as today drop function func1 create function func1 (@b varchar(10)) returns float as begin declare @a float select @a = today from vtest return(@a) end select col1, dbo.func1(col1) as col2 from ta group by col1 select distinct col1, dbo.func1(col1) as col2 from ta
Not true... SELECT OBJECTPROPERTY(OBJECT_ID('func1'), 'IsDeterministic')
[quoted text, click to view] "Omnibuzz" wrote:
> SQL Server assumes all functions to be deterministic.. is it true?? > > check this out. > > create view vtest > as > select rand() as today > > drop function func1 > create function func1 (@b varchar(10)) > returns float > as > begin > declare @a float > select @a = today from vtest > return(@a) > end > > select col1, dbo.func1(col1) as col2 from ta group by col1 > > select distinct col1, dbo.func1(col1) as col2 from ta >
Hi
See "Deterministic and Nondeterministic Functions" in Books Online. John [quoted text, click to view] "Omnibuzz" <Omnibuzz@discussions.microsoft.com> wrote in message news:345A103B-3913-4674-96F6-8AB60D528F60@microsoft.com... > SQL Server assumes all functions to be deterministic.. is it true?? > > check this out. > > create view vtest > as > select rand() as today > > drop function func1 > create function func1 (@b varchar(10)) > returns float > as > begin > declare @a float > select @a = today from vtest > return(@a) > end > > select col1, dbo.func1(col1) as col2 from ta group by col1 > > select distinct col1, dbo.func1(col1) as col2 from ta > > Any sense???
Hi KH and John,
Thanks for your replies. I can understand that it says the function is non-deterministic. But is it using the information in generating the query execution plan. And I know what deterministic and non-deterministic functions are. what I am trying to analyze here is the behaviour of the query engine. Looking at the query I posted, select col1, dbo.func1(col1) as col2 from ta group by col1 If SQL Server already knew the function was non-deterministic, then it should have thrown an error saying "not all columns are in the group by...blah..blah..".. But it runs without any problem and what more gives an erronous result.. or so I believe. check out the query execution plan of both the queries. where the sort/distinct operation is done. I find THAT wierd :) I hope I made sense and
A small change in the question.. I shouldn't have generalized and I apologise.
SQL server assumed the function I had written to be deterministic. [quoted text, click to view] "Omnibuzz" wrote:
> SQL Server assumes all functions to be deterministic.. is it true?? > > check this out. > > create view vtest > as > select rand() as today > > drop function func1 > create function func1 (@b varchar(10)) > returns float > as > begin > declare @a float > select @a = today from vtest > return(@a) > end > > select col1, dbo.func1(col1) as col2 from ta group by col1 > > select distinct col1, dbo.func1(col1) as col2 from ta >
Hi John,
I think you need to do better than that :). The create view failed because the view definition was wrong. try this CREATE View Utest AS select CURRENT_USER as usr Moreover, rand() function is non-deterministic whether you give a seed or not. check out the least significant digits in the result and it will change :) Now that we have resolved that issue. It puts us to square one.. why this
Hi
It does seem to be an anomaly RAND() is supposed to only be deterministic when you use a seed value, therefore you would not expect to be able to create your vtest view as it should be have like: CREATE View Utest AS select CURRENT_USER Msg 4511, Level 16, State 1, Procedure Utest, Line 2 Create View or Function failed because no column name was specified for column 1. Therefore it seems to have assumed that it will be deterministic and given the subsequent behaviour. John [quoted text, click to view] "Omnibuzz" <Omnibuzz@discussions.microsoft.com> wrote in message news:F0290645-C112-481F-8A7F-5750C1D417D5@microsoft.com... > Hi KH and John, > Thanks for your replies. I can understand that it says the function is > non-deterministic. But is it using the information in generating the query > execution plan. > And I know what deterministic and non-deterministic functions are. what I > am > trying to analyze here is the behaviour of the query engine. > > Looking at the query I posted, > > select col1, dbo.func1(col1) as col2 from ta group by col1 > > If SQL Server already knew the function was non-deterministic, then it > should have thrown an error saying "not all columns are in the group > by...blah..blah..".. > > But it runs without any problem and what more gives an erronous result.. > or > so I believe. > > check out the query execution plan of both the queries. where the > sort/distinct operation is done. I find THAT wierd :) I hope I made sense > and > didn't offend you guys in the process.
[quoted text, click to view] "Omnibuzz" <Omnibuzz@discussions.microsoft.com> wrote in message Guess I should have paid more attention to the message!news:B1D4C174-7960-48A1-A252-1EFDDA79702B@microsoft.com... > Hi John, > I think you need to do better than that :). The create view failed > because the view definition was wrong. > try this > > CREATE View Utest > > AS select CURRENT_USER as usr [quoted text, click to view] > This is not what is documented in Book Online. Your function and view would > Moreover, rand() function is non-deterministic whether you give a seed or > not. check out the least significant digits in the result and it will > change > :) also need to be SCHEMA_BINDING to make it deterministic but even adding them does not change that. [quoted text, click to view] > > Now that we have resolved that issue. It puts us to square one.. why this > behavior?? I am not sure why this is a problem, it is unforseen behaviour why you may want to log a support call on. John
Looking at SHOWPLAN_TEXT
If RAND is used through a function |--Compute Scalar(DEFINE:([Expr1004]=[testdb].[dbo].[func1](CONVERT_IMPLICIT(varchar(10),[testdb].[dbo].[ta].[col1],0)))) |--Constant Scan(VALUES:((rand()))) If RAND is used directly |--Compute Scalar(DEFINE:([ConstExpr1005]=rand())) Therefore it seems that the criteria is a scalar function rather than a deterministic one, but using RAND directly returns the same value for each row. John [quoted text, click to view] "John Bell" <jbellnewsposts@hotmail.com> wrote in message news:e70gO5kYGHA.1348@TK2MSFTNGP05.phx.gbl... > > "Omnibuzz" <Omnibuzz@discussions.microsoft.com> wrote in message > news:B1D4C174-7960-48A1-A252-1EFDDA79702B@microsoft.com... >> Hi John, >> I think you need to do better than that :). The create view failed >> because the view definition was wrong. >> try this >> >> CREATE View Utest >> >> AS select CURRENT_USER as usr > Guess I should have paid more attention to the message! > >> >> Moreover, rand() function is non-deterministic whether you give a seed or >> not. check out the least significant digits in the result and it will >> change >> :) > This is not what is documented in Book Online. Your function and view > would also need to be SCHEMA_BINDING to make it deterministic but even > adding them does not change that. > >> >> Now that we have resolved that issue. It puts us to square one.. why this >> behavior?? > > I am not sure why this is a problem, it is unforseen behaviour why you may > want to log a support call on. > > John >
Omnibuzz (Omnibuzz@discussions.microsoft.com) writes:
[quoted text, click to view] > I think you need to do better than that :). The create view failed > because the view definition was wrong. > try this > > CREATE View Utest > > AS select CURRENT_USER as usr > > Moreover, rand() function is non-deterministic whether you give a seed > or not. check out the least significant digits in the result and it will > change >:) > > Now that we have resolved that issue. It puts us to square one.. why this > behavior?? I'm not sure that I see the problem. Try to use the function in an indexed view or an indexed computed column, and see whether SQL Server things its deterministic. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |