Should I Type * or 1 With the EXISTS Logical Operator

  • v.malyutin

    SSChasing Mays

    Points: 602

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

  • sequelgarrett

    SSC Eights!

    Points: 802

    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".

  • Luis Cazares

    SSC Guru

    Points: 183633

    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
  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • v.malyutin

    SSChasing Mays

    Points: 602

    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.

  • v.malyutin

    SSChasing Mays

    Points: 602

    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.

  • Luis Cazares

    SSC Guru

    Points: 183633

    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
  • Luis Cazares

    SSC Guru

    Points: 183633

    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
  • beakdan

    Old Hand

    Points: 373

    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.

  • Brad Schulz

    Old Hand

    Points: 395

    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*

  • v.malyutin

    SSChasing Mays

    Points: 602

    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!

  • vasim-622610

    Valued Member

    Points: 54

    What about select null, did you test that?

  • v.malyutin

    SSChasing Mays

    Points: 602

    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.

  • RonKyle

    SSC-Dedicated

    Points: 31482

    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.

  • v.malyutin

    SSChasing Mays

    Points: 602

    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