March 30, 2010 at 12:25 pm
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.
March 30, 2010 at 12:31 pm
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
SQLkiwi blog
@SQL_Kiwi
March 30, 2010 at 12:41 pm
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
March 30, 2010 at 12:56 pm
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
SQLkiwi blog
@SQL_Kiwi
March 30, 2010 at 1:03 pm
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 1Me 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! 😉
March 30, 2010 at 1:13 pm
Hugo Kornelis (3/30/2010)
If there's one thing I've learned over the past years, it is to take everything that comes from Itzik VERY seriously! 😉
Agreed. He has a tricky habit of knowing stuff quite deeply. Good luck.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 4, 2010 at 2:57 pm
My apologies, I was forgotten to follow up here.
I got a response from Itzik. His source is an old blog post from Conor Cunningham - another man to take VERY seriously if he writes about SQL Server. Here is a link: http://www.sqlskills.com/BLOGS/CONOR/2008/02/default.aspx?page=2. I think it's an old and abandoned blog, the comments link does not work, but the post itself can still be read.
What it amounts to is that SELECT * is first replaced with SELECT <list of columns>, then permissions are checked, and only then does the optimizer realize that it's inside an EXISTS and so removes the column lists again. See the Microsoft feedback on https://connect.microsoft.com/SQLServer/feedback/details/533491/users-with-select-permission-on-one-column-in-a-table-get-errors-from-if-exists-select-from-table for an explanation of WHY these permissions are checked (though I don't agree 100%).
However, the differences between all these variants are incredibly small, and not really worth spending any amount of time or energy on. I mainly use SELECT * in EXISTS because it's the most common and sort of standard version, and because it documents that I am interested in existence of a row.
April 4, 2010 at 3:17 pm
Thanks Hugo. I read the blog and the Connect item.
Seems to me that things have changed since the Connect item, given the results of your tests.
If the information in the blog is still accurate, the only extra cost of SELECT * is metadata expansion at compile time: plan and execution time is identical. Good to know.
Conor's active blog is here:
http://blogs.msdn.com/conor_cunningham_msft/
I see your COALESCE connect item has been updated...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 27, 2010 at 8:16 am
Option of the question doesn't look correct to me..Though I made it correct!
Thanks.
Viewing 9 posts - 76 through 83 (of 83 total)
You must be logged in to reply to this topic. Login to reply