March 22, 2018 at 6:56 am
I'm studying for my 70-461 exam and have received a corker of a question in my practice test:
"You develop a database application. You create four tables. Each table stores different categories of products.
You create a Primary Key field on each table.
You need to ensure that the following requirements are met:
1) The fields must use the minimum amount of space.
2) The fields must be an incrementing series of values.
3) The values must be unique among the four tables.
What should you do?
A. Create a ROWVERSION column.
B. Create a SEQUENCE object that uses the INTEGER data type.
C. Use the INTEGER data type along with IDENTITY.
D. Use the UNIQUEIDENTIFIER data type along with NEWSEQUENTIALID()
E. Create a TIMESTAMP column."
The answer, according to the practice, is D. However I am curious as to why it's not B.
March 22, 2018 at 7:00 am
The only answer I can come up with without reading anything about sequences (haven't used them yet) is that the sequence could possibly be reset therefore resulting in duplicate values.
March 22, 2018 at 8:38 am
Yeah that is a bit odd to me as well. Clearly the only real considerations are using a sequence or a guid. I agree though that Lynn is probably correct that is possible to restart a sequence so it is possible there could be duplicates. But if being absurdly pedantic is important it is possible to get a duplicate guid (albeit ridiculously unlikely). But if using a uniqueidentifier it seems to violate the first condition because a guid requires so much more storage than an int. Also using newsequentialid does NOT ensure that the values will be unique across tables, which violates rule #3. Remember that newsequentialid is only guaranteed to by unique on the given machine until the next reboot.
Given the options here the only viable answer to me is to use a sequence. It violates the fewest of the requirements, and only violates them with manual intervention.
I would argue that the question is majorly flawed as the solution is not possible from the provided solution options.
_______________________________________________________________
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/
March 22, 2018 at 10:45 am
I don't think it's a good question. A sequence would work, and use less space. There are issues with them, but I'd think that. The author was probably thinking guaranteed uniqueness, which isn't really true, but perhaps close enough.
March 22, 2018 at 11:03 am
Steve Jones - SSC Editor - Thursday, March 22, 2018 10:45 AMI don't think it's a good question. A sequence would work, and use less space. There are issues with them, but I'd think that. The author was probably thinking guaranteed uniqueness, which isn't really true, but perhaps close enough.
But the answer choices explicitly say using newsequentialid which is only unique until the machine reboots. That isn't even really close to being unique.
_______________________________________________________________
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/
March 22, 2018 at 4:01 pm
I found it to be totally against my expectations. The problem with doing a practice test which is unofficial is you can never truly be sure if the author has the damned thing correct.
Thanks for all of your input.
March 22, 2018 at 9:51 pm
That is true. I'll say from my author work, we were required to take the beta or real exam and then build questions in the spirit of those. I'd hope that happened here, but you never know.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply