SQL Server Blog

I’ve had so many arguments surrounding foreign keys and why they’re there, whether they can be ignored and avoided and removed etc. Personally I’m a fan for the simple fact that I like a proper database with proper referential integrity enforced. But for those who think that’s not important, there are other reasons too and this happens to be one of them:

Let’s go back to a previous post in which I covered Semi Joins… these are when we’re returning data from Table A when at least one matching record exists in Table B:

Yet another question from one of my training courses (a very useful and fun way to obtain blog topics) was surrounding the difference between the following and whether one or the other should be being used:

This is quite a fun episode of Fun With Flags because I like poking about inside the Optimizer to see what’s really going on. Doesn’t mean I can influence it… but it can be quite enlightening to have a poke about inside and see what bubbles up to the surface.

For example, I mentioned in my last post (about ANY / SOME) that the Optimizer sees all EXISTS / IN / ANY / SOME as a SOME operation internally… so where did I get that from?

This was something else that came up in training and I can’t, for the life of me, remember how and why, but we seemed to end up on the topic and this came out. I thought I’d write a little blog about them just because I was amused (doesn’t take much) by how few people knew these even existed in the T-SQL language.

So what are they? Well, they’re basically the same as EXISTS, just a different syntax:

This is related to my previous post about the Semi Join and came up under the same context. But this time we’re looking at the Anti Semi Join.

This was something which came up in one of my online courses recently… everyone was fine with all the main join types that appear in SQL Server both syntactical (inner join, left join, right join, full outer join) and internal (nested loops, merge, and hash), but a question arose surrounding some of the joins that you can only see listed on an execution plan and do not specify yourself.

In this article I’ll quickly cover one of those… Semi Join:

In an extension to my previous post about using CHECKPOINT in conjunction with DROPCLEANBUFFERS I thought I would give an example to highlight the situation.

This uses code from a previous post entitled “What’s Using The Buffer Pool?” which actually shows you the number of dirty and clean pages in the buffer pool but seemingly without explaining what that means… and since the last post touched upon this as well I figured I would knock up a quick and simple demo for you to run yourself.

Since my last post I’ve had a question emailed to me which I just wanted to take a second to clarify as it’s a very good question and one that, I imagine, a lot of people have had or might not fully realise.

Everyone I speak to these days seem to know that it’s best practice to clear the buffer pool before running tests on query performance, and that to do so we run the following:

This is a follow-up to a previous blog post entitled “Which Table or Index is Hogging the Buffer Pool?”… inventive and exciting title, I know…

What we did in that post was to have a look through the Buffer Pool in SQL Server in order to find what was hogging our most precious resource. This is great to find exactly which table, and which index, is causing us problems, but we need one further crucial step… we need to know WHY that table / index is being pulled into the Buffer Pool in the first place.