Creating Tables

Question

Creating Tables

Viewing 15 posts - 16 through 30 (of 58 total)

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

paul s-306273 SSChampion Points: 10727 More actions · Answer 1

Well, I thought it was a nice question or maybe I was too naive to think it might be a trick

TomThomson SSC Guru Points: 104773 More actions · Answer 2

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.

I went through exactly the thought process Hugo describes, and answered that all three will fail (the only option that gives the same effect as a correct answer - the table is not created). I don't see how the given answer and explanation can be considered useful, as they are concerned only with running the code as three separate batches, and I thought it fair to assme that the person who wrote the question would know enough to understand the difference between running something as a single batch and running it as three batches (always assume the best of people unless there is evidence to the contrary is generally a good rule).

I find it amazing that so many people think it a good question. The attempt by one of them to justify it even after the error has been pointed out

demonfox (2/10/2013)
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

which seems say "the author can't have got it wrong because I didn't spot the mistake" just plain silly. It's always just plain silly to say "whats the point is it meant what it said?", the sensible question is "what's the point of saying something doesn't mean what you say?".

It isn't a good question, inn fact it is a terrible question, because it is not asking what the author apparently thought it asked. If it had meant what it said, it would have been a good question.

Tom

sipas Hall of Fame Points: 3828 More actions · Answer 3

binod.soft (2/10/2013)
The Statement has three GO syntax which indicates three Batches. Each Go used shows an end of the Batch.

Yes, there's not really any getting round that - however you sometimes learn as much from a badly worded question as a perfectly worded one - you just don't necessarily get your point(s).;-)

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 4

I think, i didn't take it seriously, and i got it wrong.

anyway good question.

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

Hugo Kornelis (2/11/2013)
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:

Well, it doesn't help that no-one ever uses Integer and every script generated by SSMS uses int.

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

Mighty SSCrazy Eights Points: 9063 More actions · Answer 6

Maybe another option for a(n) (in)correct answer: "Only statement 2 and 3 will succeed and 1 row will be returned...".

But probably I am the only one still having SQL Servers running pre-2008. 😀

mtassin SSC-Insane Points: 23099 More actions · Answer 7

Thanks for the trips down memory lane...

This question reminds me of things I did before I learned how temp tables worked. I was so confused way back when on why I couldn't use select INTO after I had created said table.

Of course I learned better way back then too... 🙂

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Sean Lange SSC Guru Points: 286589 More actions · Answer 8

I too had the same thought as Hugo. The single batch part threw me off so I figured that the OP must have been running the code as posted when creating the question and by single batch meant running all of the code by hitting F5. All in all a decent question but the wording could have been better. Thanks for the effort Dave.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 9

(Bob Brown)

SSCrazy

Points: 2705

February 11, 2013 at 7:30 am

#1586554

Thanks for the question!

sestell1 SSChampion Points: 10230 More actions · Answer 10

I got lucky in guessing that the intent of the question was to show that you can't use INTO with an existing table, and that the answer really meant statements 1, 3, and *4* would execute successfully. It's too bad about the wording issues, as otherwise this would have been a decent QotD.

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 11

sestell1 (2/11/2013)
It's too bad about the wording issues, as otherwise this would have been a decent QotD.

I think it was a pretty decent QotD. Approximately 2/3 got it right. Seems that number three was the only obvious answer to choose.

Toreador SSChampion Points: 11360 More actions · Answer 12

Koen Verbeeck (2/11/2013)
Well, it doesn't help that no-one ever uses Integer

I always use Integer 🙂

Sean Lange SSC Guru Points: 286589 More actions · Answer 13

(Bob Brown) (2/11/2013)
sestell1 (2/11/2013)
It's too bad about the wording issues, as otherwise this would have been a decent QotD.
I think it was a pretty decent QotD. Approximately 2/3 got it right. Seems that number three was the only obvious answer to choose.

But #3 is not the correct choice when you consider the wording of the question.

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

Does that not indicate that you should remove the batch separators from the code posted so that it will run in a single batch?

Here is the original code with the GOs commented out so it will be a single batch.

-- Statement 1

Create Table #TempQoD

(

RowId Integer

, Question varChar(max)

, Answer varChar(max)

, PublishDate Date

);

--GO

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

-- Statement 3

Select * From #TempQoD;

Drop Table #TempQoD;

--GO

Executing this as a single batch will not parse.

Msg 2714, Level 16, State 1, Line 18

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

Therefore the only obvious answers are either #2 or #3 depending...

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ldorian81 SSCommitted Points: 1694 More actions · Answer 14

If you put the whole query into SSMS 2008 and run it without removing the 'GO' commands you will still get the same results as shown below.

Msg 2714, Level 16, State 1, Line 18

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

Very poor wording.

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.

(Bob Brown) SSCrazy Points: 2705 More actions · Answer 15

Sean Lange (2/11/2013)
Here is the original code with the GOs commented out so it will be a single batch.

But the GOs are there.