SQL Server Performance

What is the Use of using Where 1=1 in SQL Query

Discussion in 'SQL Server 2005 General Developer Questions' started by vijai.krishnaraj, Dec 4, 2006.

  1. vijai.krishnaraj New Member

    Hi Experts

    What is the use of using where 1=1 in SQL Query Statement, This could be of great help to me

    Regards

  2. DilliGrg Member

    quote:Originally posted by vijai.krishnaraj

    Hi Experts

    What is the use of using where 1=1 in SQL Query Statement, This could be of great help to me

    Regards




    This means that the condition is always true.




    SELECT * INTO Table1 FROM Table2 WHERE 1 = 1.




    This will create table1 and populate data from table2.




    SELECT * INTO Table1 FROM Table2 WHERE 1 = 0.

    This will create table1 similar to Table2 but with No data.



    Thanks,
    DilliGrg
  3. Madhivanan Moderator

    It can be also used in While loop

    While 1=1
    --some stuff

    Madhivanan

    Failing to plan is Planning to fail
  4. eloop New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />It can be also used in While loop<br /><br />While 1=1<br />--some stuff<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />I guess that while loop would fall into the category of "long-running-queries"<br /><br />[<img src='/community/emoticons/emotion-5.gif' alt=';)' />] <br /><br /><br />"Follow the join tree" - Dan Tow
  5. dineshasanka Moderator

    SELECT * INTO Table1 FROM Table2 WHERE 1 = 0. Smart!!

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  6. Madhivanan Moderator

    quote:Originally posted by dineshasanka

    SELECT * INTO Table1 FROM Table2 WHERE 1 = 0. Smart!!

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/

    Other method
    SELECT Top 0 * INTO Table1 FROM Table2

    Madhivanan

    Failing to plan is Planning to fail
  7. vsnreddi New Member

    yes both scenarios are different

    SURYA

  8. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by eloop</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />It can be also used in While loop<br /><br />While 1=1<br />--some stuff<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />I guess that while loop would fall into the category of "long-running-queries"<br /><br />[<img src='/community/emoticons/emotion-5.gif' alt=';)' />] <br /><br /><br />"Follow the join tree" - Dan Tow<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Correct, but you can always include the BREAK command to get out of the loop when a certain condition is met.<br /><br />The point is that you must build in a guaranteed break point, perhaps after the loop has continued for longer than a given amount of time.
  9. DilliGrg Member

    quote:Originally posted by dineshasanka

    SELECT * INTO Table1 FROM Table2 WHERE 1 = 0. Smart!!

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/



    Thanks.
    Don't know if the original poster got his concern addressed.

    Thanks,
    DilliGrg
  10. DilliGrg Member

    quote:Originally posted by vsnreddi

    yes both scenarios are different

    SURYA




    I absolutely agree.

    Thanks,
    DilliGrg
  11. MohammedU New Member

    I use "SELECT * INTO Table1 FROM Table2 WHERE 1 = 0" to create table schema where I use WHILE 1=1 with BREAK to process records...

    I also use WHILE 1 = 1 withour BREAK to run in an infinite loop.. ex:blocking script in a job...


    Mohammed U.
  12. Madhivanan Moderator

    <<
    I use "SELECT * INTO Table1 FROM Table2 WHERE 1 = 0" to create table schema
    >>

    Note that target table wont have indices, constraints, etc that Source table has

    Madhivanan

    Failing to plan is Planning to fail

Share This Page