Most people know about cascading updates and deletes, but did you know there are two other foreign key constraint options you can use to maintain referential integrity?

Read all about them in my latest article over at SQLTeam.com.

These features, introduced with SQL Server 2005, haven’t got a lot of publicity, but they can be very useful.  I just used the SET NULL option recently for the first time (inspiring me to put together an article on it) and it works great.

Always remember that the column names returned in a result set do not have to be the same as what you eventually output at your presentation layer.  

For example, suppose you have stored procedure that accepts a @CurrentYear parameter and returns a sales variance between the current year and the previous year for each customer.  I often see programmers struggling with writing dynamic SQL to produce output like this:

That is, the names of the columns vary based on the data; that is not a good way to return data from your database!  A much better result set to return is simply this:

Notice that with that set of columns, no dynamic SQL is needed, and the column names returned are always constant regardless of the value of the @CurrYear parameter.  

As mentioned, the fact that your data set has columns labelled “CurrentYear” and “PrevYear” does not mean that you cannot re-label them any way that you like on your report or web page. 

If your client code called the stored procedure and provided a @CurrentYear parameter, then it knows exactly what “CurrentYear” and “PrevYear” represent, and you can easily label the columns in the final result exactly as needed with simple formulas or a few lines of code. 

Remember that in the world of relational database programming, table names and column names should be constant — only the data itself should change.   Focus on returning consistently structured data from your database, and let your client applications handle the labeling of columns to make them look nice.