For readable code, avoid code you need to translate in your head

I'm a big advocate of readable code. These include things like good aliasing for tables when necessary, separating code out into logical chunks, and CTE (common table expressions) instead of derived tables.

One thing I've always tried to do is avoiding code that requires translation. What do I mean by that? I try to avoid code such as this:

-- Call Answer Time After Hours
when IsBusinessHours = 'False' and SLARuleID = 2  
    then Sum([AnswrIn30Seconds]+[AnswrIn45Seconds])

It's okay when there's just one chunk of code like this, but if there's a whole set of code like this, you're more likely to make mistakes, because you need to internally translate, "So, if it's after hours, than the IsBusinessHours must be False." This caused me a hard-to-troubleshoot bug recently.

What I'd rather read is something like this:

-- Call Answer Time After Hours
when IsAfterHours = 'True' and SLARuleID = 2  
    then Sum([AnswrIn30Seconds]+[AnswrIn45Seconds])
    

It's much easier to understand. So what I do now in these situations is to set up a field that's the opposite of the original, when it would help readability. I use something like this

        ,IsAfterHours   = 

            convert(bit,
                Case 
                    When IsBusinessHours = 1 Then 0
                    When IsBusinessHours = 0 Then 1
                    Else Null 
                End
                )

Flexible update trigger - take the column value when provided, otherwise have the trigger provide a value

We have a small, departmental database with some tables that will sometimes be updated via a batch process, and sometimes via a people inputting the data directly in a datasheet. Here's a neat trick to allow us to have auditing fields (UpdateDate , LastUpdatedBy) that will either reflect the value provided by the update statement, OR provide a default value, if no value is provided in the update statement.  The trigger does this using the Update() function, which has apparently been around for a while, but I'd never used it before.

Here's the trigger:

 IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID('dbo.trg_Update_SLAMetric'))  
 DROP TRIGGER dbo.trg_Update_SLAMetric  
 go  
 CREATE TRIGGER trg_Update_SLAMetric  
 ON dbo.SLAMetric  
 AFTER UPDATE  
 AS  
 -- Update UpdateDate , LastUpdatedBy  
 UPDATE SLAMetric  
 SET   
   UpdateDate = GETDATE()  
   ,LastUpdatedBy =   
     case   
       When UPDATE(LastUpdatedBy) then Inserted.LastUpdatedBy  
       else SUSER_NAME()  
     end  
 from dbo.SLAMetric SLAMetric  
   join Inserted  
     on Inserted.SLAMetricID = SLAMetric.SLAMetricID  

It correctly updates the LastUpdatedBy field in both situations - updating the table and including the LastUpdatedBy field, and also just updating other fields (in which case it's just set to SUSER_NAME().

 update slametric set MetricNumerator = 0  
 select * from slametric  
 update slametric set LastUpdatedBy = 'test'  
 select * from slametric