September 13, 2009 at 5:30 pm
Matt Whitfield (9/13/2009)
The second link didn't seem to contain any reference to it?
You seem to be talking about a different 'it'. I am referring to the many places in BOL where it states that functions (among other objects) have dependencies just like views do. Why should a non-schema-bound function be any different?
Matt Whitfield (9/13/2009)
I still wouldn't necessarily put the 'bug' to bed that easily though. If your bank 'clearly documented' the fact that if your bank balance ended with a 3 on the third friday of every month then they would zero your account, that wouldn't necessarily make it correct, would it? While an extreme example, it's a very non-obvious behaviour. If I was selling that software, and a user came to me with a bug, i'd feel pretty tight explaining it away as 'documented behaviour'.
Beware the false analogy eh?
The product behaves as the documentation for it describes. That's not often a distinguishing feature of a bug 🙂
You seem to dislike the behaviour, but that does not make it a bug.
I am intrigued to know how you would wish it to work. Should every function and view be schema-bound? How would that work?
If you figure it out, submit a suggestion for it on Connect!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 13, 2009 at 5:43 pm
Paul White (9/13/2009)
You seem to be talking about a different 'it'. I am referring to the many places in BOL where it states that functions (among other objects) have dependencies just like views do. Why should a non-schema-bound function be any different?
Oh. Well I (and, I believe, the entire article / thread) was talking about the fact that object meta data is not refreshed when an underlying object is changed.
Paul White (9/13/2009)
Beware the false analogy eh?
The analogy is about documenting something which is not how it should be in the hope that it will become accepted.
Paul White (9/13/2009)
The product behaves as the documentation for it describes. That's not often a distinguishing feature of a bug 🙂
First sentence from wikipedia on 'software bug' :-
'A software bug is the common term used to describe an error, flaw, mistake, failure, or fault in a computer program or system that produces an incorrect or unexpected result, or causes it to behave in unintended ways.'
I would put this firmly in the category of 'unexpected result'.
Paul White (9/13/2009)
You seem to dislike the behaviour, but that does not make it a bug.I am intrigued to know how you would wish it to work. Should every function and view be schema-bound? How would that work?
If you figure it out, submit a suggestion for it on Connect!
I would like it to work in the same way that sys.sql_expression_dependencies works better than sys.sql_dependencies. 🙂
If a multiple column reference is detected between callee and caller then refresh the schema for that automatically. I don't think it would take a massive amount of server time to check for that condition on object alterations. In fact, you could probably even write a DDL trigger to do it....
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 13, 2009 at 6:06 pm
Matt Whitfield (9/13/2009)
Oh. Well I (and, I believe, the entire article / thread) was talking about the fact that object meta data is not refreshed when an underlying object is changed.
And I am pointing out that this is exactly the documented behaviour. It's just doing what it says on the tin. SCHEMABINDING was brought in to help address this issue. If not schema-bound, you can get unexpected behaviour - by definition.
Matt Whitfield (9/13/2009)
The analogy is about documenting something which is not how it should be in the hope that it will become accepted.
No your analogy was false, and about banks.
Matt Whitfield (9/13/2009)
First sentence from wikipedia on 'software bug' :-'A software bug is the common term used to describe an error, flaw, mistake, failure, or fault in a computer program or system that produces an incorrect or unexpected result, or causes it to behave in unintended ways.'
I would put this firmly in the category of 'unexpected result'.
It is not at all unexpected. See the documentation. And good luck arguing the point with Microsoft 😀
Matt Whitfield (9/13/2009)
I would like it to work in the same way that sys.sql_expression_dependencies works better than sys.sql_dependencies. 🙂 If a multiple column reference is detected between callee and caller then refresh the schema for that automatically. I don't think it would take a massive amount of server time to check for that condition on object alterations. In fact, you could probably even write a DDL trigger to do it....
Indeed. Do you foresee any problems with backwards compatibility? How would you handle errors during the metadata refresh? Would you drop the object or just not refresh it? And so on.
Anyway, it seems you want to change the behaviour of a SQL Server feature. One mechanism for this is Connect.
If it is popular, your item will attract many validations and votes, and may be considered for a future release.
I will continue to breathe normally in the meantime.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 13, 2009 at 6:28 pm
Paul White (9/13/2009)
No your analogy was false, and about banks.
Would you like it more if it was about exploding kettles? 🙂 Just because you don't like my analogy, doesn't make it false.
Paul White (9/13/2009)
It is not at all unexpected.
If that were the case, then this entire thread/article would not be in existence.
Paul White (9/13/2009)
Do you foresee any problems with backwards compatibility?
Yes, the same problems that you get from using select * in the first place. 🙂
Paul White (9/13/2009)
How would you handle errors during the metadata refresh?
The same way as any other trigger failure - by causing the statement to roll back.
Paul White (9/13/2009)
Anyway, it seems you want to change the behaviour of a SQL Server feature.
It's a 'feature' now??? LOL 🙂
Paul White (9/13/2009)
One mechanism for this is Connect. If it is popular, your item will attract many validations and votes, and may be considered for a future release.
Or, I could post a DDL trigger here which people could use should they wish to:
ALTER TRIGGER trig_sort_out_metadata
ON DATABASE
FOR ALTER_TABLE, ALTER_FUNCTION, ALTER_VIEW
AS
DECLARE @command [nvarchar](MAX)
DECLARE @object [nvarchar](MAX)
SELECT @object = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(max)') + '.' + EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)')
SET @command = '';
WITH ObjectTree
AS
(
SELECT [referenced_id],
[referencing_id],
[referencing_id] AS [base_referencing_id],
0 AS [level]
FROM [sys].[sql_expression_dependencies]
UNION ALL
SELECT [sql_expression_dependencies].[referenced_id],
[sql_expression_dependencies].[referencing_id],
[ObjectTree].[base_referencing_id],
[ObjectTree].[level] + 1 AS [level]
FROM [sys].[sql_expression_dependencies] INNER JOIN
[ObjectTree]
ON [ObjectTree].[referenced_id] = [sys].[sql_expression_dependencies].[referencing_id]
)
SELECT @command = @command + 'EXEC sp_refreshsqlmodule ''' + OBJECT_NAME([base_referencing_id]) + ''';' + char(13) + CHAR(10) FROM
(SELECT [base_referencing_id], [level] FROM [ObjectTree] WHERE [referenced_id] = OBJECT_ID(@object) GROUP BY [base_referencing_id], [level]) idat
ORDER BY [level]
EXEC (@command)
Paul White (9/13/2009)
I will continue to breathe normally in the meantime.
If you look anything like your avatar - i'd be worried as to normal!! 😀 No seriously though, I don't really understand why you seem to be talking at me as if i'm a schoolboy who's just discovered their first pube. I just have a different opinion to you - that doesn't mean that I deserve to be talked down at.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 13, 2009 at 6:32 pm
Paul White (9/13/2009)
It is clearly documented behaviour.SCHEMABINDING is the way to avoid it.
See sp_refreshsqlmodule.
Paul
OK, so it is a documented bug. Documenting a bug and providing a workaround doesn't mean it isn't a bug.
When I write 'Select *' (and yes, I do sometimes), my intention, in particular when this ends up in production code, is to select all columns; and I mean ALL columns, not just the ones that happened to be there when the query was written. I don't know of any good alternative for that unless you query system views or tables to produce dynamic SQL and then execute that (but I would not call that a better alternative).
Accepting this behaviour as a feature is like accepting that a view gives you last week's data, rather than current data, because it was written last week.
September 13, 2009 at 6:47 pm
Matt Whitfield (9/13/2009)
Would you like it more if it was about exploding kettles? 🙂 Just because you don't like my analogy, doesn't make it false.
You likened it to zeroing an account balance if it happened to end in a three!!
Matt Whitfield (9/13/2009)
If that were the case, then this entire thread/article would not be in existence.
It's not unexpected if you have read the documentation, or know the subject well.
Matt Whitfield (9/13/2009)
Yes, the same problems that you get from using select * in the first place. 🙂
So the bug is in using SELECT *...is that your point now?
Matt Whitfield (9/13/2009)
The same way as any other trigger failure - by causing the statement to roll back.
So you would advocate a 'best effort' auto-refresh? Seems a bit ad-hoc to me.
Matt Whitfield (9/13/2009)
It's a 'feature' now??? LOL 🙂
Certainly it is. A documented one. Just like with views.
Matt Whitfield (9/13/2009)
Or, I could post a DDL trigger here which people could use should they wish to:
You could, yes. That would appear in the 'Workarounds' section. Along with schemabinding and avoiding the star construct in the first place.
Matt Whitfield (9/13/2009)
I don't really understand why you seem to be talking at me as if i'm a schoolboy who's just discovered their first pube. I just have a different opinion to you - that doesn't mean that I deserve to be talked down at.
It is regrettable that you feel that way.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 13, 2009 at 6:50 pm
SCHEMABINDING was brought in to help address this issue
Well, at least you admit it is an issue: SCHEMABINDING is a hack to make SQL Server conform a bit more to the relational model.
Of course you can argue that SQL Server wasn't meant to be a relational database engine and only delivers what the tin says, but try arguing that with Microsoft.:-)
September 13, 2009 at 6:54 pm
Alex (9/13/2009)
OK, so it is a documented bug. Documenting a bug and providing a workaround doesn't mean it isn't a bug.
I think is where we shall have to agree to differ. It isn't documented as a bug. It is documented behaviour. You may find it has bug-like qualities, I do not agree.
Alex (9/13/2009)
When I write 'Select *' (and yes, I do sometimes), my intention, in particular when this ends up in production code, is to select all columns; and I mean ALL columns, not just the ones that happened to be there when the query was written. I don't know of any good alternative for that unless you query system views or tables to produce dynamic SQL and then execute that (but I would not call that a better alternative).
Specify the columns you need and schema bind where possible and appropriate. When requirements change, so does the code.
I can't think of a case where it is sensible to use SELECT * in production code, except after EXISTS.
Alex (9/13/2009)
Accepting this behaviour as a feature is like accepting that a view gives you last week's data, rather than current data, because it was written last week.
Not really, no. If you accept the way non-schema-bound views work, you should find all other dependencies that aren't schema-bound quite familiar. Since views have always behaved that way, I don't find it surprising in the least.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 13, 2009 at 6:57 pm
Paul White (9/13/2009)
You likened it to zeroing an account balance if it happened to end in a three!!
Yep, but I could have equally likened it to a kettle which exploded if you boiled it on a tuesday at 10:31pm, where the manufacturer put a note in the manual saying 'don't do that'. The point, as i said before, was to reflect on the fact that documenting something which is not what people would expect doesn't necessarily mean that it is then 'correct'.
Paul White (9/13/2009)
It's not unexpected if you have read the documentation, or know the subject well.
Not everybody reads the manual for their new kettles you know!! 😀
edit ->
Paul White (9/13/2009)
So the bug is in using SELECT *...is that your point now?
No, you asked if i foresaw any problems with using a DDL trigger to update schema for dependent objects. And I said yes I did - the same problems that you get from using SELECT * in the first place.
<- endedit
Paul White (9/13/2009)
So you would advocate a 'best effort' auto-refresh? Seems a bit ad-hoc to me.
I wouldn't advocate it, because I wouldn't advocate using SELECT * in the first place really. But I would say that if you do use SELECT *, then you're more likely to realise what has gone on with something like that trigger in place than if you saw the output of what happens with out it. I don't mean you as in you, obviously, I mean you as in 'one'.
Paul White (9/13/2009)
Certainly it is. A documented one. Just like with views.
I honestly can't believe you're calling it a feature... A behaviour, ok - but a 'feature'?
Paul White (9/13/2009)
You could, yes. That would appear in the 'Workarounds' section. Along with schemabinding and avoiding the star construct in the first place.
Totally agree.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 13, 2009 at 6:58 pm
Alex (9/13/2009)
Well, at least you admit it is an issue: SCHEMABINDING is a hack to make SQL Server conform a bit more to the relational model.
I said that in my first post. Not sure why you think it is a hack though, or to which relational model you refer.
Alex (9/13/2009)
Of course you can argue that SQL Server wasn't meant to be a relational database engine and only delivers what the tin says, but try arguing that with Microsoft.:-)
That seems an odd statement to make. Of course it's a relational database...?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 13, 2009 at 7:02 pm
Alex (9/13/2009)
SCHEMABINDING is a hack to make SQL Server conform a bit more to the relational model.
I can't really agree on that one. SCHEMABINDING does a lot more with respect to the way that queries can be optimised than perhaps might be initially obvious...
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 13, 2009 at 7:11 pm
Matt Whitfield (9/13/2009)
Yep, but I could have equally likened it to a kettle which exploded if you boiled it on a tuesday at 10:31pm, where the manufacturer put a note in the manual saying 'don't do that'. The point, as i said before, was to reflect on the fact that documenting something which is not what people would expect doesn't necessarily mean that it is then 'correct'.
It will not surprise you to learn that I wouldn't accept the kettle analogy either :laugh:
If the documentation said that dependencies unsupported by schema binding would always continue to work as expected even if the underlying objects changed, that would be a bug.
Matt Whitfield (9/13/2009)
Not everybody reads the manual for their new kettles you know!!
There would be many fewer accident reports on Tuesdays if they did.
Matt Whitfield (9/13/2009)
I wouldn't advocate it, because I wouldn't advocate using SELECT * in the first place really. But I would say that if you do use SELECT *, then you're more likely to realise what has gone on with something like that trigger in place than if you saw the output of what happens with out it. I don't mean you as in you, obviously, I mean you as in 'one'.
That I can, and do, agree with. Something based on a trigger like that would be potentially useful to anyone with non-schema-bound dependencies. Cross-database stuff can be a pain like that - I don't immediately recall whether your posted script would help there, but I imagine it could be extended to do so, if not.
Matt Whitfield (9/13/2009)
I honestly can't believe you're calling it a feature... A behaviour, ok - but a 'feature'?
You will notice the absence of adjectives like 'good' or 'desirable'. My intention was to describe it as something deliberately built-in and documented as such. I think the whole thing is rather unfortunate, and go out of my way to avoid it. We can compromise on 'behaviour' by all means.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 13, 2009 at 7:16 pm
Paul White (9/13/2009)
It will not surprise you to learn that I wouldn't accept the kettle analogy either :laugh:If the documentation said that dependencies unsupported by schema binding would always continue to work as expected even if the underlying objects changed, that would be a bug.
I'll go for that.
Paul White (9/13/2009)
There would be many fewer accident reports on Tuesdays if they did.
ROFL
Paul White (9/13/2009)
I don't immediately recall whether your posted script would help there, but I imagine it could be extended to do so, if not.
No - it being 2 in the morning i decided to go for 'reasonably simple'... To be honest i'm just trying to test out my code completion engine a bit.
Paul White (9/13/2009)
We can compromise on 'behaviour' by all means.
And once again balance was felt in the force 😀
A good discussion I feel, even if I misunderstood your tone - for which I apologise.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 13, 2009 at 7:18 pm
I said that in my first post. Not sure why you think it is a hack though, or to which relational model you refer.
Codd and Date (http://en.wikipedia.org/wiki/Relational_model). I am not aware of any others.
That seems an odd statement to make. Of course it's a relational database...?
Not if you define a relational database as one that conforms to the relational model. At least not according people who actually study and try to implement the relational model. Read any work by Date, for example.
Things like schemabinding are implementation artefacts that are introduced to overcome technical "issues". In an ideal world, i.e. a proper implementation of the relational model, such artefacts would not exist (or be totally transparant).
I admit that perhaps my expectations of SQL Server as a relational database are too high and the issue really is that I/we should be content with whatever the tins says, regardless of the underlying theory and the expectations created by it.
September 13, 2009 at 7:21 pm
Matt Whitfield (9/13/2009)
A good discussion I feel, even if I misunderstood your tone - for which I apologise.
There's really no need - I enjoy an energetic debate more than most.
Good fun, thanks - see you around the forums.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 91 through 105 (of 151 total)
You must be logged in to reply to this topic. Login to reply