While reading some SQL Tuning-related documentation, I found this:
SELECT COUNT(*)
:
Is SELECT COUNT(*)
really that bad?
What's the proper way to verify the existence of a record?
This question is related to
sql
optimization
query-optimization
You can use:
SELECT COUNT(1) FROM MyTable WHERE ...
or
WHERE [NOT] EXISTS
( SELECT 1 FROM MyTable WHERE ... )
This will be more efficient than SELECT *
since you're simply selecting the value 1 for each row, rather than all the fields.
There's also a subtle difference between COUNT(*) and COUNT(column name):
COUNT(*)
will count all rows, including nullsCOUNT(column name)
will only count non null occurrences of column nameI would prefer not use Count function at all:
IF [NOT] EXISTS ( SELECT 1 FROM MyTable WHERE ... )
<do smth>
For example if you want to check if user exists before inserting it into the database the query can look like this:
IF NOT EXISTS ( SELECT 1 FROM Users WHERE FirstName = 'John' AND LastName = 'Smith' )
BEGIN
INSERT INTO Users (FirstName, LastName) VALUES ('John', 'Smith')
END
Other option:
SELECT CASE
WHEN EXISTS (
SELECT 1
FROM [MyTable] AS [MyRecord])
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END
You can use:
SELECT 1 FROM MyTable WHERE... LIMIT 1
Use select 1
to prevent the checking of unnecessary fields.
Use LIMIT 1
to prevent the checking of unnecessary rows.
The other answers are quite good, but it would also be useful to add LIMIT 1
(or the equivalent, to prevent the checking of unnecessary rows.
I'm using this way:
IIF(EXISTS (SELECT TOP 1 1
FROM Users
WHERE FirstName = 'John'), 1, 0) AS DoesJohnExist
You can use:
SELECT 1 FROM MyTable WHERE <MyCondition>
If there is no record matching the condition, the resulted recordset is empty.
SELECT COUNT(1) FROM MyTable WHERE ...
will loop thru all the records. This is the reason it is bad to use for record existence.
I would use
SELECT TOP 1 * FROM MyTable WHERE ...
After finding 1 record, it will terminate the loop.
Source: Stackoverflow.com