April 22, 2010 at 8:06 am
Although this question probably should have stated it was for SQL2008 or used a UNION on the insert, it was still a solid question. I have no doubt that writing a QOD, no matter how simple, is a difficult process to get 100% accurate with nothing missing or all assumptions answered. If you've read anything about SQL2008 enhancements then it's not a stretch to see this as a valid insert statement. If not, well then you learned something... which is the point anyway. 🙂
If you really need the points... you can have mine. I've tried spending them at Walmart but they wouldn't take them. 😉
Speaking of learning something, Paul Randal has some great articles on what happens during a TRUNCATE statement on his blog.
David
April 22, 2010 at 8:18 am
Tao Klerks (4/22/2010)
If you get an error on the insert, then none of the given answers is relevant - you cannot choose any of the given options, because the last select never even runs.
That is an interesting point. If the question asks what the result of the last select statement is, and you get an error but fail to realize the error is not for the last select statement, do you still think you got the answer correct?
If the question said, what do you get when running this code, then I could see error as being correct, but the question was about the last select statement.
I did not recognize the the syntax on the insert, but could figure out that it was putting something into a table. So I focused on the area where the question was pointing me.
April 22, 2010 at 8:21 am
tosscrosby (4/22/2010)
I agree, a very poor question indeed. At a minimum, it should have given the minimum version of SQL to run on. I only have 2000 and 2005. Incorrect syntax on the insert statement.
Instead of insert into a (b) values (1), (2), (3) use
insert into a (b) values (1)
insert into a (b) values (2)
insert into a (b) values (3) for sql server 2005. What's the deal. Idea is to put three rows in the table.
A simple question but the confusion point for me is to test if I can do a rollback on truncate table statement. I never had tried before for truncate but was sure for delete.
A good question. But not worth 2 points. Should had been 1 instead.
As soon as I write begin transaction, next line would be commit and in between is the DML command. If I use that logic I would get 0 records for 2 and 3 statements.
select * from a
begin tran
truncate table a
select * from a
commit
rollback tran
select * from a
SQL DBA.
April 22, 2010 at 8:37 am
Lost points but learned a feature difference between 2005 and 2008 so points well spent in my book
-------------------------------------------------------
"With great power comes great responsibility"
April 22, 2010 at 8:39 am
I think this was worded a little poorly, and I didn't catch the insert issue. I ran the code on 2008, it worked, I let the question go. I thought the rollback/truncate was tricky enough to be worth 2 points.
I have added 2008 to the question header, as well as noted in the answer for error, "error on the last SELECT".
The debate is interesting here, but for those of you that say that the question isn't fair because it's 2008 specific, 2008 isn't even the current version today. SQL Server 2008 R2 is. I would think that after a year and a half, that you would expect that 2008 is the subject of most questions.
SQL 2000 is EOL, SQL 2005 is getting close to a complete end of support (it's 2010), regardless of what's in *your* environment, consider 2008 to be the standard.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 22, 2010 at 8:42 am
tasneem 27027 (4/21/2010)
insert into a (b) values (1), (2), (3)
This would ideally give you an exception at the insert statement. Because as far I know the comma seperated value insertion as mentioned in the above insert statement is incorrect. So answer should be an exception/error
Welcome to your new SQL 2008 masters. Comma seperated insert lists of values like that are legit.
April 22, 2010 at 9:28 am
mtassin (4/22/2010)
tasneem 27027 (4/21/2010)
insert into a (b) values (1), (2), (3)
This would ideally give you an exception at the insert statement. Because as far I know the comma seperated value insertion as mentioned in the above insert statement is incorrect. So answer should be an exception/error
Welcome to your new SQL 2008 masters. Comma seperated insert lists of values like that are legit.
I like the fact that this question of the day used the comma seperated values. I haven't had a chance to use this feature yet but this is something I've wanted in the language for years.
I got the question wrong. I'm kicking myself because it is something I should have gotten right, I just clicked the wrong box and hit submit before I had a chance to change it, noticed it right as I hit the button. doh.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
April 22, 2010 at 9:32 am
Well...not sure if this has already been stated.
Query...does the Insert work on SQL 2008 if the db was created in an earlier version?
April 22, 2010 at 9:55 am
I chose 0, because it did not error on select, it errored on insert. and the select returns rightly, 0 rows. I had never seen the insert done that way and thought that error was the point, albeit not a very interesting one, and tried it out to see if it worked (on SQL 2005 of course) because it looked wrong to me. I guess I should have read the mind of the questioner and seen that he meant to ask "does a rollback undo a truncate" which is a bit more interesting question.
It would be nice if using new or version specific TSQL syntax to identify it. It distracts from the point.
April 22, 2010 at 9:57 am
vishal.kapoor2007 (4/22/2010)
insert into a (b) values (1), (2), (3)The above statement will result in the error mentioned below in case u use any other version of SQL other than SQL 2008
Incorrect syntax near ','.
So the questions should be generic enough to be compatible with all the available versions of SQL.
I disagree - the question is about whether or not you can rollback a transaction. It isn't a test of whether you can cut and paste the query into a query editor. Do we also need to make sure that the questions can be run on SQL 6.5? I know of some people that are still running that.
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
April 22, 2010 at 9:58 am
Ninja's_RGR'us (4/22/2010)
Anyhow I see how hard it is to get a "perfect" question in there. Takes a lot of thinking.
I like 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
April 22, 2010 at 10:02 am
Hugo Kornelis (4/22/2010)
Ian Elliott (4/22/2010)
I don't think it's hard to write the perfect question if it's just given a little though on the basics e.g. test the question and answers on different versions and specify which ones you're referring to in the question. Being more specific in a question doesn't mean you are simplifying the question but can mean you don't distract people from the learning involved in getting the answer. 😀As an author of eleven questions so far (2 of them not yet published), I can only say that you are wrong. I tried very hard to get the questions perfect, all 11 of them, and I still earned criticism (some very justified, some ... ahem ... less so) on all of them.
If you want to write questions that are not a complete giveaway and that include a learning opportunity for at least some of the visitors, it IS, definitely, very very very hard to get even close to perfect.
Hugo makes a great point. If everything is explicitly laid out so as to invoke no thought, then the question may be too easy and far too few will learn anything at all.
Also, since these QODs are more of a learning opportunity and community involvement opportunity, they are no less perfect than certification questions.
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
April 22, 2010 at 10:08 am
jim.tinney (4/22/2010)
I chose 0, because it did not error on select, it errored on insert. and the select returns rightly, 0 rows.
That's not quite true. If you considered the point of the question to be the insert error, then none of the answers were possible. The last select does not run when the insert fails - it's a syntax error, so the entire batch fails.
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
April 22, 2010 at 10:17 am
Ravi-339369 (4/22/2010)
Query...does the Insert work on SQL 2008 if the db was created in an earlier version?
I can't exactly answer your question because I don't have a copy of SQL Server 2008 handy to check, but typically T-SQL syntax changes like this are managed by the "Compatibility Level" of a database.
Typically, when you create a database in one version of sql server and then bring it to a newer version of SQL server (restore, attach, or upgrade the instance) then the "Compatibility Level" of the database remains on the earlier version - and the new T-SQL syntax features are not available until you change the compatibility level of the database.
Sorry I can't check for this specific feature, but that's the theory as I understand it.
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
April 22, 2010 at 10:25 am
David_Simpson (4/22/2010)
Although this question probably should have stated it was for SQL2008 or used a UNION on the insert, it was still a solid question. I have no doubt that writing a QOD, no matter how simple, is a difficult process to get 100% accurate with nothing missing or all assumptions answered. If you've read anything about SQL2008 enhancements then it's not a stretch to see this as a valid insert statement. If not, well then you learned something... which is the point anyway. 🙂If you really need the points... you can have mine. I've tried spending them at Walmart but they wouldn't take them. 😉
Speaking of learning something, Paul Randal has some great articles on what happens during a TRUNCATE statement on his blog.
David
Great info and great point of view.:cool:
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
Viewing 15 posts - 46 through 60 (of 87 total)
You must be logged in to reply to this topic. Login to reply