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:13 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 9,928, Visits: 11,207
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: Today @ 4:58 PM
Points: 6,133, Visits: 8,399
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


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 9,928, Visits: 11,207
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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 19, 2014 4:41 AM
Points: 1,048, Visits: 1,558
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