TIL: EXISTS SELECT 1 vs EXISTS SELECT * in SQL Server

EXISTS is a logical operator that checks if a subquery returns any rows. EXISTS works only with SELECT statements inside the subquery. Let’s see if there are any differences between EXISTS with SELECT * and SELECT 1.

There is no difference between EXISTS with SELECT * and SELECT 1. SQL Server generates similar execution plans in both scenarios. EXISTS returns true if the subquery returns one or more records, even if it returns NULL or 1/0.

Let’s use a local copy of the StackOverflow database to find users from Antartica who have left any comments. Yes, the same StackOverflow we use everyday to copy and paste code.

Let’s check how the execution plans look like when using SELECT * and SELECT 1 in the subquery with the EXISTS operator.

1. EXISTS with “SELECT *”

This is the query to find all users from Antartica who have commented anything. This query uses EXISTS with SELECT *.

SELECT *
FROM dbo.Users u
WHERE u.Location = 'Antartica'
AND EXISTS(SELECT * FROM dbo.Comments c WHERE u.Id = c.UserId);
--         ^^^^^^^^

To make things faster, let’s add one index on Location and another one on UserId on the dbo.Users and dbo.Comments tables, respectively.

CREATE INDEX UserId ON dbo.Comments(UserId);
CREATE INDEX Location ON dbo.Users(Location);
GO

Let’s check the execution plan. Notice the “Left Semi Join” operator and the other operators.

2. EXISTS with “SELECT 1”

Now, let’s change the subquery inside the EXISTS to use SELECT 1.

SELECT *
FROM dbo.Users u
WHERE u.Location = 'Antartica'
AND EXISTS(SELECT 1 FROM dbo.Comments c WHERE u.Id = c.UserId)
--         ^^^^^^^^

Again, let’s see the execution plan.

Voilà! Notice, there is no difference between the two execution plans when using EXISTS with SELECT * and SELECT 1. We don’t need to write SELECT TOP 1 1 inside our EXISTS subqueries. We can even rewrite our queries to use SELECT NULL or SELECT 1/0 without any division-by-zero error.

If you want to read more SQL and SQL Server content, check how to write Dynamic SQL and three differences between TRUNCATE and DELETE.

Happy SQL time!

TIL: How to compare DateTime without the time part in SQL Server

If you use DATEDIFF() or CAST() to filter a table by a DATETIME column using only the date part, there’s a better way. Let’s find it out.

To compare dates without the time part, don’t use the DATEDIFF() or any other function on both sides of the comparison in a WHERE clause. Instead, put CAST() on the parameter and compare using >= and < operators.

Let’s use a local copy of the StackOverflow database to find all user profiles created on a particular date.

Inside StackOverflow database, there’s dbo.Users table with a CreationDate column. Let’s use that column to find all users who created their profiles today.

Before we get started, let’s create an index on CreationDate to make things faster.

CREATE INDEX CreationDate ON dbo.Users(CreationDate);

Probably, we would write a query like this one,

SELECT * FROM dbo.Users
WHERE DATEDIFF(DAY, CreationDate, GETDATE()) = 0;

But, SQL Server has to scan the entire index. Notice the “Index Scan” operator in the execution plan. SQL Server doesn’t have any statistics on CreationDate wrapped in a DATEDIFF() function.

An Index Scan by itself in an execution plan isn’t good or bad. It depends on the number of rows read.

In this case, SQL Server read all the records on the dbo.Users table. When we hover over the row next to the “Index Scan” operator, we notice the number of rows read. It scanned the whole index, more than 2 millions of records.

Let’s stop using DATEDIFF() or CAST() to filter a table on a DATETIME column.

To filter a table on a DATETIME column comparing only the date part, use CAST() only around the parameter, and >= and < with the desired date and the day after.

SELECT * FROM dbo.Users
 WHERE CreationDate >= CAST(GETDATE() AS DATE)
   AND CreationDate < DATEADD(day, 1, CAST(GETDATE() AS DATE));

Voilà! That’s how to compare dates on the WHERE clauses. Don’t use DATEDIFF() or CAST() on both sides of the comparison. In general, don’t put functions around columns in the WHERE clause.

For more content about SQL Server, check the difference between EXISTS SELECT 1 and EXISTS SELECT *, T-SQL doesn’t have constants and variables aren’t a good idea, and how LIKE handle NULL values.

Source: Optimized date compare in WHERE clause

Show your work. Takeaways

Show your work is a New York Times bestseller by Austin Kleon. He describes his book as “a book for people who hate the very idea of self-promotion”. This book tells you how and why you should show your work online. These are my takeaways.

“Show your work” teaches that your work has to be out there. And, if your work isn’t online, it doesn’t exist. Good work isn’t enough.

“Be so good they can’t ignore you” summarizes the purpose of the book. To not be ignored, you have to be findable. Build a routine of sharing. Take advantage of your network. Imagine you don’t need a resume because your next boss already reads your blog.

“Be so good they can’t ignore you.”

-Steve Martin

You don’t have to be a genius

Creativity is about collaboration, too. Find a place where you can share your ideas and flourish your creativity. Find your creativity circle or “scenius”. Famous musicians and artists were surrounded by other artists to share, copy, acknowledge and sparkle their ideas.

Ask yourself what you want to learn. And make a commitment to learning in front of others. Share what you love, and the people who love the same thing will find you.

Share something small every day

There is no such a thing as overnight success. At the end of the day, see what you can share: inspiration, progress, or learning. Share what you think it’s helpful or entertaining.

Don’t be afraid of sharing your work. 90% of everything is crap. But don’t turn into human spam.

“Stop worrying, start sharing”

-From Show your work official trailer

Open up your cabinet of curiosities

Have a cabinet of curiosities. Before museums, people had a place to put what they loved, usually rare and weird things. Think of yourself as a collector.

Before you can share your work, you can share your taste. You can share what you read, who you follow, what inspires you. Credit your sources. Don’t share things you can’t credit.

Voilà! These are my takeaways from Show Your Work. This isn’t a programming book, but it has inspired me to continue writing, even when I think nobody is reading.

If you want to read other takeaways, check Clean Coder and Pragmatic Thinking and Learning.

#showyourwork

TIL: How to add gzip compression to ASP.NET Core API responses

Today, I got the report that one API endpoint took minutes to respond. It turned out that it returned hundreds of large complex objects. Those objects contained branding colors, copy text, and hotel configurations in a reservation system. This is how to add response compression in ASP.NET Core 6.0.

To compress responses with ASP.NET Core, register the default compression providers into the dependencies container with the UseResponseCompression() method.

Something like this,

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddResponseCompression();
//               ^^^^^

var app = builder.Build();
app.UseResponseCompression();
//  ^^^^^
app.MapControllers();
app.Run();

If we don’t specify any compression provider, ASP.NET Core uses a default one.

If we want gzip compression, then let’s register the GzipCompressionProvider inside AddResponseCompression() and set its compression level by configuring the GzipCompressionProviderOptions,

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();

builder.Services.AddResponseCompression(options =>
{
    options.Providers.Add<GzipCompressionProvider>();
    //                ^^^^^
});

builder.services.Configure<GzipCompressionProviderOptions>(options => 
{
    options.Level = CompressionLevel.Fastest;
    //      ^^^^^
});

var app = builder.Build();
app.UseResponseCompression();
// ^^^^^
app.MapControllers();
app.Run();

For my slow endpoint, the easiest solution to speed it up was mapping my huge complex object to a new view model that only contained the properties the client side needed. I rolled a simple extension method MapToMyNewSimplifiedViewModel() for that.

Voilà! That’s how to add gzip compression to responses with ASP.NET Core 6.0. That’s what I learned today.

UPDATE (Oct 2023): In previous versions of ASP.NET Core, we needed the Microsoft.AspNetCore.ResponseCompression NuGet package. It’s deprecated. ASP.NET Core has response compression built in now. We don’t need NuGet packages for this.

For more ASP.NET Core content, check how to read configuration values, how to create a caching layer, and how to use background services with Hangfire.

Source: Response compression in ASP.NET Core

Two free tools to format SQL queries

Do you need to format your SQL queries? Are you doing it by hand? Stop! There is a better way!

Instead of formatting SQL queries to follow code conventions by hand, we can use online tools or extensions inside Visual Studio, SQL Server Management Studio, or any other text editor.

These are two free tools to format SQL queries and store procedures. Inside Notepad++, use Poor Man’s T-SQL Formatter. And, ApexSQL Refactor for Visual Studio and SQL Server Management Studio.

Before

Before using Poor Man’s T-SQL Formatter and ApexSQL Refactor, I spent too much time formatting queries by hand. I mean making keywords uppercase, aligning columns, and arranging spaces.

I tried to use the “Find and Replace” option inside a text editor. But it only worked for making keywords uppercase. Sometimes, I ended up messing with variables, parameters, and other things inside my queries.

Things were worse with long store procedures. I changed two lines and ended up formatting thousand of lines. “Once you touch it, you’re the owner.”

Let’s format a sample query from StackOverflow

Let’s format the query to find StackOverflow posts with many “thank you” answers.

select
   ParentId as [Post Link],
   count(id)
from posts
where posttypeid = 2 and len(body) <= 200
  and (body like '%hank%')
group by parentid
having count(id) > 1
order by count(id) desc;

After formatting the query to follow Simon Holywell SQL Style Guide, it should look like this,

SELECT ParentId AS [Post Link]
     , COUNT(id)
  FROM posts
 WHERE posttypeid = 2
   AND LEN(body) <= 200
   AND (body LIKE '%hank%')
 GROUP BY parentid
HAVING COUNT(id) > 1
 ORDER BY COUNT(id) DESC;

Let’s see how these two tools format our sample query.

1. Poor Man’s T-SQL Formatter

Poor Man’s T-SQL Formatter is a free and open-source .NET and JavaScript library to format your SQL queries. It’s available for Notepad++, Visual Studio, SQL Server Management Studio, and others. We can try it online too.

This is how Poor Man’s T-SQL formatted our sample query in Notepad++.

It doesn’t make function names uppercase. Notice the functions len and count.

Also, it indents AND clauses in the WHERE clause. I want them right-aligned to the previous WHERE. But it’s a good starting point.

Sometimes, it needs a bit of help if the query has single-line comments in it with --.

By the way, it’s better to use /* */ for single-line comments inside our queries and store procedures. This makes formatting easier when we copy queries or statements from our database’s plan cache.

2. ApexSQL Refactor

ApexSQL Refactor is a free query formatter for Visual Studio and SQL Server Management Studio. It has over 160 formatting options. We can create our own formatting profiles and preview them. It comes with four built-in profiles. Also, we can try it online.

UPDATE (Sept 2023): ApexSQL Refactor isn’t freely available online anymore.

This is how ApexSQL Refactor formatted our sample query in Visual Studio 2019.

It isn’t perfect, either. But, it makes functions uppercase. Point for ApexSQL Refactor.

Also, it indents AND clauses in the WHERE too. I couldn’t find an option to change it. But, there is an option to indent ON in SELECT statements with JOIN. It affects ON for index creation too. We can live with that.

Voilà! Please let’s save some time formatting our SQL queries with any of these two free tools.

We can take a step further and call a formatter inside a Git hook to automatically format SQL files. I did it with Poor Man’s T-SQL formatter.

For more content, check my Visual Studio setup for C# and six tips to performance tune our SQL Server.

Happy SQL time!