InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 23:33:20 GMT20http://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspx[quote][b]GilaMonster (2/12/2011)[/b][hr][quote][b]amenjonathan (2/11/2011)[/b][hr]I also use SELECT NULL.Personally if something is faster and there's virtually no complexity difference, I use the faster version. Why waste where there is no need is my motto.[/quote]The point is, it's not significantly faster, it's a couple of nanoseconds at best. It's micro-optimisation, it's a waste of time for no value.Use EXISTS (Select *, EXISTS (Select 1, EXISTS (Select NULL, or any other form as they are not going to make anything resembling a noticeable difference.[/quote]If I'm typing it out for the first time and I type SELECT NULL instead of SELECT *, I don't see how that wastes any time. Yes it does not provide a lot of value, but hey a tiny amount of value for free is still better than no value for free. Also, again this is just the way I do things, it helps me stay vigilant with optimization.Again my motto is why not do what's most optimal if it doesn't require any additional work?Mon, 14 Feb 2011 09:25:50 GMTamenjonathanhttp://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspx[quote][b]amenjonathan (2/11/2011)[/b][hr]I also use SELECT NULL.Personally if something is faster and there's virtually no complexity difference, I use the faster version. Why waste where there is no need is my motto.[/quote]The point is, it's not significantly faster, it's a couple of nanoseconds at best. It's micro-optimisation, it's a waste of time for no value.Use EXISTS (Select *, EXISTS (Select 1, EXISTS (Select NULL, or any other form as they are not going to make anything resembling a noticeable difference.Sat, 12 Feb 2011 01:20:08 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspx[quote][b]luckysql.kinda (2/11/2011)[/b][hr][url=http://mssqlcorruptiontackle.blogspot.com/2011/01/cost-analysis-among-exists-select-1.html]Cost Analysis Among EXISTS (SELECT 1), EXISTS (SELECT COUNT(1)), EXISTS (SELECT *) AND EXISTS (SELECT TOP 1)[/url] should help[/quote]Not really. Firstly there's no cost analysis there, no testing, no numbers, nothing other than the blogger's untested opinion, and one of the queries he lists is logically different from the others.Sat, 12 Feb 2011 01:17:50 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspx[url=http://mssqlcorruptiontackle.blogspot.com/2011/01/cost-analysis-among-exists-select-1.html]Cost Analysis Among EXISTS (SELECT 1), EXISTS (SELECT COUNT(1)), EXISTS (SELECT *) AND EXISTS (SELECT TOP 1)[/url] should help-luckyFri, 11 Feb 2011 16:31:52 GMTluckysql.kindahttp://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspxI also use SELECT NULL.Personally if something is faster and there's virtually no complexity difference, I use the faster version. Why waste where there is no need is my motto.Fri, 11 Feb 2011 13:48:24 GMTamenjonathanhttp://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspxMy two cents worth: I use 'SELECT [b][u]Null[/u][/b] FROM ...' when using an EXISTS clause. As Gail has pointed out, the speed difference is probably trivial at best, more likely non-existent, but it emphasizes (to me, anyway) that I'm not looking for any data in such a phrase, just the bare existence of at least one record matching certain conditions.Fri, 11 Feb 2011 07:23:12 GMTpdanes2http://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspx[quote][b]GilaMonster (2/10/2011)[/b][hr][quote][b]deepak.a (2/10/2011)[/b][hr]Thanks for reply,if we use columns or * will it make any performance difference? and also Like ex : if exists(Select 1 ... ) or if exists(select Top 1 1 ....)[/quote]No and no[quote]using a SELECT 1, avoids having to look at any of the meta-data that is not even needed to check the existancee of the records form the table?[/quote]Got nothing to do with metadata. EXISTS only cares about whether there is a row or not, it doesn't look at columns, it doesn't care.Don't waste your time trying to do micro-optimisations. Silly tricks like this do not make major performance differences.[/quote]Thanks a lot Gail Shaw for your replyFri, 11 Feb 2011 02:29:00 GMTdeepak.ahttp://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspx[quote][b]Dave Ballantyne (2/10/2011)[/b][hr]There are the same , count(1) get converted to count(*) by the optimizer / parser.See this post on my blog[url]http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/07/27/count-or-count-1.aspx[/url][/quote]thanks dave for your reply and sharing nice articleFri, 11 Feb 2011 02:28:03 GMTdeepak.ahttp://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspx[quote][b]deepak.a (2/10/2011)[/b][hr]Thanks for reply,if we use columns or * will it make any performance difference? and also Like ex : if exists(Select 1 ... ) or if exists(select Top 1 1 ....)[/quote]No and no[quote]using a SELECT 1, avoids having to look at any of the meta-data that is not even needed to check the existancee of the records form the table?[/quote]Got nothing to do with metadata. EXISTS only cares about whether there is a row or not, it doesn't look at columns, it doesn't care.Don't waste your time trying to do micro-optimisations. Silly tricks like this do not make major performance differences.Thu, 10 Feb 2011 06:58:23 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspxThere are the same , count(1) get converted to count(*) by the optimizer / parser.See this post on my blog[url]http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/07/27/count-or-count-1.aspx[/url]Thu, 10 Feb 2011 06:29:55 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspxHi GilaMonster,Thanks for reply,if we use columns or * will it make any performance difference? and also using top 1 1 instead 1 will give better peroformance ?Like ex : if exists(Select 1 ... ) or if exists(select Top 1 1 ....)using a SELECT 1, avoids having to look at any of the meta-data that is not even needed to check the existancee of the records form the table?Thanks & regardsDeepak.AThu, 10 Feb 2011 06:18:35 GMTdeepak.ahttp://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspxNo difference. No matter what you use, it's removed early in the parsing stage. I use SELECT 1 to make it extremely clear that the exists is not returning any columns.Thu, 10 Feb 2011 05:45:42 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspx[url]http://www.sqlservercentral.com/Forums/Topic786650-338-1.aspx[/url][url]http://www.sqlservercentral.com/Forums/Topic453737-338-1.aspx[/url]Marginal differences, it would appear.BrainDonor.Thu, 10 Feb 2011 05:00:11 GMTBrainDonorhttp://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspxIn Theory, it makes no difference what you SELECT in an EXISTS sub-query as it is just syntactical sugar. (ie Nothing is actually selected.)I seem to remember someone doing a test in SQL2000 which showed that SELECTing a constant was marginally quicker than SELECT * as SQL2000 seemed to look up the column names. I have no idea if this is still true with SQL2008.A lot of development shops specify that you should use SELECT 1, in EXISTS sub-queries, so they can easily check for lazy developers putting SELECT * in the rest of their code.Thu, 10 Feb 2011 04:48:47 GMTKen McKelveyhttp://www.sqlservercentral.com/Forums/Topic1061848-391-1.aspxHi All,Can any body please explain ,While Checking the existance of records using "exists". Which method will be more sufficient and WHY ? “SELECT 1″ rather than a “SELECT *” when using an EXISTS or a NOT EXISTS clause”.Thanks & RegardsDeepak.AThu, 10 Feb 2011 04:34:03 GMTdeepak.a