SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query cost


Query cost

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)

Group: Administrators
Points: 141540 Visits: 19417
It's actually trivial to read the discussion. I usually do it first thing to check the question before I check on the way it looks to a new user.

On the home page we have the question, and then we have the "discuss" link there.

If you want to read the discussion first, be my guest. This is a tool for you, the user. It's not a ranking, it doesn't imply you have knowledge, and it doesn't imply that you're a guru. You could add to the discussion and show something, but answering a question doesn't necessarily prove anything.

Complaining without any valid reason for doing so, however, does show an impression for someone that might be looking to interview you.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Peter Trast
Peter Trast
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1036 Visits: 655
LOL!!!

I had 3 choices
1. Wrong Answer
2. Right Answer
3. Wrong Answer

so I chose #2, the "Right Answer"... was this a joke question or did the webpage break??? :-)

I would like to see the actual or original choices if they are available...

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Peter Trast
Peter Trast
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1036 Visits: 655
NM, I read the forum... I got a point for that??? I almost feel guilty Sad

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Atif-ullah Sheikh
Atif-ullah Sheikh
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5018 Visits: 5204
Something is not right....

I got three choices
1. Wrong Answer
2. Right Answer
3. Wrong Answer

I got the point by selecting 'Right Answer' :-P

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here


Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34226 Visits: 11359
The optimizer works on a logical tree of operations. That tree is derived from the text of the statement presented. The optimizer has many built in guaranteed-safe transformations which mean that many logically equivalently written queries end up producing an identical, or trivially different, execution plan.

The important thing to realise is that in many cases, queries written with IN and EXISTS are provably identical, and produce identical execution plans. In those cases, debating which is or isn't more efficient is an utterly moot point. The text is different, but the execution is the same.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34226 Visits: 11359
Hugo Kornelis (1/27/2010)
the strangely popular but really rather odd EXISTS 1 instead of EXISTS *...

I too find it odd, and always use the star syntax, but it turns out there is a (vanishingly small) difference, related to the checking of column permissions for EXISTS *.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18389 Visits: 12426
Paul White NZ (3/30/2010)
Hugo Kornelis (1/27/2010)
the strangely popular but really rather odd EXISTS 1 instead of EXISTS *...

I too find it odd, and always use the star syntax, but it turns out there is a (vanishingly small) difference, related to the checking of column permissions for EXISTS *.
As far as I know, that was once the case. In SQL Server 7.0 or so.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34226 Visits: 11359
Hugo Kornelis (3/30/2010)
As far as I know, that was once the case. In SQL Server 7.0 or so.

It's still in Itzik's Inside SQL Server 2008 T-SQL Querying book :-)
Do you want to test it or should I? ;-)

The effect was always, as I said, vanishingly small. I always use the star syntax. As I think I mentioned.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18389 Visits: 12426
Paul White NZ (3/30/2010)
Hugo Kornelis (3/30/2010)
As far as I know, that was once the case. In SQL Server 7.0 or so.

It's still in Itzik's Inside SQL Server 2008 T-SQL Querying book :-)
Do you want to test it or should I? ;-)

The effect was always, as I said, vanishingly small. I always use the star syntax. As I think I mentioned.
I tested it. Below is the code I ran. It shows that both SELECT * and SELECT 1 test for both table- and column-level permissions. (Try changing the granted and denied privileges - I was not able to find any way to get the SELECT * to behave other than the SELECT 1).
create table x (a int, b int);
go
create user TestUser without login;
go
deny select on x to TestUser;
grant select on x(b) to TestUser;
go
if exists (select * from x) print 'Aye';
if exists (select 1 from x) print 'Aye';
if exists (select a from x) print 'Aye';
go
execute as user='TestUser';
go
if exists (select * from x) print 'Aye';
if exists (select 1 from x) print 'Aye';
if exists (select a from x) print 'Aye';
go
revert;
go
drop user TestUser;
drop table x;
go




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34226 Visits: 11359
Hugo Kornelis (3/30/2010)
I was not able to find any way to get the SELECT * to behave other than the SELECT 1

Me either! I tried indexes, scalar functions, XML methods...you name it.
Looks like Itzik will be revising that paragraph in the next edition.
I am relieved - I had posted many times in the past to say that both forms were identical, so was quite shocked to read that there was a difference after all. Never bothered to test it though...so thanks for that.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search