March 11, 2014 at 10:11 am
It was the DST change that got me. I was so tired that the double negatives confused me and I answered in the double negative and selected all the answers that were true. 🙂
Shouldn't I get half credit for working out the correct wrong answers, right? 😉
March 11, 2014 at 11:19 am
Please , is it possible that someone explains me this sentence found in http://msdn.microsoft.com/en-us/library/ms188029.aspx ?
"When a sparse column is included in the select list, the sparse column property does not transfer to the column in the new table. If this property is required in the new table, alter the column definition after executing the SELECT...INTO statement to include this property"
According to me , "The sparse column property does transfer to the column in the new table" is exactly the contrary of what I have read in the BOL.
But it is possible that with my poor understanding of the English language , it is possible I have missed something.
Another remark : I have been warned that in the Microsoft exams , it is possible that I can find questions which are negative , that's to say , I would have to find the false proposals among several ones , a good way to disturb anybody especially when you are not using your mother tongue. This behavior is stupid if it is right that Microsoft is accepting such questions. Exams are to check whether you have good knowledge , if the person is asking such questions as today , I think that it is only to forbid to person to concentrate on the only useful thing : to show that he/she knows SQL Server. These tricky questions are not clever , only dishonest , but free mascot ( who created this question ) is not dishonest , he/she follows only the Microsoft behavior and it is an excellent way to prepare to the exam.
Only one problem ( which is a recurrent one ) : when you validate your choices , it is difficult to remember what they were and to know what we have replied in the false way.
March 11, 2014 at 12:41 pm
Toreador (3/11/2014)
Took a lot of thinking to work out all those double negatives!
+1:-), but a good question none the less
March 11, 2014 at 12:55 pm
I only got this right because I actaully read the question and answers out loud to make sure I got those double negatives correct. Good question. I am glad the question about the identity property was the only "it depends" answer that I saw. The others appeared straight forward to me.
March 11, 2014 at 2:21 pm
+1 Easy question, it's very logical.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
March 11, 2014 at 10:49 pm
J DBA (3/11/2014)
It was the DST change that got me. I was so tired that the double negatives confused me and I answered in the double negative and selected all the answers that were true. 🙂Shouldn't I get half credit for working out the correct wrong answers, right? 😉
That could be the reason this quesiton has alloted +2; one to find correct ans and one to eliminate others 🙂
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 11, 2014 at 10:56 pm
SQLRNNR (3/11/2014)
Koen Verbeeck (3/11/2014)
free_mascot (3/11/2014)
Koen Verbeeck (3/11/2014)
Great question.Although the IDENTITY property doesn't always transfer:
When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:
*The SELECT statement contains a join, GROUP BY clause, or aggregate function.
*Multiple SELECT statements are joined by using UNION.
*The identity column is listed more than one time in the select list.
*The identity column is part of an expression.
*The identity column is from a remote data source.
...
Usually we do not consider exceptions hence for IDENTITY property too we have not consider conditions.
Who is this "we"?
I'd like to know as well. Exceptions and conditions are usually considered and explained in the question.
Thank you and appreciate your comment.
Usually when talks about exceptions and when considering exceptions one(we) clearly mentions the same.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 12, 2014 at 12:01 am
Great question...
The specified article specifies under 'Limitations and Restrictions' that indexes are not transferred to the new table and has to be created after executing the SELECT... INTO..
Limitations and Restrictions
You cannot specify a table variable or table-valued parameter as the new table.
You cannot use SELECT…INTO to create a partitioned table, even when the source table is partitioned. SELECT...INTO does not use the partition scheme of the source table; instead, the new table is created in the default filegroup. To insert rows into a partitioned table, you must first create the partitioned table and then use the INSERT INTO...SELECT FROM statement.
Indexes, constraints, and triggers defined in the source table are not transferred to the new table, nor can they be specified in the SELECT...INTO statement. If these objects are required, you can create them after executing the SELECT...INTO statement.
Specifying an ORDER BY clause does not guarantee the rows are inserted in the specified order.
When a sparse column is included in the select list, the sparse column property does not transfer to the column in the new table. If this property is required in the new table, alter the column definition after executing the SELECT...INTO statement to include this property.
When a computed column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computed at the time SELECT...INTO was executed.
March 12, 2014 at 1:48 am
BWFC (3/11/2014)
Toreador (3/11/2014)
Took a lot of thinking to work out all those double negatives!+1
March 12, 2014 at 9:15 am
Took a bit of research... Thanks for this great question!
March 12, 2014 at 10:00 am
Koen Verbeeck (3/11/2014)
Great question.Although the IDENTITY property doesn't always transfer:
When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:
*The SELECT statement contains a join, GROUP BY clause, or aggregate function.
*Multiple SELECT statements are joined by using UNION.
*The identity column is listed more than one time in the select list.
*The identity column is part of an expression.
*The identity column is from a remote data source.
+1. I thought certain conditions had to be in place for the identity not to be inherited!
March 17, 2014 at 3:25 pm
I suspect this is buried, but...
Do any of the correct answers reflect a change in SQL Server 2012, or did the SELECT...INTO statement work the same way under 2008/earlier editions?
_____________________________
Past performance != future results.
All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...
March 20, 2014 at 7:17 am
The double negatives got me - DOH!!!
December 5, 2014 at 12:52 am
Indexes are not copied.
Thanks.
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply