What’s the best to check if item exist or not: Select Count(ID)OR Exist(…)?

by

EXISTS, always

  • COUNT will traverse the table or an index: you asked for a COUNT
  • EXISTS will stop as soon as it finds a row

Edit, to be clear

Of course, in this case if the email column is unique and indexed it will be close.

Generally, EXISTS will use less resources and is more correct too. You are looking for existence of a row, not “more than zero” even if they are the same

Edit2: In the EXISTS, you can use NULL, 1, ID, or even 1/0: it isn’t checked…

21 May 2011 edit:

It looks like this was optimised in SQL Server 2005+ so COUNT is now the same as EXISTS in this case

More Related Contents:

  1. Can an INNER JOIN offer better performance than EXISTS
  2. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?
  3. Insert results of a stored procedure into a temporary table
  4. How do I obtain a Query Execution Plan in SQL Server?
  5. When should I use CROSS APPLY over INNER JOIN?
  6. SQL JOIN vs IN performance?
  7. How important is the order of columns in indexes?
  8. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  9. Convert Xml to Table SQL Server
  10. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  11. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  12. Is it possible to select sql server data using column ordinal position
  13. How do I group on continuous ranges
  14. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  15. Dynamic SQL to generate column names?
  16. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  17. Extracting hours from a DateTime (SQL Server 2005)
  18. Column name or number of supplied values does not match table definition
  19. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  20. How to split string using delimiter char using T-SQL?
  21. How to find consecutive rows based on the value of a column?
  22. SQL server select distinct rows using most recent value only
  23. How to escape square brackets inside square brackets for field name
  24. How do you copy a record in a SQL table but swap out the unique id of the new row?
  25. Average of multiple columns
  26. Eliminate and reduce overlapping date ranges
  27. Cumulative Total in MS Sql server [duplicate]
  28. Memory effective way to read BLOB data in C#/SQL 2005
  29. Distributed transaction error?
  30. Determine what user created objects in SQL Server

Leave a Comment