Ways To Check For Non-Existence

Brent Ozar shows two methods for finding records missing associated child records:

You’re writing a query, and you wanna check to see if rows exist in a table.

I’m using the free Stack Overflow database, and I wanna find all of the users who have not left a comment. The tables involved are:

  • In dbo.Users, the Id field uniquely identifies a user.
  • In dbo.Comments, there’s a UserId field that links to who left the comment.

A quick way to write it is:

And this works fine. When you read the query, you might think SQL Server would run that SELECT * FROM dbo.Comments query for every single row of the Users table – but it’s way smarter than that, bucko. It scans the Comments index first because it’s much larger, and then joins that to the Users table.

But this isn’t the only way to query these tables, and Brent shows how to tell which method works better.

Related Posts

Table Variables and Parallelism

Erik Darling shows off a neat trick for inserting with parallelism into a table variable: One of the many current downsides of @table variables is that modifying them inhibits parallelism, which is a problem #temp tables don’t have. While updating and deleting from @table variables is fairly rare (I’ve seen it, but not too often), you at minimum need an insert […]

Read More

Refreshing Views After DDL Changes

Eduardo Pivaral shows how you can refresh the metadata for a view in SQL Server after one of its underlying tables or functions changes: So we proceed to execute an alter view over the first view: ALTER VIEW dbo.[vi_invoices_received_by]ASSELECT ConfirmedReceivedBy as [Received by], COUNT(InvoiceID) as [# of Invoices], CustomerIDFROM Sales.InvoicesGROUP BY ConfirmedReceivedBy, CustomerID;GO So we […]

Read More

Categories

August 2018
MTWTFSS
« Jul Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031