I received a comment asking about this, so I’ll write a short note on the subject.
The QP will take and expand all *’s early in the pipeline and bind them to objects (in this case, the list of columns). It will then remove unneeded columns due to the nature of the query.
So for a simple EXISTS subquery like this:
SELECT col1 FROM MyTable WHERE EXISTS (SELECT * FROM Table2 WHERE MyTable.col1=Table2.col2)
The * will be expanded to some potentially big column list and then it will be determined that the semantics of the EXISTS does not require any of those columns, so basically all of them can be removed.
“SELECT 1″ will avoid having to examine any unneeded metadata for that table during query compilation.
However, at runtime the two forms of the query will be identical and will have identical runtimes.
As such, I typically use SELECT 1. In my blog post I didn’t.
Conor
6 Responses to EXISTS Subqueries: SELECT 1 vs. SELECT
Hi.
I tried to see if I could see any difference based on this, as it is something I’ve pondered on myself as well, and was interested in testing out for work purpose and …. well, personal curiosity.
However, do you know how big does the table have to be before you start seeing difference between SELECT 1 and SELECT * in the EXISTS subquery?
I made some test tables, and no matter what I did I got the exact same execution plan and estimated cost value used for both running SELECT 1 and SELECT * in the subquery.
Even when I queued together a test table (consisting of 31 fields – 1 integer and 30 nvarchar(10), filled out) with it self like:
SELECT <SOME VALUES> FROM <TABLE> T1 WHERE EXISTS (SELECT * FROM <TABLE> T2 WHERE T1.<VALUE> = T2.<VALUE>)
both with SELECT * and SELECT 1 it gave the same plan, with and without indexes.
So I was interested in the size before there are actual benefits from using SELECT 1 over SELECT *?
With regards.
Is what you say in the post an official definition of how the SELECT clause of the EXISTS predicate is parsed/compiled?
I have never seen any actual difference between SELECT *, SELECT <constant> and SELECT <key column>.
(http://milambda.blogspot.com/2006/10/exists-or-not-exists-that-is-question.html)
ML
What he is saying is that there is a difference at compile time, not execution time. Looking at execution plans generated and estimated cost is not going to show any differences. If you would see any difference it would be in the compile time output by SET STATISTICS TIME ON.
Hello,
I would have a question regarding this topic.
Take the following table set-up:
USE MASTER;
GO
DROP DATABASE Test;
GO
CREATE DATABASE TEST;
GO
USE TEST;
GO
DROP LOGIN Test;
GO
CREATE LOGIN Test WITH PASSWORD=N’PWD’, DEFAULT_DATABASE=Test, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
CREATE USER Test FOR LOGIN Test WITH DEFAULT_SCHEMA=dbo;
GO
CREATE TABLE dbo.MyTable(
FreeCol int NULL,
RestrictedCol int NULL
);
GO
GRANT SELECT ON dbo.MyTable (FreeCol) TO Test;
GO
DENY SELECT ON dbo.MyTable (RestrictedCol) TO Test;
GO
EXECUTE AS LOGIN = ‘Test’;
SELECT suser_sname(), user_name();
SELECT FreeCol FROM dbo.MyTable;
SELECT 1 FROM dbo.MyTable;
SELECT * FROM dbo.MyTable;
REVERT;
SELECT suser_sname(), user_name();
Why does the query "SELECT 1 FROM dbo.MyTable" fail (permission denied on column RestrictedCol)?
To me this looks like the columns are also retrieved when you just SELECT 1.
Or is this just the case when column level permissions are used?
Thanks! Great Blog btw
I believe this is just a case when column-level permissions are used.
Obviously one _could_ build a system where it would work, but for whatever reason it wasn’t done that way.
Sorry I don’t have anything more interesting to tell you on this one
.
Conor
–Quote–
What he is saying is that there is a difference at compile time, not execution time. Looking at execution plans generated and estimated cost is not going to show any differences. If you would see any difference it would be in the compile time output by SET STATISTICS TIME ON.
–Quote–
Even running statistics time, and doing DROPCLEANBUFFERS and FREEPROCCACHE I have problems seeing any consistent difference between the two.
Both compiles looks to take the same time, and the execution time differs more or less sporadic between the two.