Jeff Moden (11/22/2016)
Ed Wagner (11/22/2016)
carl.thompson (11/22/2016)
Hi SSCrazyEights,Thank you for your reply.
That makes perfect sense and I think confirmed what we thought to be the reason for the inclusion in the function. Can you confirm that this has been used in the function for the purpose of speed alone? As the function does not appear to be referencing any schema bound objects?
Our infrastructure team have advised that a system upgrade failed due to the split function and a view both using WITH SCHEMA BINDING. Other for than performance improvement we advised that the SCHEMA BINDING option was not required within the split function. Have we provided correct advice?
Thanks in advance for any reply.
Carl
The schemabinding is always optional. Specifying it means that the query optimizer knows that it doesn't have to check anything, so it saves a step at run-time. This is where the performance improvement comes from.
The other advantage it can provide is safety. Let's say you have a function that references a table and relies on that table to do what it needs to do. Someone comes along and, not knowing about your function, alters the table in a way that breaks your function. With schemabinding specified on the function, the table can't be altered. The function has to be altered, then the table altered, then the function altered again. In short, it saves you from yourself...and others.
I must admit I'm curious why your infrastructure team said that a system upgrade failed due to a schemabinding option being present on a view and function. If you're talking about an upgrade to an application, I can see it if the upgrade altered a base object, but I would think this would have been discovered during development or testing. I can't see it for an upgrade of the system - either OS or database version.
Ah... you said "always". 😉 Just for those that might run into it, it's not optional if you're using it as a source for a persisted computed column.
Touche, sir. 😉