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 :)

--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.