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, February 15, 2010 9:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Wednesday, June 11, 2014 5:42 PM
Points: 33,007, Visits: 15,132
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
Post #865829
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: Friday, June 27, 2014 8:02 AM
Points: 3,241, Visits: 4,996
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:05 PM
Points: 11,185, Visits: 11,070
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:05 PM
Points: 11,185, Visits: 11,070
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: Today @ 9:34 AM
Points: 5,906, Visits: 8,152
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:05 PM
Points: 11,185, Visits: 11,070
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: Today @ 9:34 AM
Points: 5,906, Visits: 8,152
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:05 PM
Points: 11,185, Visits: 11,070
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
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse