 | |
IsDeterministic(), IsPrecise(), and WITH SCHEMABINDING
comp.databases.ms-sqlserver comp.databases.ms-sqlserver
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
IsDeterministic(), IsPrecise(), and WITH SCHEMABINDING -
01-05-2011
, 04:55 PM
#2
| |||
| |||
|
|
What gives? Why is WITH SCHEMABINDING required? |
Consider this simple example:
CREATE VIEW dbo.v1 WITH SCHEMABINDING
AS SELECT CAST(1 AS int) AS a;
go
CREATE FUNCTION dbo.f2()
RETURNS TABLE --WITH SCHEMABINDING
AS RETURN (SELECT a FROM dbo.v1) ;
go
SELECT * FROM dbo.f2();
SELECT * FROM dbo.f2();
go
SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.f2'), 'IsDeterministic')
SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.f2'), 'IsPrecise')
go
ALTER VIEW dbo.v1
AS SELECT CAST(CURRENT_TIMESTAMP AS float) AS a;
go
SELECT * FROM dbo.f2();
SELECT * FROM dbo.f2();
go
DROP FUNCTION dbo.f2;
DROP VIEW dbo.v1;
go
Without schemabinding, there is no link between the function and the
underlying view. When the view changes, the properties of the function
would not change, but they would no longer be correct.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
#3
| |||
| |||
|
|
On Wed, 05 Jan 2011 14:55:30 -0800, Gene Wirchenko <genew (AT) ocis (DOT) net wrote: (snip) What gives? Why is WITH SCHEMABINDING required? Consider this simple example: CREATE VIEW dbo.v1 WITH SCHEMABINDING AS SELECT CAST(1 AS int) AS a; go CREATE FUNCTION dbo.f2() RETURNS TABLE --WITH SCHEMABINDING AS RETURN (SELECT a FROM dbo.v1) ; go SELECT * FROM dbo.f2(); SELECT * FROM dbo.f2(); go SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.f2'), 'IsDeterministic') SELECT OBJECTPROPERTYEX(OBJECT_ID('dbo.f2'), 'IsPrecise') go ALTER VIEW dbo.v1 AS SELECT CAST(CURRENT_TIMESTAMP AS float) AS a; go SELECT * FROM dbo.f2(); SELECT * FROM dbo.f2(); go DROP FUNCTION dbo.f2; DROP VIEW dbo.v1; go Without schemabinding, there is no link between the function and the underlying view. When the view changes, the properties of the function would not change, but they would no longer be correct. |
I thought that the determination was if the input parameters are
the same and the database state is the same, then yes. To me,
changing the database state would mean that all bets are off.
create function dbo.contrived()
returns int
as return 1
The above function is supposedly not deterministic. It does not
touch any database! The example that got me going was an interest
calculation function that took rate, amount, from date, and to date.
It did not touch a database either.
Sincerely,
Gene Wirchenko
#4
| |||
| |||
|
|
I thought that the determination was if the input parameters are the same and the database state is the same, then yes. To me, changing the database state would mean that all bets are off. create function dbo.contrived() returns int as return 1 The above function is supposedly not deterministic. It does not touch any database! The example that got me going was an interest calculation function that took rate, amount, from date, and to date. It did not touch a database either. |
WITH SCHEMABINDING gives a required, but not sufficient condition, that
the function is deterministic. That is simply what Microsoft has decided.
They could maybe have designed it differently, but then they would have
had to run more complex analysis when the function is used, and when the
function is altered or dropped.
To the price that we users have to add WITH SCHEMABINDING to make our
function deterministic. A fairly small price, I would say.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
#5
| |||
| |||
|
|
Why not? |
The point I was trying to make in the previous example, is that in
SOME cases, a deterministic function can become non-determinstic as a
result of changed to another object. When the function f2 is made, it
uses a view that is precise and deterministic. At that point, the SQL
engine *could* decide to mark the function as deterministic and
precise as well. But after the ALTER VIEW, this is no longer true.
The development team had basically four choices:
1. Base IsDeterministic and IsPrecise on the situation when the
function is created and ignore later changes, so that a function that
is no longer precise can still be marked as precise - I don't know
about you, but I would report a bug if I encountered this behaviour.
2. After an ALTER VIEW, check each and every object that is directly
or indirectly dependant on the view and correct the IsDeterministic
and IsPrecise attrbiutes. Probably a large engineering investment.
3. Analyse the function, require it to be schema-bound if the
IsDeterministic and IsPrecise attributees depend on some other object
but don't require schemabound if it is independent.
4. Alsways require the function to be independent.
My speculation is that the team discarded options 1 and 2, then
weighed the engineering effort for option 3 versus the user impact of
option 4 and decided that option 3 was not the optimal way to spend
their engineering dollars.
If you disagree, you can of course always suggest a change in this
behaviour: http://connect.microsoft.com/SQLServer
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
#6
| |||
| |||
|
|
Gene Wirchenko (genew (AT) ocis (DOT) net) writes: I thought that the determination was if the input parameters are the same and the database state is the same, then yes. To me, changing the database state would mean that all bets are off. create function dbo.contrived() returns int as return 1 The above function is supposedly not deterministic. It does not touch any database! The example that got me going was an interest calculation function that took rate, amount, from date, and to date. It did not touch a database either. I guess it is a limitation or a simplification. WITH SCHEMABINDING gives a required, but not sufficient condition, that the function is deterministic. That is simply what Microsoft has decided. They could maybe have designed it differently, but then they would have had to run more complex analysis when the function is used, and when the function is altered or dropped. |
can not do a complete analysis: it has the feel of trying to solve the
halting problem. I hope that they err on the side of calling
something nondeterministic when it is rather than the reverse, but
this example of doing so does seem strange.
Quote:
|
To the price that we users have to add WITH SCHEMABINDING to make our function deterministic. A fairly small price, I would say. |
have this point. Fortunately, I found an answer on the Web as to what
(but not why). There are too many gotchas in this field, and the
sheer mass of them is a high price.
Sinerely,
Gene Wirchenko
#7
| |||
| |||
|
#8
| |||
| |||
|
|
I think this is another MS versus ANSI/ISO. In the SQL/PSM language one of the options in a CREATE FUNCTION is DETERMINISTIC. That means the optimizer can replace a call with the same arguments (parameters are the formal place holders; arguments are the actual values in a CALL) with a constant result. This is a handy thing since a good optimizer will look across the sessions invoking the function and turn it into a look-up table. A JOIN replaces re-computing math, temporal or string functions. Some food for thought on the matter. |
"DETERMINISTIC" and it's buddy "EXTERNAL ACTION" are really things that
the developer has to decide.
For example, if your function uses a random number to generate a file
which is later destroyed then, as far as the user is concerned the
function is both deterministic nor does it have external action.
It's all about the net-effect.
Anyway, requiring schema binding to have a deterministic routine makes
sense to me.
Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.