How expensive is a SELECT that returns an empty result set? I'm analyzing the HTTP logs to one of our web services and I've noticed a lot of request to an endpoint such as /entities/0 which will end up effectively doing something like SELECT x, y, z FROM entities WHERE Id = 0, and I know for a fact there's no entity with Id = 0. 

There are approximately 600,000 requests to this endpoint in a 24hr period. Each request opens it's own connection to the SQL Server and executes the query which returns an empty result.

I'm wondering how bad this actually is. What overhead is associated on the SQL Server with having an open connection, and what overhead with repeatedly returning the empty result for the same query (WHERE Id = 0), does SQL Server cache and optimize for this?

We're using SQL Server 2012.
That is about 7 requests per second if the rate is flat over the 24 hour period, which shouldn't have a noticeable impact on a decently specced SQL Server machine. If they are far more bunched then they may be more of a problem.

I would be inclined to investigate where the requests are coming from. If they don't do anything useful then they may indicate a bug in the application.

SELECT &lt;something&gt; FROM &lt;table&gt; WHERE &lt;column&gt;=&lt;value_that_doesn't_exist&gt; is not going to tax the query planner or engine really, the work top bring up and tear down a connection will be more arduous to the SQL infrastructure than the work to lookup a cached query plan and read a couple of pages to see the row does not exist.

Assuming that the Id column is the table's clustering key there will be at most a couple of page reads and they will be from RAM as they are not ever going to leave the buffer cache due to how frequently they are accessed. If that column isn't the clustering key then add another page or two to those read (again, almost certainly from RAM not disk). If the column isn't indexed at all (I'd be surprised if that is the case given it is called "Id") then things are potentially more problematical as there will be a table scan involved.