We, as T-SQL developers definitely do need to get better and formalize our testing, I blogged the other day about how to test constraints for example:

http://sqlserverfunctions.wordpress.com/2014/10/05/unit-testing-constraints-in-sql-server/

I believe unit testing basically comes down to separating your code into small units (i.e. Lets get away from thousand line stored procedures) and deciding what the code unit should and also inversely shouldn't do!