Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories





Anything involving joining or relating tables in SQL; includes join techniques, optimizations, etc.
SQL Server 2005 Foreign Key Constraints: SET NULL and SET DEFAULT

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 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.

posted @ Wednesday, August 13, 2008 9:56 AM | Feedback (3) | Filed Under [ Database Design Links Joins/Relations ]


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 took it over. He claimed the design of the query engine handled Left joins...

posted @ Wednesday, February 13, 2008 11:22 AM | Feedback (10) | Filed Under [ Efficiency Joins/Relations ]

Rewriting correlated sub-queries with CASE expressions

Here's a very common situation that is very easy to optimize and simplify, submitted via the mailbag.

posted @ Wednesday, January 09, 2008 8:42 AM | Feedback (3) | Filed Under [ CrossTabs / Pivoting Data Efficiency Joins/Relations ]

Taking a look at CROSS APPLY

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) b That is not legal because A.Val is out of scope within the derived table; this is because the derived...

posted @ Thursday, October 18, 2007 5:01 PM | Feedback (10) | Filed Under [ T-SQL Techniques SQL Server 2005 Joins/Relations ]

Be Careful When Mixing INNER and OUTER Joins

Be careful when mixing OUTER and INNER JOINS together in a single SELECT statement. 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. Here's an example that demonstrates some of the issues. read more...

posted @ Thursday, October 11, 2007 2:39 PM | Feedback (27) | Filed Under [ T-SQL Techniques Joins/Relations ]

Creating CSV strings in SQL: Should Concatenation and Formatting Be Done at the Database Layer?

A question I see very often in the SQLTeam forums is how to return data in a summarized form by concatenating a list of values into a single CSV column. This can be done fairly easily in T-SQL, but as the formatting and concatenation requirements becomes more elaborate, be sure to ask yourself: Am I forcing presentation code into the database layer? read more...

posted @ Tuesday, October 09, 2007 3:02 PM | Feedback (19) | Filed Under [ T-SQL .NET (C# / VB) Efficiency Report Writing ASP.NET Joins/Relations ]

"Nested WHERE-IN" Anti-Pattern Follow-up; More on Derived Tables (sub-queries)

A quick follow up to the "Nested WHERE-IN" anti-pattern post from yesterday ... If you didn't get a chance, be sure to read the comments from that post as well, there are some great points in there so far. read more...

posted @ Friday, July 13, 2007 9:33 AM | Feedback (3) | Filed Under [ T-SQL Techniques Joins/Relations ]

The "Nested WHERE-IN" SQL Anti-Pattern

There's a fascinating technique that I see many beginners use when writing SQL statements, and I call it the "Nested WHERE-IN" anti-pattern. It is, unfortunately, a common SQL technique used to avoid JOINS at all costs. read more...

posted @ Thursday, July 12, 2007 12:50 PM | Feedback (19) | Filed Under [ T-SQL Techniques Joins/Relations ]

Using GROUP BY to avoid self-joins

Sometimes, it appears that a necessary solution to common SQL problems is to join a table to itself. While self-joins do indeed have their place, and can be very powerful and useful, often times there is a much easier and more efficient way to get the results you need when querying a single table.


posted @ Tuesday, June 12, 2007 11:35 AM | Feedback (5) | Filed Under [ T-SQL Techniques Efficiency Joins/Relations GROUP BY ]

Criteria on Outer Joined Tables

When using an OUTER JOIN, you should put criteria on the outer table in the join condition, not in the WHERE clause. However, I often see a "workaround" to avoid this simple and solid rule, which might seem to work but actually doesn't. Since it is hard to explain why over and over in forum posts, I thought it might be helpful to address that here once and for all with an example.


posted @ Monday, May 14, 2007 10:12 AM | Feedback (16) | Filed Under [ T-SQL Joins/Relations ]

Thinking Set-Based .... or not?

So, I hear you're a "set-based SQL master" ! As Yoda once said, you've "unlearned what you have learned". You've trained yourself to attack your database code not from a procedural, step-by-step angle, but rather from the set-based "do it all at once" approach. However, don't completely forget the most important skill that you learned in the procedural world!


posted @ Monday, April 30, 2007 1:03 PM | Feedback (21) | Filed Under [ T-SQL Techniques Report Writing Joins/Relations ]

Better Alternatives to a FULL OUTER JOIN

As many of you know, I strongly recommend that you avoid using RIGHT OUTER JOINS, since they make your SQL code less readable and are easily rewritten as LEFT OUTER JOINs. In addition, I have yet to find a situation where a FULL OUTER JOIN makes sense or is necessary -- I have found that in just about every case other techniques work better.


posted @ Thursday, April 19, 2007 11:56 AM | Feedback (69) | Filed Under [ T-SQL Techniques Efficiency Report Writing Joins/Relations GROUP BY ]

Conditional Joins in SQL Server

Sometimes, when writing SELECTs, we come across situations in which we we need to write a join containing a condition of some sort. You might think to do this with either a CASE expression or with some OR boolean logic in your join expression. There's a much better way to approach the problem.


posted @ Tuesday, April 03, 2007 12:15 PM | Feedback (39) | Filed Under [ T-SQL Report Writing Joins/Relations ]

How to JOIN Multiple Transactional Tables in SQL

A common difficulty beginning SQL programmers encounter is joining two or more transactional tables all in one SELECT statement. Missing data, duplicates, time-out errors, and other unexpected results often arise from trying to directly write JOINS between two transaction tables.


posted @ Monday, June 19, 2006 3:34 PM | Feedback (10) | Filed Under [ T-SQL Techniques Joins/Relations GROUP BY ]

The power of the Cross Join

Many SQL books recommend that you “avoid cross joins” or “beware of Cartesian products” when writing your SELECT statements, which occur when you don't express joins between your tables. It’s true that you need to ensure that your join conditions are adequately stated so that you don’t accidentally produce this effect, but it is not true that you should avoid these types of joins in every situation.


posted @ Monday, September 12, 2005 9:20 AM | Feedback (26) | Filed Under [ T-SQL Techniques Joins/Relations ]

Delete Duplicates And Resolve Foreign Key References in SQL

Here's a scenario many of us have encountered: You inherit a database. TableX has an identity column "ID" set up as the primary key, and contains many duplicates.


posted @ Thursday, October 07, 2004 11:15 AM | Feedback (4) | Filed Under [ T-SQL Database Design Joins/Relations ]

Comparing sets of rows from two tables

I thought I'd post a blog showing a technique I haven't seen elsewhere.  This is from a recent post of mine at the SqlTeam forums. The problem: you have two tables, each containing mutiple rows of data per “SetID”.  You need to compare the two tables to see which sets have complete matches. First, the DDL and the data: create table #t1  (SetID int not null,  Attribute varchar(10) not null,  Value int not null, constraint t1_pk primary key (SetID, Attribute)) create table #t2  (SetID int not null,  Attribute varchar(10) not null,  Value int not null, constraint t2_pk primary key (SetID, Attribute)) GO insert into #t1 select 1,'a',1 union select 1,'b',2 union select 1,'c',3 union select 2,'a',4 union select 2,'c',5...

posted @ Friday, October 03, 2003 1:59 PM | Feedback (15) | Filed Under [ T-SQL Joins/Relations ]

Powered by:
Powered By Subtext Powered By ASP.NET