Tag Archives: SQL Server

Why Your SQL Query Runs Backwards (And Why It Matters)

Leave a reply

“Order and simplification are the first steps toward mastery of a subject — the actual enemy is the unknown.”

Thomas Mann

As a new SQL developer, do you remember being frustrated that you couldn’t use the alias of a COUNT(*) in the predicate/WHERE clause, but you could in the ORDER BY? Or have you been confused on when you should put something in the ON clause vs the WHERE clause vs the HAVING clause? This article will help clear that all up and it all has to do with how SQL Server processes your query.

Why SQL Isn’t Like Other Languages

Most developers are used to writing code top to bottom and expecting the computer to execute that code in the same order. However, unlike those procedural languages, SQL is a declarative language. In a procedural language, you give the computer specific set of directions telling it how to do tasks. In a declarative language, you give the computer what you want it to return and it determines how it will do that (using the Query Optimizer). This often confuses first time SQL developers; especially when they believe SQL Server is doing all the tasks from the top down in the query. In fact, it may even surprise many seasoned SQL Developers to learn that SQL Server actually starts in the middle of your query.

*Many thanks to Itzik Ben-Gan and his books for helping solidify this in my brain several years ago! I highly recommend reading them if you want to go even deeper into this.*

TL;DR:
The T-SQL Logical Processing Order:

  • FROM
  • JOIN
  • ON
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • DISTINCT
  • ORDER BY
  • TOP

Lets take this query as an example for our discussion:

SELECT TOP 50 e.EmployeeName Name, DATEDIFF(Year, e.StartDate, GETDATE()) YearsEmployed, SUM(s.SalesAmt) Total_SalesAmt
FROM dbo.EmpTable e
LEFT JOIN dbo.SalesTable s
ON e.EmpID = S.EmpID
AND s.Type = 'Software'
WHERE e.Region = 'North'
GROUP BY e.EmployeeName, DATEDIFF(Year, e.StartDate, GETDATE())
HAVING SUM(s.SalesAmt) >= 1000
ORDER BY YearsEmployed DESC, SUM(s.SalesAmt) DESC, e.EmployeeName

Most people who write SQL write it from the top down starting with SELECT (maybe leaving that with a * until you decide what fields you want to put in there), moving on to FROM, the JOIN(s), a WHERE clause, a GROUP BY, the HAVING, and finally the ORDER BY.

The Logical Order vs. The Written Order

However, SQL Server actually reads/processes the query in this logical order: FROM, JOIN(s), ON, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally TOP.

It is important to note here we are talking about the Logical processing order. This is how SQL Server is going to read your query to make sense of it. It will then pass this information on over to have a query plan created which will be the Physical processing order, which is how it will actually gather the data from the tables and return the results.

A Step-by-Step Walkthrough of Query Processing

SQL Server starts with the raw tables themselves , and filters down the rows before it gets to the specific columns you want returned. This can have a significant impact on how and what you can put in your different sections of the query.

Phase 1: FROM – Gathering the Raw Data

In the first phase, SQL Server pulls back all records and all rows from the table after the FROM clause. Note: It does not limit to any specific rows (because at this point it does not know any predicate information) nor does it limit to any specific columns (because at this point it does not know any columns you want returned in your SELECT clause).

(1) FROM dbo.EmpTable e

Below you see what we’ll call ResultSet1 from Phase 1 (the … represents all the other fields in this table, you’ll see the same for the next several phases). The FROM clause is one of two mandatory clause that every query must have.

Phase 2 & 3: JOIN and ON – Combining and Filtering Tables

The second phase does your join(s). At this point SQL Server does a cartesian join between ResultSet1 from above to your tables in the join (in this case the dbo.SalesTable). This is a cartesian join (every row from ResultSet1 is matched to every row from your table), because SQL Server has not read any of the ON criteria yet.

As a side note: This phase also includes APPLY, PIVOT, and UNPIVOT.

(1) FROM dbo.EmpTable e
(2) LEFT JOIN dbo.SalesTable s

Lets say below is the first few rows of the dbo.SalesTable:

Below is what the ResultSet2 would look like after Phase 2 with the cartesian join:

Next in Phase 3, we would apply the ON clause of the join. This would filter our cartesian join based on whatever criteria we may have in this clause.

(1) FROM dbo.EmpTable e
(2) LEFT JOIN dbo.SalesTable s
(3) ON e.EmpID = S.EmpID
AND s.Type = ‘Software’

In our example, this will remove the “extra joins” of the cartesian join (where the EmpIDs don’t match). It will also only keep rows where the type is Software. As you can see below, this means we have both SalesTable rows for Fred since they are Software, but then for Bob and Gloria we see nothing from SalesTable. But we do still see their data from the EmpTable since we have a LEFT JOIN.

At this point, SQL Server would repeat Phases 2 and 3 for each table you are joining on. In our example, though, we have no more tables.

Phase 4: WHERE – Applying the First Filters

Now we move on to Phase 4 which is the WHERE criteria. This is where many developers get confused. In an INNER JOIN, the ON clause and the WHERE clause can be used interchangeably. However, on LEFT or RIGHT JOINs, your WHERE clause could actually change it into an INNER JOIN. For instance, if you were to move the s.Type = ‘Software’ to the where clause, that would in essence make it logically into an INNER JOIN.

In our example, we will lose Gloria because she was in the South region.

(1) FROM dbo.EmpTable e
(2) LEFT JOIN dbo.SalesTable s
(3) ON e.EmpID = S.EmpID
AND s.Type = ‘Software’
(4) WHERE e.Region = ‘North’

Slight detour: Why Can’t I Use a SELECT Alias in my WHERE Clause?

Also, this is a good place to stop and answer one of the more confusing things for new SQL Developers: Why can’t I use an alias from my SELECT clause in the WHERE predicate? The phases so far show you why you can’t. So far all the SQL Server has seen when logically processing your query is the FROM, JOIN, ON, and WHERE clauses. It has not even looked at SELECT yet. This is why your query will fail if you try to put your column aliases in the WHERE predicate.

Phase 5: GROUP BY – Aggregating the Data

For the next one, Phase 5, we bring in the GROUP BY clause. This is where it gets kind of weird, so stick with me. It brings the last result set from Phase 4 and it tacks onto that all the fields you are looking to group by. But it still keeps this at the row level data you have seen so far, including all other columns (because SQL Server has not yet looked at what fields are needed for any of the other clauses, so it still needs to keep all the fields under consideration).

(1) FROM dbo.EmpTable e
(2) LEFT JOIN dbo.SalesTable s
(3) ON e.EmpID = S.EmpID
AND s.Type = ‘Software’
(4) WHERE e.Region = ‘North’
(5) GROUP BY e.EmployeeName, DATEDIFF(Year, e.StartDate, GETDATE())

Notice how the two grouping fields at the start of the rows for Fred Smith are spanning the two “raw” data fields following them. This is starting to show how we are going to group values together in the final results. This also is starting to tell subsequent phases what will and what will not be allowed in those phases. For instance, it’s telling SQL Server that you cannot put Region in the SELECT clause, because we are not grouping by it (unless you are doing an aggregation on Region like MIN/MAX, of course). All the other non-grouped fields will still be eligible (which is why this phase’s result set still includes them, but they will only be eligible for certain parts of those phases.

This is another good place to stop and show how a DISTINCT and a GROUP BY are for all intents and purposes the same and you will often get the same execution plan if you group by all the same fields you are using a DISTINCT keyword on. The difference, though, is in the intent: GROUP BY is meant to the allow you to do some sort of aggregation when you get to the next Phases and DISTINCT is meant to remove any duplicates. However, they can generate the same execution plan depending on how they are written.

Phase 6: HAVING – Filtering the Groups

We are over half way done now with Phase 6, which is where SQL Server looks at the HAVING clause. This is typically where you put any aggregation predicates, but you can still include any raw field level criteria if you want/need as well. Just remember, at this point you have already performed your group by, so any criteria you put in the HAVING clause is going to be at that level.

(1) FROM dbo.EmpTable e
(2) LEFT JOIN dbo.SalesTable s
(3) ON e.EmpID = S.EmpID
AND s.Type = ‘Software’
(4) WHERE e.Region = ‘North’
(5) GROUP BY e.EmployeeName, DATEDIFF(Year, e.StartDate, GETDATE())
(6) HAVING SUM(s.SalesAmt) >= 1000

In our example, Bob’s record is going to drop off because he has no sales info. This is obviously a very simplified example, but you could see how it could still have many other records at this point. Note that we do not see any columns with that summed value of 1100 for Fred. This is because SQL Server does not necessarily need it. You can have fields/calculations in your HAVING that never actually make it to be presented to the user.

Phase 7: SELECT – Choosing the Final Columns

Now we finally get to what many new SQL developers think is what SQL Server first considers; Phase 7 brings in the SELECT clause. This is where SQL Server will finally get rid of any extra fields that are not needed.

The SELECT clause is where you can give any fields or calculations an alias. And that is also why you can use aliases finally in the ORDER BY but you can’t before this phase because SQL Server hasn’t seen the alias yet. However, now it has and you can use it in the subsequent phase.

As a side note, while our example does not include them, this is also where any window functions (ROW_NUMBER(), LEAD(), LAG(), etc) would be evaluated. This also explains why you can’t include them in your WHERE clause, because they haven’t even been read yet at that point.

(7) SELECT TOP 50 e.EmployeeName Name, DATEDIFF(Year, e.StartDate, GETDATE()) YearsEmployed, SUM(s.SalesAmt) Total_SalesAmt
(1) FROM dbo.EmpTable e
(2) LEFT JOIN dbo.SalesTable s
(3) ON e.EmpID = S.EmpID
AND s.Type = ‘Software’
(4) WHERE e.Region = ‘North’
(5) GROUP BY e.EmployeeName, DATEDIFF(Year, e.StartDate, GETDATE())
(6) HAVING SUM(s.SalesAmt) >= 1000

Note above: the TOP 50 part of the query is not bolded, because that is part of another phase yet to come.

Phase 8: DISTINCT – Removing Duplicates

We are almost done with the clauses as we come up to Phase 8: the DISTINCT clause. In our example we do not have a DISTINCT clause, but this is where you can remove duplicates based on all fields in your SELECT clause. So if you had something like below from Phase 7, where there were two Jane Doe’s, both started the same year and both had the same total sales amount:

If we had a distinct on this, then it would return only one row:

As an important aside here: Do not use DISTINCT as a quick fix to remove duplicates. If you do not expect duplicates, then investigate why. There may be something wrong with your joins to cause a bit of a cartesian join somewhere which is duplicating rows by mistake. Distinct is very resource intensive, especially if you have a ton of rows you have to look at to see if there are duplicates. You are much better off fixing the query if it’s causing the duplicates.

Phase 9: ORDER BY – Sorting the Results

We move back down to the bottom of the query in Phase 9 with the ORDER BY clause. SQL Server does not ever guarantee the order of your results. Even if you are running a simple SELECT * from dbo.Sometable where that Sometable has a clustered index. The only way to guarantee your results are in a specific order is to use an ORDER BY clause.

(7) SELECT TOP 50 e.EmployeeName Name, DATEDIFF(Year, e.StartDate, GETDATE()) YearsEmployed, SUM(s.SalesAmt) Total_SalesAmt
(1) FROM dbo.EmpTable e
(2) LEFT JOIN dbo.SalesTable s
(3) ON e.EmpID = S.EmpID
AND s.Type = ‘Software’
(4) WHERE e.Region = ‘North’
(5) GROUP BY e.EmployeeName, DATEDIFF(Year, e.StartDate, GETDATE())
(6) HAVING SUM(s.SalesAmt) >= 1000
(9) ORDER BY YearsEmployed DESC, SUM(s.SalesAmt) DESC, e.EmployeeName

As you can see above, you can use the alias (YearsEmployed) or you can use the actual calculation (SUM(s.SalesAmt)), or the real field name even if it’s aliased (e.EmployeeName). In fact, you could even use the ordinal value of the field in your select statement if you wanted (eg, ORDER BY 2). WARNING: Anyone having to review your query is likely going to be cursing your name in the future! Plus, if you change the order of your SELECT clause in the future, you will need to remember to change the ordinal value, as well.

Phase 10: TOP – Limiting the Final Rows

FInally, we are at the last one, Phase 10 — the TOP clause. This is what will limit your query results to a maximum number of records. You can even add the keyword PERCENT after the number to get the top x-percent of the results (eg, TOP 10 PERCENT). If you have the result set ordered, like we do in this query, you’ll get that TOP x-number/percent back in that same order. If you did not use an ORDER BY clause, then you will just get the first x-number/percent records back.

(7 and 10) SELECT TOP 50 e.EmployeeName Name, DATEDIFF(Year, e.StartDate, GETDATE()) YearsEmployed, SUM(s.SalesAmt) Total_SalesAmt
(1) FROM dbo.EmpTable e
(2) LEFT JOIN dbo.SalesTable s
(3) ON e.EmpID = S.EmpID
AND s.Type = ‘Software’
(4) WHERE e.Region = ‘North’
(5) GROUP BY e.EmployeeName, DATEDIFF(Year, e.StartDate, GETDATE())
(6) HAVING SUM(s.SalesAmt) >= 1000
(9) ORDER BY YearsEmployed DESC, SUM(s.SalesAmt) DESC, e.EmployeeName

Logical vs. Physical Processing: What’s Next?

Remember, this is just how SQL Server will logically process this query; this is not how it will physically process it. That will be determined by the query optimizer and will involve any number of operators depending on how SQL Server thinks it will best be able to get you the data.

SELECT (Transact-SQL) – Microsoft Learn If you want to read the official word from the source, the Microsoft documentation for the SELECT statement covers this logical processing order in detail.

Itzik Ben-Gan’s Simple Talk Articles As I mentioned, Itzik Ben-Gan’s books were a huge help for me. You can find more of his articles and resources over at his Simple Talk author page.

Before reading this, did you realize that SELECT happens so late in the process (Phase 7)? How does this change how you view your aliases?

An Underutilized Secret Weapon for Faster Queries: The SQL Filtered Index

Leave a reply

“Efficiency is doing things right; effectiveness is doing the right things.”

Peter Drucker

First, a Quick Refresher: “Normal” Indexes

When storing and retrieving data in MS SQL Server, you have two options: Clustered and Nonclustered indexes. A clustered index is your table in a specific order (Think of the old-timey phonebooks). A nonclustered index is typically a smaller subset of fields of the entire table, usually ordered on a different field than your clustered index (Think the index in the back of a textbook). However, both indexes, by default, consist of one record for each row in your table.

Continue reading →

Handling the “Unknown”: 3 Ways SQL NULL Will Break Your Query

Leave a reply

“There are known knowns… there are known unknowns… but there are also unknown unknowns—the ones we don’t know we don’t know.”

Donald Rumsfeld, former U.S. Secretary of Defense.

Have you ever written a query like below and it returned zero rows?

SELECT SomethingA, AnotherB, Value
FROM ATable
WHERE Value = NULL

If so, this post is for you! We’ll cover that and a few other gotchas and misconceptions of NULL that new SQL developers may not be aware exist. And even some seasoned developers forget from time to time!

NULL is not an actual value. It’s not like zero (0) or an empty string (”). It’s a state or a marker that signifies nothing actually exists here. It’s akin to unknown, missing, or not applicable.

SQL NULL Gotcha #1: Comparisons in the WHERE Clause (IS NULL)

In a where predicate, NULLs won’t work with operators like “=”, “<>”, or “!=”. This is because you can’t compare something that has a value to something that doesn’t have a value. You can’t even compare two NULL values like this.

Instead, you want to use IS NULL and IS NOT NULL. Back to the original query in this post, this is how you’d write that. Now you’ll get all the records where Value is actually a NULL value.

SELECT SomethingA, AnotherB, Value
FROM ATable
WHERE Value IS NULL

SQL NULL Gotcha #2: Expressions and Concatenation (COALESCE & ISNULL)

When you add or concatenate a NULL value with a non-NULL value, you get a NULL value. Here is an example of this in action:

DECLARE @a AS INT 
DECLARE @b AS INT = 10
DECLARE @c AS VARCHAR(50) = 'Hello'
DECLARE @d AS VARCHAR(50)

SELECT @a + @b Adding, 
@c + @d Concatenating

Instead, you should use ISNULL() or COALESCE() to properly handle NULLs in this fashion. ISNULL() evaluates the first parameter to see if it’s NULL, if it is, then it will return the second parameter. However, if the second parameter is also NULL then it will return that NULL value. That’s where COALESCE() comes in handy: It can accept any number of parameters (up to the max allowed parameters). It’ll continue to look at each parameter until it finds a non-NULL value. But if the last one is also NULL, then it’ll return NULL. That’s the power of COALESCE(): it will continue to look at each parameter until it finds the first non-NULL value. Which is why you typically want your last parameter to be something that can’t be NULL.

***NOTE: If you are looking to use an index on a field you are putting inside any function, including these two, you will NOT be able to utilize that index to do seeks.***

Here is the same example above with the correct solution with an example of ISNULL() and COALESCE():

DECLARE @a AS INT 
DECLARE @b AS INT = 10
DECLARE @c AS VARCHAR(50) = 'Hello'
DECLARE @d AS VARCHAR(50)

SELECT ISNULL(@a, 0) + @b Adding, 
@c + COALESCE(@d, '') Concatenating

SQL NULL Gotcha #3: How Aggregate Functions (SUM, AVG, COUNT, etc) Handle NULLs

When it comes to built-in system aggregate functions (like SUM(), COUNT(), MIN(), and AVG()), they completely ignore NULL values. This is typically fine for most functions. For instance, when you want to SUM your values you don’t really care about NULL values; same goes MAX. However, for AVG or COUNT, if you have a NULL value you may not get what you expect.

CREATE TABLE #temp (nbr INT)

INSERT INTO #temp
VALUES (20), (10), (NULL), (5), (5)

SELECT SUM(nbr) sum_nbr, 
       MIN(nbr) min_nbr, 
       MAX(nbr) max_nbr, 
       AVG(nbr) avg_nbr, 
       COUNT(nbr) count_nbr
FROM #temp

In this code above, we entered 5 values in the table that total 40, but the count is only returning 4. And the average the business is expecting to see is 8 (40 divided by 5 rows), not 10. And finally, most perplexing, they are expecting to see zero (the NULL) as the minimum value, but the query is returning 5. So just like the previous gotcha, we should use a function like ISNULL() to force NULL values to zero if that is how the business wants to treat NULL values.

For COUNT(), you can also use * instead of your NULLable field to get an accurate count for all rows. Whereas we see above putting a NULLable field in the count function will potentially give you counts you don’t necessarily want.

Here is the alternate query showing how the business wants to see the data represented:

CREATE TABLE #temp (nbr INT)

INSERT INTO #temp
VALUES (20), (10), (NULL), (5), (5)

SELECT SUM(ISNULL(nbr, 0)) sum_nbr, 
       MIN(ISNULL(nbr, 0)) min_nbr, 
       MAX(ISNULL(nbr, 0)) max_nbr, 
       AVG(ISNULL(nbr, 0)) avg_nbr, 
       COUNT(*) count_nbr
FROM #temp

And yes, before you mention it, the better approach is to not allow NULLs if the business does not want to include that here. This was just an example of something that could happen in real life, because data is not always clean.

A Note: When Ignoring NULLs is the Correct Choice

However, you don’t ALWAYS want to force some value in for NULL values. There are times you truly want to not include NULL values in any of what we have talked about above. Then in those circumstances, by all means, please let SQL treat NULL like it does by default. There is nothing wrong with this if that is what the query calls for.

Conclusion: Key Takeaways for Handling SQL NULLs

SQL NULLs are one of those tough topics for new developers to get their heads around. It’s like back in math when you tried to learn about imaginary numbers. But don’t worry, even seasoned SQL developers get caught by this one every once in awhile.

Just remember:

  • NULL is not a true value (like 0 or an empty string), but rather the state of something being unknown.
  • When testing for something being NULL or not, always use IS NULL or IS NOT NULL.
  • Arithmetic or concatenation involving a NULL value will always result in a NULL value. Use ISNULL() or COALESCE().
  • Using aggregate functions (like SUM, AVG, or COUNT) will ignore NULL values.

Which SQL NULL gotcha has tripped you up the most in the past? Add it to the comments below!

Here are 5 official Microsoft docs in case you want to dig deeper on the subject of NULLs:

  1. NULL and UNKNOWN (Transact-SQL)
    • This is the foundational document explaining the three-valued logic (TRUE, FALSE, UNKNOWN) that NULL introduces.
  2. ISNULL (Transact-SQL)
    • The official syntax and examples for the ISNULL function you mentioned.
  3. COALESCE (Transact-SQL)
    • The official documentation for COALESCE, which also includes a good section comparing it directly to ISNULL.
  4. Aggregate Functions (Transact-SQL)
    • This page confirms your point: “Except for COUNT(*), aggregate functions ignore null values.”
  5. SET ANSI_NULLS (Transact-SQL)
    • This is a more advanced (but crucial) topic. It explains the database setting that controls whether = NULL comparisons evaluate to UNKNOWN (the default) or TRUE.

Skip the Full Snapshot: Add a Single Article to SQL Replication Fast!

Leave a reply

“I will always choose a lazy person to do a difficult job because a lazy person will find an easy way to do it.”

Bill Gates, Founder Microsoft

Lets imagine you have a multi-terabyte database you are replicating. Now let’s say you either need to add in a new table into that subscription or heaven forbid something went catastrophically wrong and you need to reinitialize one of the tables that already exists in the subscription. Sounds like it’s time to buckle up and wait for a while until that snapshot is taken just for that one article to be applied on the subscriber side, right? Wrong!

What if I told you there was a faster way? In this post we’ll walk through the steps to make this happen including how to watch the action as it’s happening from publisher to subscriber.

ALWAYS TEST IN NON-PRODUCTION FIRST!!!!

The “Full Snapshot” Headache

Performing a full snapshot will not only take a long time, but it’ll use a lot of resources and potentially cause blocking on unaffected tables that have nothing to do with the table you really want to get replicated.

The Fast Way: Add an Article Without a Full Snapshot

  1. (If needed) Cleanup: If the scenario is you need to fix a broken replication of just one table, remove that article from your subscription and then continue on. If you are looking to just add a new table into your replication, skip this step and move on to the next.
  2. Change Publication Settings: There are two config settings that will allow you to bypass the need for a full snapshot to be taken – allow_anonymous and immediate_sync. Below are the commands you’ll want to run to change both of these values to false.
USE [YourPublisherDatabase]

EXEC sp_changepublication 
    @publication = N'YourPublicationName', 
    @property = N'allow_anonymous', 
    @value = 'false'

EXEC sp_changepublication 
    @publication = N'YourPublicationName', 
    @property = N'immediate_sync', 
    @value = 'false'
  1. Add Your Article: Once you run those commands, you can add the article to the subscription.

Watch it Work in Replication Monitor

Then the easiest way to watch everything progress is to use the Replication Monitor. Once you have the monitor open for the publisher server, navigate to the publication you are working with and open the Agents tab.

  1. Run the Snapshot Agent: In the Agents tab, Start the snapshot agent. This will kick off a new snapshot, but if you right click and go to View Details on the agent you will see it only snapshot the new article.
  2. Verify: Once that completes, you’ll want to immediately back out to the publication level again and go to the All Subscriptions tab. Then right click on the subscription and go to the View Details. In the Distributor to Subscriber History tab, you can watch that new article get copied to the subscriber.

    Don’t Forget to Clean Up!

    Revert Settings: Once done, all you need to do is clean up the configurations again. Go back to the publisher and run these commands to change both values to true again (note it’s the opposite order as you ran it last time).

    USE [YourPublisherDatabase]

EXEC sp_changepublication 
    @publication = N'YourPublicationName', 
    @property = N'immediate_sync', 
    @value = 'true'

EXEC sp_changepublication 
    @publication = N'YourPublicationName', 
    @property = N'allow_anonymous', 
    @value = 'true'

    Much quicker than doing a full snapshot, right?! All that was required was to change those two configurations in the subscription options and then do your normal steps after that. By doing these few extra steps at the beginning you will save tons of resources and time when you want to add a single article into an existing subscription.

    But as always, be sure to always test this in nonproduction first!!!

    Many thanks to Ragnar Skalvik (DBA Architect) for coming up with this working solution for our team at work! His assistance and tenacity at finding solutions is immeasurable!

    Continued learning…

    Transactional Replication
    If you’re a bit new to the whole setup or need to explain it to your boss, this is the main “what is transactional replication?” page. It gives you the full 10,000-foot view of the technology.

    sp_changepublication (Transact-SQL)
    This is the official page for the main stored procedure we’re using. It’ll show you every single property you can change, but the ones we care about are allow_anonymous and immediate_sync.

    What other “replication tricks” have you learned over the years to save time and resources? Put your tips in the comments below!

    The TOP (N) Trick: Short Circuit Your SQL Server Counts for Massive Speed Gains

    Leave a reply

    “It is not enough to be busy… The question is: What are we busy about?” – Henry David Thoreau (Poet and Philosopher)

    Have you ever had to write a query to check some threshold count of records and then do some action based on that?

    For instance, let’s say your reporting website shows a red indicator when you have more than 10,000 orders of a specific category type in the last 30 days. Otherwise, that indicator is green. You don’t care if there is only one more than your limit or one million more.

    The Obvious (and Slow) Solution: SELECT COUNT(*)

    The obvious first answer is to do a full count of everything that matches that criteria:

    SELECT COUNT(*) Cnt
FROM Sales.Orders
WHERE CategoryID = 5
  AND CreateDt >= GETDATE()-30;

    Lets say this gives us 156,000 records, which is well above the threshold of 10,000. But look at the cost: it produced 12,080 reads and took 380ms (which you can see using SET STATISTICS IO ON and SET STATISTICS TIME ON). This is on a query that is already using an index and whose stats are up to date.

    This query is so slow because it must count every single matching row, even after we have easily counted past our 10,000 record threshold. But how can we short circuit this counting and stop at our threshold?

    The Fast Solution: Short-Circuiting with TOP (N)

    I’m sure you have all used SELECT TOP (N) at some point to just get a sample of what a table looks like. This same logic can help us short circuit our counting. What if we just asked SQL for the top 10,000 records and then counted that result?

    We can do this with an inline subquery:

    SELECT COUNT(*) Cnt
FROM (
    SELECT TOP (10000) ID
    FROM Sales.Orders
    WHERE CategoryID = 5
      AND CreateDt >= GETDATE()-30
    ) AS r;

    This query now only does 54 reads (0.4% of the original) and completes in 12ms (3% of the original).

    It works because the inner query (SELECT TOP (10000)) stops as soon as it finds 10,000 matching records. The outer query then just counts the results of the inner query. If the count is 10,000, we know we have at least that many. If it’s less (for example 8,500), we know we’re under the threshold.

    The Takeaway

    So don’t forget: if you don’t really care about the total count, but just that it’s above some limit, don’t waste resources and time doing a full COUNT(*). Simply put a short circuit into your query with TOP (N) so it stops counting at your given threshold!

    Do you know of any other methods in SQL Server to solve this “at least N rows” problem? Share them in the comments!