SQL Server - ignoring always true condition
Mateusz
I need to add to the WHERE clause one condition, which is always true, but it must reference one of the columns, for example:
ID is the primary key (and therefore NOT NULL)
and I will execute a select:
SELECT *
FROM Table
WHERE ID IS NOT NULL
Will this condition be ignored or checked for every row?
Martin Smith
SQL Server can determine at compile time that this condition will always be true and avoid the need to check at runtime.
CREATE TABLE #T
(
ID INT CONSTRAINT PK_ID PRIMARY KEY NONCLUSTERED,
X INT CONSTRAINT UQ_X UNIQUE
)
SELECT X
FROM #T
WHERE ID IS NOT NULL;
DROP TABLE #T
In the execution plan above the only index accessed is UQ_X and this doesn't even contain the ID column that would make such a runtime evaluation possible.
By contrast if ID is nullable (and replaced with a unique constraint rather than primary key as a PK wouldn't allow NULL) then the check would of course need to be made at run time and so the plan will need to retrieve the column and might look like one of the following.
Scans whole table with predicate pushed into scan
Attempt to use narrower index requires lookup to retrieve the column and evaluate the predicate
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: Multiple markers on google maps with different icons?
- Next: jsf - How can i get inputText value in managed bean?
Related
Related Related
- 1
SQL Server - ignoring always true condition
- 2
if condition always evaluating true
- 3
Term for an if condition that is always true
- 4
SQL Server : Insert into Where Condition is True
- 5
My Method always return 0 ignoring the condition
- 6
if condition always returns true (Java)
- 7
Why is this PHP if condition always true?
- 8
Why is this PHP if condition always true?
- 9
awk condition always TRUE in a loop
- 10
Bash Script if condition is always true
- 11
Condition always true when reached in while loop
- 12
Why does my condition always evaluate as true?
- 13
Compiler: What if condition is always true / false
- 14
Python - True part of if condition always executes
- 15
C++ GUI Condition for if statement always true
- 16
WIX Operating System Condition Always true
- 17
Why a condition that contains a pointer always returns true?
- 18
Why is my "if" condition (string comparison) always true?
- 19
SQL - only execute 'and' if a condition is true
- 20
SQL Filter rows if condition is true
- 21
Search index in SQL Server ignoring special characters
- 22
Search index in SQL Server ignoring special characters
- 23
IN Condition Issue in SQL Server
- 24
select with condition -Sql server
- 25
SQL-Procedures: NOT EXISTS seems to be always true
- 26
Intellij Idea hint: Condition is always false - can that be true here? (Java)
- 27
Boolean condition is always false when using `status == true`
- 28
Typescript error This condition will always return 'true' since the types have no overlap
- 29
if condition in try block always turns out true even if it shouldn´t
Comments