Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789»»

Query cost Expand / Collapse
Author
Message
Posted Monday, March 15, 2010 2:50 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, 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
Post #883359
Posted Monday, March 15, 2010 2:51 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, Visits: 655
NM, I read the forum... I got a point for that??? I almost feel guilty

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Post #883362
Posted Friday, March 19, 2010 4:06 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:48 AM
Points: 3,241, Visits: 5,002
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'


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

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #886193
Posted Tuesday, March 30, 2010 10:19 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #893015
Posted Tuesday, March 30, 2010 10:22 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #893019
Posted Tuesday, March 30, 2010 12:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:07 AM
Points: 6,040, Visits: 8,322
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
Post #893162
Posted Tuesday, March 30, 2010 12:31 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #893173
Posted Tuesday, March 30, 2010 12:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:07 AM
Points: 6,040, Visits: 8,322
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
Post #893181
Posted Tuesday, March 30, 2010 12:56 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #893192
Posted Tuesday, March 30, 2010 1:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:07 AM
Points: 6,040, Visits: 8,322
Paul White NZ (3/30/2010)
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.
I'll contact Itzik and ask him if we are overlooking something. If there's one thing I've learned over the past years, it is to take everything that comes from Itzik VERY seriously! ;)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #893204
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse