Fastest way to determine if record exists
EXISTS (or NOT EXISTS) is specially designed for checking if something exists and therefore should be (and is) the best option. It will halt on the first row that matches so it does not require a TOP clause and it does not actually select any data so there is no overhead in size of columns. You can safely use SELECT * here - no different than SELECT 1, SELECT NULL or SELECT AnyColumn... (you can even use an invalid expression like SELECT 1/0 and it will not break).
IF EXISTS (SELECT * FROM Products WHERE id = ?)BEGIN--do what you need if existsENDELSEBEGIN--do what needs to be done if notEND
SELECT TOP 1 products.id FROM products WHERE products.id = ?; will outperform all of your suggestions as it will terminate execution after it finds the first record.
Nothing can beat -
SELECT TOP 1 1 FROM products WHERE id = 'some value';You don't need to count to know if there is a data in table. And don't use alias when not necessary.