Creating Tables

  • Dave62

    SSCertifiable

    Points: 6215

    Comments posted to this topic are about the item Creating Tables

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice question to start the week ... reminded me of the basics once again.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • SQLRNNR

    SSC Guru

    Points: 281106

    Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • kapil_kk

    SSC-Insane

    Points: 21316

    Good basic question to start the week..... 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • binod.soft

    SSC-Addicted

    Points: 420

    The Statement has three GO syntax which indicates three Batches. Each Go used shows an end of the Batch. I have shown below each of the GO with their end of batches comments.

    But As per your question the statement should be executed in a single Batch in which case One has to remove all the GO syntax to execute the whole Query in a single Batch. And hence "Option 2 : All statement should fail" must be the right answer.

    The Error must be :--

    Msg 2714, Level 16, State 1, Line 18

    There is already an object named '#TempQoD' in the database.

    Where as if you execute the whole statement with all GO syntax as it is .. In that case the Answer must be option 3.

    -- Statement 1

    Create Table #TempQoD

    (

    RowId Integer

    , Question varChar(max)

    , Answer varChar(max)

    , PublishDate Date

    );

    Go--End of first batch

    -- Statement 2

    Select

    1 As [RowId]

    , 'True or False: The HAVING clause is the 4th phase of logical query processing?' As [Question]

    , 'True: 1-FROM, 2-WHERE, 3-GROUP BY, 4-HAVING, 5-SELECT, 6-ORDER BY.' As [Answer]

    , '15 Jan 2013' As [PublishDate]

    Into

    #TempQoD;

    Go--End of 2nd Batch

    -- Statement 3

    Select * From #TempQoD;

    Drop Table #TempQoD;

    GO--End Of third batch

  • demonfox

    SSCertifiable

    Points: 6289

    binod.soft (2/10/2013)


    The Statement has three GO syntax which indicates three Batches. Each Go used shows an end of the Batch. I have shown below each of the GO with their end of batches comments.

    But As per your question the statement should be executed in a single Batch in which case One has to remove all the GO syntax to execute the whole Query in a single Batch. And hence "Option 2 : All statement should fail" must be the right answer.

    The Error must be :--

    Msg 2714, Level 16, State 1, Line 18

    There is already an object named '#TempQoD' in the database.

    Where as if you execute the whole statement with all GO syntax as it is .. In that case the Answer must be option 3.

    -- Statement 1

    Create Table #TempQoD

    (

    RowId Integer

    , Question varChar(max)

    , Answer varChar(max)

    , PublishDate Date

    );

    Go--End of first batch

    -- Statement 2

    Select

    1 As [RowId]

    , 'True or False: The HAVING clause is the 4th phase of logical query processing?' As [Question]

    , 'True: 1-FROM, 2-WHERE, 3-GROUP BY, 4-HAVING, 5-SELECT, 6-ORDER BY.' As [Answer]

    , '15 Jan 2013' As [PublishDate]

    Into

    #TempQoD;

    Go--End of 2nd Batch

    -- Statement 3

    Select * From #TempQoD;

    Drop Table #TempQoD;

    GO--End Of third batch

    Well, Let's not lost the question in the wordings; what's the point of three Go Statements , if the statement meant to remove them and then execute ..

    as it said

    What will the outcome be after running all the following statements as 1 batch?

    All the following statements, means Including Go ..

    nice and simple , basics refreshed with a good question ...

    thanks for the question

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • manik_anu

    SSCrazy

    Points: 2367

    nice question... starting with basic morning....:-)

    Manik
    You cannot get to the top by sitting on your bottom.

  • vk-kirov

    SSCertifiable

    Points: 7686

    binod.soft (2/10/2013)


    remove all the GO syntax to execute the whole Query in a single Batch

    I agree with you. The wording of the question is totally misleading.

    demonfox (2/10/2013)


    All the following statements, means Including Go ..

    How about this:

    http://msdn.microsoft.com/en-us/library/ms188037.aspx

    GO (Transact-SQL)

    Remarks

    GO is not a Transact-SQL statement

    So I read the question as following:

    1) Do something to make the following statements to belong to one batch (ie, remove all GOs);

    2) Run the resulting batch;

    3) What will the outcome be?

  • Koen Verbeeck

    SSC Guru

    Points: 258854

    For me the question was more about the fact you can use Integer instead of int. Didn't know that 😀

    The question cleverly had no color coding on the word Integer, so I assumed SSMS didn't recognize it. But actually it does when you copy paste the code into SSMS.

    Interesting...

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • demonfox

    SSCertifiable

    Points: 6289

    vk-kirov (2/10/2013)


    GO (Transact-SQL)

    Remarks

    GO is not a Transact-SQL statement

    So I read the question as following:

    1) Do something to make the following statements to belong to one batch (ie, remove all GOs);

    2) Run the resulting batch;

    3) What will the outcome be?

    As you said all the wordings ; besides when it says statements then I understood it as the statements written to separate three statements ; and you understood that as T-Sql Statements 😀

    For me the question was more about the fact you can use Integer instead of int. Didn't know that

    +1

    I missed that :w00t: 😀

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • This was removed by the editor as SPAM

  • Stuart Davies

    SSCoach

    Points: 18813

    Nice question - thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    When picking my answer, I knew I had a 50/50 chance, because my mindreading skills are still not on par with my SQL skills.

    There were two options.

    1. The author knows very well what a batch is, and also knows very well that GO ends a batch. The phrasing of the question: "as 1 batch" was deliberate, as a sign that you should answer the question as if the GO lines are not there. He deliberately put them in to trap the people who just copy/paste the code.

    2. The author either doesn't know what a batch is, isn't aware what GO does, or wass not paying sufficient attention when adding that "as 1 batch" to the question. He meant that the entire code should be executed at once (allthough that is actually irrelevant, the results are the same if the batches, or evne the individual statements, are sent and executed one by one).

    In the first case, the entire batch will fail at parse and compile time, so none of the statements will actaully execute. You get an error message and nothing happens. The option "All statements will fail", though technically not 100% accurate (as there is a single failure of a whole batch, not multiple failures of incividual statements), comes close enough to be considered the correct answer.

    In the second case, the first batch will compile and execute successfully, the second batch will fail compilation and not execute, and the third batch will compile and execute successfully. The option "Only the 1st and 3rd statement will succeed but no rows will be returned", though technically not 100% accurate (as the 4th statement will also succeed), comes close enough to be considered te correct answer.

    I threw my dart to pick one of the two options, and failed. Oh well.


    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/

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Koen Verbeeck (2/10/2013)


    For me the question was more about the fact you can use Integer instead of int. Didn't know that 😀

    The question cleverly had no color coding on the word Integer, so I assumed SSMS didn't recognize it. But actually it does when you copy paste the code into SSMS.

    I think that's a shortcoming of SQLServerCentral's color-coding algorithm. Last time I submitted a question, I just had to paste in the query and the color-coding was added automagically.

    I'm surprised you didn't know this. Integer is actually the official name; int is an accepted abbreviation.

    *cough* I have to retract the above. That's what I always thought, but when I went out to find a Books Online reference, I was surprised to see only "int" there. "Integer" is not even mentioned. :unsure:

    EDIT: Found the source of my confusion. The ANSI documents describing the SQL standard define both INT and INTEGER, and describe INT as "equivalent to INTEGER". I've always interpreted that as "INTEGER" being the official form and "INT" being an accepted alternative.


    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/

  • ldorian81

    SSCommitted

    Points: 1694

    Hugo Kornelis (2/11/2013)


    When picking my answer, I knew I had a 50/50 chance, because my mindreading skills are still not on par with my SQL skills.

    There were two options.

    1. The author knows very well what a batch is, and also knows very well that GO ends a batch. The phrasing of the question: "as 1 batch" was deliberate, as a sign that you should answer the question as if the GO lines are not there. He deliberately put them in to trap the people who just copy/paste the code.

    2. The author either doesn't know what a batch is, isn't aware what GO does, or wass not paying sufficient attention when adding that "as 1 batch" to the question. He meant that the entire code should be executed at once (allthough that is actually irrelevant, the results are the same if the batches, or evne the individual statements, are sent and executed one by one).

    In the first case, the entire batch will fail at parse and compile time, so none of the statements will actaully execute. You get an error message and nothing happens. The option "All statements will fail", though technically not 100% accurate (as there is a single failure of a whole batch, not multiple failures of incividual statements), comes close enough to be considered the correct answer.

    In the second case, the first batch will compile and execute successfully, the second batch will fail compilation and not execute, and the third batch will compile and execute successfully. The option "Only the 1st and 3rd statement will succeed but no rows will be returned", though technically not 100% accurate (as the 4th statement will also succeed), comes close enough to be considered te correct answer.

    I threw my dart to pick one of the two options, and failed. Oh well.

    +1 😀

    Execute as 1 Batch and results are combined toghether hence my result below therefore I answered as "All statements will fail". 😉

    Msg 2714, Level 16, State 6, Line 3

    There is already an object named '#TempQoD' in the database.

    (0 row(s) affected)

    :w00t:

    Regards
    ld

    Stoke-on-Trent
    United Kingdom

    If at first you don't succeed, go to the pub and drink away your current thought plan.

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

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