Should I Type * or 1 With the EXISTS Logical Operator

  • Comments posted to this topic are about the item Should I Type * or 1 With the EXISTS Logical Operator

  • You definitely spend more time when you touch two keys "Shift + 7" to type the *, than when you type just "1". 

    I doubt this. Hitting SHIFT + 8 is much easier and less awkward than the pinky hitting the "1".

  • sequelgarrett - Monday, April 24, 2017 7:31 AM

    You definitely spend more time when you touch two keys "Shift + 7" to type the *, than when you type just "1". 

    I doubt this. Hitting SHIFT + 8 is much easier and less awkward than the pinky hitting the "1".

    And this is assuming everyone has a US keyboard.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • v.malyutin - Sunday, April 23, 2017 10:00 PM

    Comments posted to this topic are about the item Should I Type * or 1 With the EXISTS Logical Operator

    In the article, you write: "I think that #1 and #3 are both affected by a large number of columns. That's why I am surprised a lot."
    This is not surprising to me. You tested them after dropping the clustered index, on the heap.The plan you got for that case includes a table scan. And scanning a table with 1,000 integer columns takes more time, because the row size is bigger and hence less rows fit on the page.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Luis Cazares - Monday, April 24, 2017 7:40 AM

    sequelgarrett - Monday, April 24, 2017 7:31 AM

    You definitely spend more time when you touch two keys "Shift + 7" to type the *, than when you type just "1". 

    I doubt this. Hitting SHIFT + 8 is much easier and less awkward than the pinky hitting the "1".

    And this is assuming everyone has a US keyboard.

    You can be sure, everyone has. And numbers are Arabic. Not sure about the *. But, layout is the same in each culture.

  • Hugo Kornelis - Monday, April 24, 2017 9:33 AM

    v.malyutin - Sunday, April 23, 2017 10:00 PM

    Comments posted to this topic are about the item Should I Type * or 1 With the EXISTS Logical Operator

    In the article, you write: "I think that #1 and #3 are both affected by a large number of columns. That's why I am surprised a lot."
    This is not surprising to me. You tested them after dropping the clustered index, on the heap.The plan you got for that case includes a table scan. And scanning a table with 1,000 integer columns takes more time, because the row size is bigger and hence less rows fit on the page.

    I re-created all things before ostress running. Anyway, the key is that both of queries have been tested on the same data and metadata. Thanks.

  • v.malyutin - Monday, April 24, 2017 9:36 AM

    Luis Cazares - Monday, April 24, 2017 7:40 AM

    sequelgarrett - Monday, April 24, 2017 7:31 AM

    You definitely spend more time when you touch two keys "Shift + 7" to type the *, than when you type just "1". 

    I doubt this. Hitting SHIFT + 8 is much easier and less awkward than the pinky hitting the "1".

    And this is assuming everyone has a US keyboard.

    You can be sure, everyone has. And numbers are Arabic. Not sure about the *. But, layout is the same in each culture.

    No, layout is different in each country. My personal laptop is mexican and my work laptop is american. I usually have typos due to the different layouts. That happens as well in Mexico because we have 2 different layouts: mexican and latinamerican.
    I've also used a french keyboard which needs Shift to enter numbers.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • By the way, thank you for writing about this.
    I actually had a conversation on the same subject with a co-worker today (and with another one some days ago). It's good to have proof of someone else testing it. However, one of my favorite tests is when I use WHERE EXISTS( SELECT 1/0 FROM Table....) or today I used SELECT COUNT(1/0) FROM Table.
    Everyone is amazed with that construct.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares - Monday, April 24, 2017 9:55 AM

    By the way, thank you for writing about this.
    I actually had a conversation on the same subject with a co-worker today (and with another one some days ago). It's good to have proof of someone else testing it. However, one of my favorite tests is when I use WHERE EXISTS( SELECT 1/0 FROM Table....) or today I used SELECT COUNT(1/0) FROM Table.
    Everyone is amazed with that construct.

    Luis, you beat me to it. A common discussion here at work was precisely that count(1) or exists(select 1 ...) are faster / better than the asterisk versions. In the case of COUNT(WHATEVER) you can see that in the execution plan the engine actualy executes COUNT(*). In the case of EXISTS (SELECT WHATEVER ....) the engine doesn even consider the expressions in the select clause in the output list, only the columns in the predicate. So they are all the same.
    But for me, the correct form should be the ones with asterisk. In this context the asterisk doesn't mean all the columns, instead it means all the rows.

    v.malyutin
    Thank you for taking the time to test that there are no diferences in execution time with either construct. Although I disagree with your comment about * being a problem in this context. If a person reviewing my code can't tell the difference, they shouldn't be reviewing any code.

  • You might get a kick out of tongue-in-cheek blog post I wrote about this subject several years ago:
    http://bradsruminations.blogspot.com/search/label/SELECT%20*

  • beakdan - Monday, April 24, 2017 10:43 AM

    Luis Cazares - Monday, April 24, 2017 9:55 AM

     If a person reviewing my code can't tell the difference, they shouldn't be reviewing any code.

    I totally agree!

  • What about select null, did you test that?

  • vasim-622610 - Monday, April 24, 2017 11:41 AM

    What about select null, did you test that?

    No, I did not. But I think whatever you typed the execution plan is going to be the same. Only the WHERE matters.

  • I wonder if this would matter in MS Access.  Up to now I've continued my use of SELECT TOP 1 * that I've used since using it in Access (which required using an Exists function I found in an Access 97 book). 

    In any case, I will be using SELECT * from now on.  My own view is that it reads better than SELECT 1.  If the second were demonstrably faster, I would use it, but if it's a tie, I'll go for aesthetics.

  • RonKyle - Monday, April 24, 2017 2:53 PM

    I wonder if this would matter in MS Access.  Up to now I've continued my use of SELECT TOP 1 * that I've used since using it in Access (which required using an Exists function I found in an Access 97 book). 

    In any case, I will be using SELECT * from now on.  My own view is that it reads better than SELECT 1.  If the second were demonstrably faster, I would use it, but if it's a tie, I'll go for aesthetics.

    Can you show me up an execution plan from Access? I remember for sure MS Access 97 could not provide you with execution plan, but I am not sure about newest version.

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply