Foreign Key Constraints: SET NULL and SET DEFAULT
13 Aug 2008 1:56pm GMT 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.
On RIGHT OUTER JOINS ...
13 Feb 2008 4:22pm GMT Because I feel pretty strongly about this and the entire focus of my blog is writing clear, clean and efficient SQL, I thought I'd repeat my response from a SQLTeam forum question here. smithje asks this, regarding OUTER JOINS: Left/Right, does it matter. Is one better than the other? A few years ago consultant on a project for our company advised me to always write my queries to use Left joins. He had worked on the project to convert the original database application to MS SQL when Microsoft...
Rewriting correlated sub-queries with CASE expressions
9 Jan 2008 1:42pm GMT Here's a very common situation that is very easy to optimize and simplify, submitted via the mailbag: Nate writes: Hey, I have a read a bunch of your stuff on your blog and you seem to be right on the money. I thought maybe you would be able to point me in the right direction and possibly address this issue on your blog so others could benefit from your understanding. I have been searching for the best way to do what I think should be a simple task in SQL. I have a...
Taking a look at CROSS APPLY
18 Oct 2007 9:01pm GMT Applying a Sub-Query, Joining a Derived Table ... I think the easiest way to think of CROSS APPLY is that it is like doing a CROSS JOIN with a correlated sub-query instead of a derived table. Let's see if I can explain that .... A derived table is "self-contained", in that all tables and columns in the parent SELECT are not accessible (though variables and parameters can be referenced). For example, consider: select A.*, b.X from A cross join (select B.X from B where B.Val=A.Val)...
Be Careful When Mixing INNER and OUTER Joins
11 Oct 2007 6:39pm GMT I had previously written about the danger of Criteria on Outer Joins, but recently another situation popped up that occasionally causes confusion with OUTER JOINS that I thought I might address. The issue is when you have multiple tables joined in a single SELECT, and you mix OUTER and INNER JOINS together. The end result doesn't always seem to "work", and it can be tricky to understand exactly why and how to fix it without incurring additional unintended side effects. Consider the...