Sunday, April 18, 2010

UDFs and Underlying Schema Changes

I had a problem at work recently where we changed a column's collation but a User Defined Function that referenced this column didn't reflect the updates and started complaining about collation conflicts. In this situation, most people (at least most people I know) recommend dropping the UDF and recreating it to force the UDF to update, but there is a better way.

sp_refreshsqlmodule will refresh the UDF in a single step and without the risk in dropping an object. The MSDN documentation for CREATE FUNCTION (under Best Practices) recommends creating functions WITH SCHEMABINDING as an alternative.

Saturday, April 03, 2010

Truth and Python 3: The 'and' and 'or' Operators

The Python and and or operators are a little involved but work in a useful and helpful way. These operators combine two objects, called operands, into a single object. The operators can be of any type and aren't restricted to bool. Python uses the keywords for these operators instead of the && and || symbols common in other languages.

The and operator returns the first false operand or the last one if both operands and true. Some examples will make this clearer:

>>> '' and 0
>>> 'x' and 0
>>> 'x' and 1

The or operator returns the first true operand or the last one if both operands are false. Here are some examples:

>>> 'x' or 1
>>> '' or 1
>>> '' or 0

Of course, bool(x and y) will always be equal to bool(x) and bool(y). For this reason, and operator will work as expected when used in conditional clauses. Similarly, bool(x or y) will always be equal to bool(x) or bool(y).

These operators are 'lazy' or 'short-circuiting'. The second operand is only evaluated if it would make a difference to the result. Here's an example:

>>> def x():
...     print('x')
...     return True
>>> def y():
...     print ('y')
...     return True
>>> x() or y()

In the example above y isn't called since x returns True.

Logically or will return the first operand if it's true, otherwise it will return the last. This means that or works like the null coalescing operator (??) in C# and it's often used in that way. Here's a vague example:

def some_method(some_parameter=None):
    some_parameter = some_parameter or get_default_value()
    # details...