Table-valued parameters in SQL 2005

Can’t be done? Oh, but it can. Let me show you.

Just quickly, a Table-Valued Parameter is a useful thing introduced in SQL 2008 that lets you have a read-only parameter which is a table type, passed into a stored procedure. To use it you need to have a user-defined table type, so that you can define what is going to be passed in. You can read about them at

The 2008 code looks something like this:


/* First create a database to play in */          
create database TVPDemo;         
use TVPDemo;         

/* And create a table with some sample data. I’m getting mine from AdventureWorks */          
select ProductID, Name as ProductName, ListPrice, ProductSubcategoryID         
into dbo.Products         
from AdventureWorks.Production.Product;         

/* Now for the real stuff – create a table type */          
create type dbo.NewProducts as table         
(ProductName nvarchar(100) collate Latin1_General_CI_AS         
,ListPrice money         
,SubCategory int         
,DeleteMe bit         

/* And a stored procedure which uses this table type */          
create procedure dbo.MaintainProducts(@NewProducts dbo.NewProducts readonly) as         
/* Obviously we could MERGE – that’d work nicely here. But I want 2005 features */ 

 /* Update some products into Products table */         
 update p set ListPrice = n.ListPrice, ProductSubcategoryID = n.SubCategory         
 from dbo.Products p         
 join @NewProducts n         
 on n.ProductName = p.ProductName         
 where n.DeleteMe = cast(0 as bit);    

 /* Insert some */         
 insert dbo.Products (ProductName, ListPrice, ProductSubcategoryID)         
 select n.ProductName, n.ListPrice, n.SubCategory         
 from @NewProducts n         
 where not exists (select * from dbo.Products p where p.ProductName = n.ProductName)         
 and n.DeleteMe = cast(0 as bit);    

 /* And delete some */ 
 delete p         
 from dbo.Products p         
 join @NewProducts n         
 on n.ProductName = p.ProductName         
 where n.DeleteMe = cast(1 as bit);    

 /* Now list them all, returning this to the client */ 
 select *         
 from dbo.Products; 


/* Now let’s familiarise ourselves with what’s in Product now */          
select *         
from dbo.Products;    

/* And do some maintenance on it. We create a table variable of the appropriate type, populate it and call the proc */         
declare @SomeNewProducts dbo.NewProducts;         
insert @SomeNewProducts (ProductName, ListPrice, SubCategory, DeleteMe)         
select 'Blade', 0.1, 1, 0         
union all         
select 'Blade2', 0.1, null, 0         
union all         
select 'Bearing Ball', 1, 2, 1         

exec dbo.MaintainProducts @SomeNewProducts;    

When we ran this stored procedure, the latest version of dbo.Products was outputted, so we can clearly see the new record, and the absence of the one we deleted.         

But this wasn’t possible in SQL 2005. We didn’t have user-defined table types, and we certainly didn’t have table-valued parameters.

Except that we could still do something very similar. This was something I’d taken for granted, but when I showed this to someone at the PASS Summit, and then someone else, I got persuaded to write a blog post on it.

If you haven’t seen this idea before, I’m sure you’ll kick yourself. It’s remarkably simple, but I think it’s quite powerful. Like I said – I’d taken it for granted.

The idea is this: make a VIEW with an INSTEAD OF trigger, using the inserted table instead of the table variable.

That INSTEAD OF trigger is essentially where your stored procedure is kept. A trigger is still a procedure, it’s just not stored in the traditional list of stored procedures. But it will act just like one.

As for the view – that can just be a placeholder. Think of it as simply defining the columns you need to handle. You don’t need a FROM clause, and you don’t even need any rows to come back. I like to put a contradiction in there so that I don’t think there’s any real values coming out.

So a trigger doesn’t take a table-valued parameter, but it can leverage the inserted and deleted tables that are available in triggers. For us, we’re just interested in the former. Have a look at the code, and you’ll see what I mean.

This code can run on SQL 2005 (well, it can also run on later versions, but that’s less important).


/* First let’s set up a new database, just like we did in SQL 2008*/          
create database TVPDemo;         
use TVPDemo;         

select ProductID, Name as ProductName, ListPrice, ProductSubcategoryID        
into dbo.Products         
from AdventureWorks.Production.Product;         

/* Here’s the tricky bit. Make a view. Focus on the columns. I put WHERE 0=1 in, just to make it cleaner */        
create view dbo.NewProducts as        
 cast(N'' as nvarchar(100)) collate SQL_Latin1_General_CP1_CI_AS as ProductName,         
 cast(0 as money) as ListPrice,         
 cast(0 as int) as SubCategory,         
 cast(0 as bit) as DeleteMe         
where 0=1         

/* This trigger contains the same code as in the 2008 stored procedure.        
* But instead of having a table variable, we use the inserted table.         
create trigger dbo.MaintainProducts on dbo.NewProducts instead of insert as        
 /* Update some products into Products table */ 
 update p set ListPrice = i.ListPrice, ProductSubcategoryID = i.SubCategory         
 from dbo.Products p         
 join inserted i         
 on i.ProductName = p.ProductName         
 where i.DeleteMe = cast(0 as bit);    

 /* Insert some */         
 insert dbo.Products (ProductName, ListPrice, ProductSubcategoryID)         
 select i.ProductName, i.ListPrice, i.SubCategory         
 from inserted i         
 where not exists (select * from dbo.Products p where p.ProductName = i.ProductName)         
 and i.DeleteMe = cast(0 as bit);    

 /* And delete some */ 
 delete p         
 from dbo.Products p         
 join inserted i         
 on i.ProductName = p.ProductName         
 where i.DeleteMe = cast(1 as bit);    

  /* Now list them all, returning this to the client */          
 select *         
 from dbo.Products;    


/* Look what’s in there now */         
select *         
from dbo.Products;    

/* Remember there’s never anything in here */         
select *         
from dbo.NewProducts;    

/* Now we simply insert into our view. As we do, the trigger runs immediately and makes the changes */          
insert dbo.NewProducts (ProductName, ListPrice, SubCategory, DeleteMe)         
select 'Blade', 0.1, 1, 0         
union all         
select 'Blade2', 0.1, null, 0         
union all         
select 'Bearing Ball', 1, 2, 1 

So there you have it – a useful TVP equivalent in versions prior to SQL 2008. I get that I’m probably writing this post about ten years too late. Sorry about that.

But if you’re not fond of the idea of having to declare and populate a table variable, then perhaps this idea is for you. This method will support any type of inserting, whether it’s row-by-row, or the results of a single SELECT statement. One day though, TVPs won’t be READONLY any more (this doesn’t seem to be the case for SQL Server 2012 unfortunately), and when that happens, you’ll want to definitely be using TVPs.


Highlights and Lowlights of PASS Summit 2011

This was a proper big week.

The PASS Summit ran from Tuesday night to Friday, but I’d arrived in America the Friday before. So by the time it actually started, I had that strange feeling that things were wrapping up. My calendar was ridiculously full. The stuff that I was aware of ahead of time looked like this:

Friday 7th: Arrive in America. Travel to Portland. Speaker Dinner.

Saturday 8th: SQL Saturday #92 (two sessions to give, plus a song performance with Buck during the morning break). Charity dinner for The Leukemia & Lymphoma Society (whom LobsterPot sponsor).

Sunday 9th: Walk the Portland Half Marathon. Travel to Seattle. Collect my kilt. Register for the Summit.

Monday 10th: Early morning prayer meeting. Deliver a pre-conference seminar. Insiders Dinner in evening.

Tuesday 11th: Early morning prayer meeting. Meeting about SQL Saturday. PASS Board Meeting. Insiders Day at the Microsoft Campus. Opening Night Party, including being a Quiz Bowl contestant. Speaker/Volunteer Dinner.

Wednesday 12th: Early morning prayer meeting. Chapter leaders’ meeting. Chapter lunch. Book signing. Lightning Talk to deliver (my song). Global Growth meeting. Exhibitors’ party. Parties for SQL People, SQL Sentry, SolidQ and SQLKaraoke.

Thursday 13th: Early morning prayer meeting. WIT Lunch. Spotlight session to deliver. Redgate Dinner. Party at Gameworks.

Friday 14th: Early morning prayer meeting. More book signing. Board Q&A session. Board photos.

Saturday 15th: Sleep in and fly home.

That’s the short version. Really. There were a lot of other things that were squeezed in – in particular, the fact that I had promised Audrey Hammonds about six months ago that I would run through her presentation with her, and one other thing that became somewhat significant: THE FACT THAT ALLEN KINSEL ARRANGED FOR ME TO PERFORM MY SONG DURING THE FRIDAY KEYNOTE.

Readers of my blog (and plenty of other blogs) will know by now that I played my guitar and sang at the start of the Friday Keynote. Buck Woody accompanying me with his guitar and backing vocals. What you might not know is that this only got arranged late on Wednesday night. Allen had seen me perform it (again with Buck) during the Lightning Talks on Wednesday afternoon, and by the time the night was over, Buck and I were booked in to perform it in front of the three and a half thousand delegates at the biggest keynote of the week. I’ve played and sang before a couple of hundred before, but never even close to that many. To say I had mixed emotions would’ve been an understatement. I didn’t hesitate to say yes, and was excited, but was also phenomenally nervous.

As a late entry to a tight schedule, we got two minutes only and had to cut the song short. We didn’t sing the bridge section, so stopped after just Verse Chorus Verse Chorus. It was tremendous fun, and I loved seeing faint glows of phones being waved around in the crowd. I desperately wanted to look into the crowd as I was walking off stage, but my nerves and the fear of tripping on the guitar lead froze me completely and I didn’t turn my head at all. I was told later that there was a standing ovation – but I was just full of emotion, and so tired. So tired. It was my own fault – I’d gone out with Microsoft people after the various parties, and when they’d all gone to bed for a couple of hours around 5:30am, I’d got myself ready to lead a quick song at the 6am prayer meeting head off to my 6:15am sound check, prior to the 7:15am book signing. I got through the day (although I fell asleep for a few minutes during Audrey’s session, which I will need to watch to see how she did). I eventually got to bed around 10:30pm Friday night, and about twelve hours later managed to get up with just enough time to pack and check out of the hotel for noon.

But this wasn’t the highlight for me.

Nor was the highlight the fact that my level of involvement was so much greater, now that I’m a Board Member. I have to admit that I always get so much more out of events like this if I’m involved. I appreciate that I can get to more sessions if I’m not already laden with other entries on my calendar, but I would rather serve others wherever possible. I’m still new on the board, but as an advisor, I’m hoping to be able to influence things like the Summit more and more next year, and maybe there will be a few areas in which we can find opportunity to improve it still.

Serving is great – but wasn’t the highlight.

The highlight was the people. It always is, and it always will be. Right from the moment I arrived in America and tracked down John & Yanni Robel and Jes Borland. Arriving in Portland and seeing Jeremiah & Kendra, Buck Woody, Tim Ford, Erin Stellato and many more at the SQLSaturday events. Participating in the Portland Half Marathon with Erin, Jes, Yanni, Brent Ozar, Karen Lopez, Doug Lane (who did 10km) and Allen White (who did the full marathon), and a lots of people wearing the LobsterPot logo on their shirts. And despite not being able to run (I physically can’t run because of an old back injury, so I just walked it), I didn’t even finish last of the half marathoners! For all the extra pain I felt because my back isn’t really up to walking 21km in 3 hours, it was worth it. I’m especially proud of Yanni, who has achieved amazing things despite having a nasty blood cancer.

Despite Sunday being a ridiculously early start and a very long day, each day started with 6am prayer meetings. But this gave the opportunity to start each day on a good note, meet even more people (like Matt “Mrs” Slocum), and to figure out some of why I always click so well with friends like Stacia Misner.

Even the time I spent moving from one spot to another was a chance to see people and grow friendships deeper. I think of the night I was heading back to my hotel after the walk back from one of the parties had gone past the other hotels, and I bumped into the “Damn Strates” (Jason & Sarah), only to spend an hour talking with them about a number of different things. Or the time spent talking with one of the industry’s newest and brightest lights Jes Borland, who thinks she can out-talk me. Or the time with fellow board members JRJ, MarkS & Lara, which is time I can’t help but enjoy as the conversations switch over and over from flippant to serious and back again.

Oh, and the lowlight was losing my prescription sunglasses. That’s a real pain.

The SQL community has something very special, that other communities should be jealous of. These people genuinely love each other, and it’s really good to see and to be part of it. I’ve seen the same at SQLBits, but not in many other contexts.


PS: Apologies to everyone for not having links on your names. I wrote this on a plane without connectivity.

Supporting Leukemia Research at the Portland Half Marathon

My company LobsterPot Solutions gives quite a bit to charity. Sometimes it’s working for reduced rates (or even free), but sometimes we give money directly.

A Seattle-based friend of mine has CML – one of the types of leukemia, and when I found out she was raising money for the Leukemia & Lymphoma Society by running marathons through their Team In Training program, I wanted to help. LobsterPot donated money to her cause, and I had expected that might be the end of the matter.

Later, I was contacted for my company logo. It seems that the amount we gave was enough to have the logo printed on the backs of shirts. In fact, the LobsterPot logo is going to be worn by 170 people at races all season, including the Portland Half Marathon. As I was going to be within a few hours’ drive of Portland on the day, I decided to check it out for myself.image

I even entered the event – the Portland Half Marathon 2011.

I can’t run. I had a back injury in 2003, which has stopped me being able to cope with the impact of running. But I walked it, in a purple shirt provided by the Leukemia & Lymphoma Society with the LobsterPot logo on the back. It took me over three hours to get around the distance, and although walking is good for my back, I was still very sore at the end.

Walking that course though, all 21km of it, I saw lots of purple shirts with LobsterPot claws on them, the wearers all encouraging each other to stay strong, and to finish. Many of these people, including my friend Yanni, are leukemia survivors, and it was inspiring.

So now I have a challenge for you. If you are a business owner, then find ways to give back. It doesn’t have to be much, but it should be something. If you don’t know anyone you can sponsor for this type of event, visit Yanni’s fundraising page at She’s running a full marathon in January, and has just started raising funds.

Get yourself over to and see how to donate to her. Who knows – if you’re a company there could be a hidden marketing value there. Three years ago she was told she had an incurable blood cancer. Just over a week ago, she completed a half-marathon. Isn’t that the kind of spirit you want your company to reward?

But don’t do it for yourself, do it for Yanni and those like her.


I should've looked the other way

The words for that song I did at the PASS Summit 2011 are as follows. On the Friday, I stopped where the bridge starts. Various recordings of it are making it to YouTube, such as here, where the song starts around 2:20 in.

I should've looked the other way

Verse 1:
My query sucks – it takes too long
So long I wrote this song
The plan's not big – it ain't a giant
And yet I have an angry client
Performance now has made her weary
So I've come in to fix her query
I promise I won't ever fail her
Say "Trust me, love, I'm from Australia!"

I need to find you
But I don't want to search every row
My predicate's residual
My seek just runs too slow
I thought I'd caught a glimpse of you
Been searching for all day
But all along, I'd done it wrong
I should've looked the other way

Verse 2:
A trace is on, I know the reads
That fetch the bytes the query needs
There's spooling from a CTE
They've got recursion needlessly
I need to dig a little further
I worry there might be a cursor
The DBA has the plan_handle
He says it's not corrupt, he knows Paul Randal!

Repeat chorus

There is an index covering predicates with keys
But my developer has used inequalities
There is a range scan
Hiding truth
Hiding cost
Hiding you…

Repeat chorus x2

I should've looked the other way

© 2011 Rob Farley 😉


Data, Information and Knowledge

Hopefully my connection is slightly better during today’s keynote than it was during yesterday’s, when “Live Blogging” didn’t really cut it. The PASS staff saw the problem and have resolved it (thanks guys!).

Quentin Clark has jumped on stage to talk some more about SQL Server 2012, and he started with the expression “Data, Information and Knowledge”. I love this – I see Business Intelligence about extracting information from data, and it’s good to have Microsoft see this priority across the whole SQL platform.

He’s also talking about the 12 biggest features of SQL Server 2012, which he says has more new features than any release of SQL Server yet.

1. Required 9s

  • Integration Services as a Server
  • HA for StreamInsight
  • SQL Server AlwaysOn.

SQL Server has seen uptime as a key component for a long time, but to provide High Availability for StreamInsight is particularly significant. StreamInsight involves being able to consume data at significant rates, being able to run queries against that data while it’s still on the move – before it’s even reached the relational database. High Availability for StreamInsight should be able to better provide strategies to ensure that streaming data need not be lost. Businesses suffer badly when they lose data. SQL Server 2012 should be able to reduce this problem almost completely.

2. Blazing-Fast Performance

  • Performance Enhancements – RDBMS, SSAS, SSIS
  • ColumnStore Index

3. Rapid Data Exploration
4. Managed Self-Service BI

  • Power View + PowerPivot
  • Administration from SharePoint
  • Reporting Alerts

Yes, “Power View” has a space in it. It’s the new name for Crescent, which is about self-service reporting using a Silverlight experience. I’m all for allowing users to interact with the data in powerful ways, but I’m also concerned about how to manage this. SharePoint seems to continue as the main platform for this, and although I’d love to see the administration of these reports be done inside SQL itself (instead of SharePoint), I get that SharePoint is currently the platform of choice.

5. Credible, Consistent Data

  • BI Semantic Model
  • Data Quality Services
  • Master Data Services

I’m not going to comment on this stuff right now – it’s been talked about plenty already, but the enhancements definitely look good. The new stuff around DQS lets you fix up data at a number of extra points and have it pushed back into the underlying warehouse.

6. Organisational Compliance

  • Expanded Audit – User-defined, Filtering
  • User-defined Server Roles

This is useful. Audit is one of the massive things, and yet an administrator has always been able to turn it off. With the ability to have user-defined server roles, auditing turns into a much more real option.

7. Peace of Mind

  • Production-simulated Application Testing
  • System Center Advisor & Management Packs
  • Expanded Support – Premier Mission Critical

The Replay tools have become Distributed Replay, which is introduces a ton of really good options.

8. Scalable Data Warehousing
9. Fast Time to Solution

  • SQL Server Appliances – Optimised and Pre-tuned
  • HW + SW + Support – Just Add Power
  • Choice of Hardware

Appliances are stepping up. Buying an appliance, adding the network connection and electricity, and it’s ready to accept data in twenty minutes. The number of new options available suggests that the future of hardware buying will be even more focused on the appliance concept.

10. Extend Any Data, Anywhere

  • Greater Interoperability – new drivers for PHP, Java & Hadoop
  • ODBC Drivers for Linux & Change Data Capture for SSIS & Oracle
  • Beyond Relational: FileTable, 2D Spatial, Semantic Search

I really like the idea of CDC for Oracle, although I doubt there will be any

11. Optimised Productivity

  • SQL Server Data Tools (formerly “Juneau”)
  • Unified Across Database & BI
  • Deployment & Targeting Freedom

12. Scale on Demand

  • AlwaysOn
  • Deployment Across Public & Private
  • Elastic Scale

And of course, many of these items contribute to make a much more cloud-ready platform. Cloud isn’t for everyone, but Microsoft are certainly making steps to make it a more feasible option.

And we’re over – hitting publish now.


Mashing up data

In the PASS Summit Keynote (Day 1), they’re demonstrating tools to join data from Excel, Marketplace, SQL Azure, using the “Data Explorer”.

The thing that I’m liking about this is that you can specify that you want to do a lookup or a merge. Both joins, but two different types. I like this because when I write joins in T-SQL queries, I tend to have it in my head whether I’m expecting a Nested Loop (lookup) or a Merge Join. Seems Data Explorer lets me decide that right up front.

So you can pull data down from all kinds of different places, and publish it back out through the Marketplace. This means you can enhance other data sets, which other people can then enhance further, and this can continue to make some incredible data sources that can be consumed in OData just about anywhere.

SQL + Hadoop news

I’m at the PASS Summit, and announcements are coming out nice and fast. One of the big ones that has just been made is that SQL Server will work much more closely with Apache Hadoop. Amazing stuff, that I think helps people realise that Microsoft is very much about providing platforms, and isn’t trying to stop you from using your other platforms either.

So in the “coming soon”, there were:

  • Apache Hadoop-based distribution for Windows Server and Windows Azure
  • ODBC Driver and Add-in for Excel, both for Apache Hive
  • JavaScript Framework for Hadoop

I’m not doing anything with Hadoop myself, but I know plenty of people do, and this is really good news!

PASS Summit 2011 – Day 1 Keynote

I’m a guest blogger again!

This trip is already several days old for me. I arrived in America on Friday, delivered two sessions at Portland SQL Saturday event, walked (on Sunday) the Portland Half Marathon as a sponsor of Leukemia and Lymphoma Society’s Team in Training program, delivered a full-day summit precon seminar on Monday, and was in PASS Board meetings yesterday. Finally we’ve reached Day 1, and I could use a weekend!

But it’s now that things really kick off properly.

As I write this, Rushabh Mehta is on stage, talking about some of the numbers that apply to this summit. There are over attendees at the Summit this year, demonstrating the amazing reach that the PASS Summit has. And they also just talked about my involvement as an advisor in reaching the rest of the world better too. Every year, the Summit gets bigger, and PASS runs more events in more places. It’s an exciting time to be part of this community.

Microsoft’s Ted Kummert (Senior VP, Business Platform Division) is on stage now, and has announced that SQL Server Denali will officially be named SQL Server 2012.

Joins without JOIN

I’m now doing two sessions at the SQL Saturday event in Portland. I had been scheduled to do a single session (on indexes), but got an email yesterday asking if I could do another one as well. So now I’m going to do a session earlier in the day about Joins.

Yes, JOINs. Nice co-incidence to find that this month’s T-SQL Tuesday, hosted by Stuart Ainsworth and a week early because of the PASS Summit, is on that same topic. Plus the fact that I gave a presentation on it to the Adelaide SQL User Group last week.

So let’s jump in…

There are three types of joins that we write with the JOIN keyword – INNER, OUTER and CROSS. I’m imagine if you’ve read this far through this post, you’re very much aware of that. But there are other types of joins as well, that don’t use the JOIN keyword (and I’m not counting the comma short-hand for CROSS JOIN that we all used back in the 90s).

A join describes the way that two sets are related to each other within a query (and for the purposes of this post, I’m not going to count joins that concatenate two sets, such as using the UNION keyword). If you’re using the JOIN keyword, the relationship between the tables is shown in the ON clause – although if you’re using CROSS JOIN, the relationship is that every row in one set is related to every row in the other set.

If we don’t use the JOIN keyword, then where do we see joins? Well, in correlated sub-queries. I showed this when demonstrating that the APPLY keyword applies a join between one set an another in another recent blog post.

But every correlated sub-query, whether using APPLY or not must perform a join.

Consider the following:


SELECT *     
 , (SELECT COUNT(*) FROM Production.Product AS p       
 WHERE p.ProductSubcategoryID = s.ProductSubcategoryID) AS ProdCount      
FROM Production.ProductSubcategory AS s; 

There’s a join here, between Production.ProductSubcategory and Production.Product. The ON clause equivalent is the WHERE clause of the correlated sub-query. But what kind of join is it?

Let’s think… There’s clearly a relationship between the two tables, so it’s not a CROSS JOIN. Also, there’s nothing that would stop a row from the ProductSubcategory table to be returned, so it’s not an INNER JOIN (which only returns rows that match). It’s an OUTER JOIN. And we can prove this by looking at the execution plan.

The plan says “Right Outer Join”. Don’t be upset by the fact that it’s a Right Join rather than a Left Join – it’s just that Product table is coming from the top row and ProductSubcategory is from the bottom row. The ‘side’ of an Outer Join is just a matter of perspective. A Right Join is no different to a Left Join, it’s simply a question of which side of the desk you’re on.

Another type of join with JOIN uses EXISTS or IN. These two act very similarly, and I’m going to use EXISTS in my examples.

Consider the following query:


SELECT *     
FROM Production.ProductSubcategory AS s      
 (SELECT * FROM Production.Product AS p       
 WHERE p.ProductSubcategoryID = s.ProductSubcategoryID); 

and it’s converse:


SELECT *     
FROM Production.ProductSubcategory AS s      
 (SELECT * FROM Production.Product AS p       
 WHERE p.ProductSubcategoryID = s.ProductSubcategoryID); 

Just as before, this performs a join between the two tables. But it’s not actually an Outer Join we see going on here. After all, we see that rows can be filtered out of the ProductSubcategory table. Furthermore, we don’t have access to any information in the Product table, not even the count of rows. But it does Filter. This is neither an Inner Join or an Outer Join. The EXISTS form is a Semi Join, and the NOT EXISTS form is an Anti Semi Join. These operations simply filter one set based on whether a match is present or not. We see this operation in the two plans involved.

The fact that the Query Optimizer chose to implement one with a Hash Match and one with a Nested Loop is irrelevant. We’re looking at the Logical aspects of the plan, not the Physical.

And so we see there are plenty of times that a Join can appear without the JOIN keyword.

But let me show you something interesting about the Anti Semi Join for a moment…

Many people don’t like NOT EXISTS, and would rather write my earlier query like this:


SELECT *     
FROM Production.ProductSubcategory AS s      
LEFT OUTER JOIN Production.Product AS p       
ON p.ProductSubcategoryID = s.ProductSubcategoryID      

It’s relatively easy to show that these two queries should be identical. Both of them find rows where a match doesn’t exist. Both perform a logical Anti Semi Join. And yet many people will tell you that doing an Outer Join plus a WHERE clause is better – from a performance perspective. They will generally tell you that NOT EXISTS is logically equivalent.

We’ve seen plenty of times when the Query Optimizer will realise that two queries are identical and produce the same plan, but this doesn’t seem to be the case here.

This plan shows very much what we asked for. An Outer Join plus a Filter, as opposed to the Anti Semi Join.

And this has very little reason to be any faster than an Anti Semi Join operation. In fact, the interesting thing here is that the Outer Join completes. It tracks every successful match and passes that up to the Filter. Our Anti Semi Join operation doesn’t do that.

I see no reason to use an Outer Join plus Filter over NOT EXISTS. It may have been quicker at some point if the Anti Semi Join operation was done poorly in earlier versions, but I’d really like to hear if anyone can show me the Outer Join plus Filter method being quicker.

PS: This Saturday (Oct 8th) I’ll be presenting this kind of stuff in my “Deeper JOINs” session at SQL Saturday #92 in Portland, and in my pre-conference seminar a couple of days later (Monday 10th) in Seattle at the PASS Summit. You can still register for these events, so if you find this kind of thing interesting, get yourself over to the Pacific North West and I’ll see you there!