Following some earlier chatter about Views which reference Tables which then change (particularly if the VIEW does a SELECT * - which is not something that every actually happens here) it got me thinking about sp_RefreshView (which we use when we rollout scripts that may have changed Tables) and SCHEMABINDING.
It raises the question why would I not use SCHEMABINDING on all views?
Obviously some hassle if I want to change a Table as I will have to drop all related views and recreate them. Not sure that is any harder than Drop/Recreate constraints, Triggers and so on?? which I have to do now anyway (well, not for a simple DDL change, but many of mine require a drop/re-create of the table)
As it happens we have all VIEW scripts in individual files. Our rollout process consists of assembling a script from all scripts with a newer modified-date than "last time". So if I just "touch" the date on a VIEW script it will be included in the next rollout - thus I just have to add a series of DROP VIEW statements to my script prior to the ALTER TABLE statement and any necessary Re-create Views will happen as part of my rollout process. (Sidenote: we use ALTER for pre-existing Views that are re-created, and I suppose being able to see a Create and a Modify date might have a benefit, but with this process the original view will be Dropped so my Create & Modify dates will be the same)
Upside is that I will never make a change to a Table which has a knock-on effect on a view without also considering which views might be effected and need changing.
That lead me to an interesting article on using SCHEMBABINDING in Functions that do simple calculations. I have quite a few of those which I use as shorthand to save me repeating a small snippet of code. For example a "fnSafeInt2String(MyInt)" which converts an INT to a VARCHAR and if the INT is NULL it returns a blank string, and thus can safely be concatenated without propagating any NULL values. Clearly no Tables or Column involved there, but when I check OBJECTPROPERTYEX() on the function I find that:
SYSTEMDATAACCESS = 1
USERDATAACCESS = 1
IsDeterministic = 0
IsPrecise = 0
IsSystemVerified = 0
and all those values are swapped if I use SCHEMBABINDING. Apparently the absence of SCHEMABINDING will cause the query optimizer to assume that the function may access data and it will add a spool operator ... so adding SCHEMBABINDING improves the query plan (I read that it accounts for a performance improvement of 22% but I haven't checked that)
There was also an additional benefit (which doesn't apply to me) where a Function is persisted in, say, a computed column / index.
So ... ... any reason not to use SCHEMABINDING on all, suitable, Functions and all Views (other than the hassle of Drop/Create all relevant views)?