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 Tuesday, March 30, 2010 1:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
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
Posted Tuesday, March 30, 2010 1:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 AM
Points: 11,192, Visits: 11,098
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #893217
Posted Sunday, April 4, 2010 2:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #896480
Posted Sunday, April 4, 2010 3:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 AM
Points: 11,192, Visits: 11,098
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896485
Posted Tuesday, July 27, 2010 8:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:10 AM
Points: 814, Visits: 1,361
Option of the question doesn't look correct to me..Though I made it correct!

Thanks.
Post #959417
« Prev Topic | Next Topic »

Add to briefcase «««56789

Permissions Expand / Collapse