It’s something you need to take care of in almost every ASP.NET Application. What if you would just like to know if that specific row exists or not, in SQL Server 2005 or SQL Server 2008. For example: Did this user vote on that topic already or not? There are different possibilities for that kind of situation. However, I wanted the fastet solution. If it’s about a query which looks like this:
SELECT somefield FROM sometable WHERE somefield = ‘somevalue’
In this case ‘somevalue’ is used as a literal key value, which also has an index running on it.
So if this query will always return either 0 or 1 rows, then – from an I/O point of view – using SELECT COUNT(*) or using EXISTS will be equally fast.
Why? Unfortunately SQL Server is not shortcutting an index seek if the value is being discovered in an intermediate index level. It also doesn’t shortcut it, if the value is out of range. So we always got a logical read for all index levels.
For the discussion COUNT(*) versus EXISTS, it does not matter whether the index on ‘somefield’ is clustered or not. However, the definition of the clustered index (if present) does affect performance. That’s about testing, you need to check out if the clustered on is faster or not. It mainly depends on the key size of the clustered index, the row size and the size of ‘somefield’. In theory, the fastest situation would be a clustered “and” nonclustered index on ‘somefield’. This will make the nonclustered index on ‘somefield’ the most shallow index possible, so the index seek on thisindex will use the least amount of I/O.
Finally handing over a resultset will be more costly than returning a return value.