Few tricky Questions about SQL Server 2008 that I came across...

Question

Post reply

Few tricky Questions about SQL Server 2008 that I came across...

Viewing 7 posts - 31 through 36 (of 36 total)

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

beginner_dk SSC-Addicted Points: 453 More actions · Answer 1

Got it! Thanks Gila Monster.

Thanks and Regards

DK 🙂

sidor 79242 Newbie Points: 9 More actions · Answer 2

I just took this test, and the CREATE LOGIN is now looking like this. When I pasted all four into a SQL Query, I get syntax errors on the last 3. I selected the last one as the incorrect one, but the reality is, these questions are fairly stupid.

CREATE LOGIN [HumanResources\JohnSmith] FROM WINDOWS

CREATE LOGIN AndrewWalters WITH PASSWORD ='abcxyz123', DEFAULT DATABASE = HR

CREATE LOGIN PatriciaSmith WITH PASSWORD ='Abcxyz$123'

CREATE LOGIN [HumanResources\CarlyMason] FROM WINDOWS, DEFAULT DATABASE=CRM

SABARISH JAGANATHAN Newbie Points: 7 More actions · Answer 3

hi,

kindly could you able tell me the answer's for those question's

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

SABARISH JAGANATHAN (8/21/2013)
hi,
kindly could you able tell me the answer's for those question's

Try reading all the comments. Most of them have no correct answer.

_______________________________________________________________

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/

TomThomson SSC Guru Points: 104773 More actions · Answer 5

GilaMonster (3/19/2013)
For B to be correct, it needs ORDER BY CustomerName added at the end.

Strictly speaking that's not true, as it doesn't need ORDER BY CustomerName, as it could have ORDER BY RN instead and achieve the same effect (since RN is in the select list and the prder for the row numbering is specified to be by CustomerName). Being a lazy person, I might actually use ORDER BY RN because typing RN is less effort than typing CustomerName.

I agree or course that all these questions are appalling drivel.

Tom

MysticMetal+SQLServerCentral SSC Enthusiast Points: 107 More actions · Answer 6

GilaMonster (3/20/2013)
beginner_dk (3/19/2013)
Regarding sorting order, i went through the BOL over clause and tested on a table created my self and it worked. so i thought it might be true.
"It worked for me once" != "It's guaranteed to work". No order by, no guarantee of order, end of story.
Now, a simple query like that will probably come back in the correct order, but that's a side effect of how the query execution engine works, not a guaranteed behaviour

The question asks "Which of the following statements can be used to provide sorted result set based in the CustomerName column" not "Which of the following statements will provide sorted result set based in the CustomerName column", so there is a single best answer, if you assume these things:

1*) The end system can sort numeric columns, it just can't sort string columns.

2*) It's ok to force the end system to sort the results, even though it'd be a lot more efficient in SQL server (and was probably already done by the server to generate the answer).

3) It ok for the RN column to be non-deterministic for any CustomerName values that are duplicated.

*1 & 2 could be combined into: The query is going to be part of a larger query, which just wants to figure out which X number of customers to use (based on their name for some reason) and will use the numbers in the larger query (so we can't just "select top #").**

** assuming this is the case, then I don't see how there could be a better answer without just adding something to break ties, since you can't do a "order by" in a subquery.

----------

Two cents from someone who just finished the same test and was wondering what was up with some of the questions / what 9 questions I got wrong/what the correct answers were...

MysticMetal+SQLServerCentral SSC Enthusiast Points: 107 More actions · Answer 7

GilaMonster (1/10/2012)
JagWalia (1/10/2012)
Q: Which of the following allows for the truncation of a transaction log?
A. Transaction logs are truncated when you use SQL Profiler.
B. Transaction logs are truncated when you use the following syntax: truncate table A
C. Transaction logs are truncated when you backup your database.
D. You can not backup transaction log.
None of the above. They're all wrong.
Q: Which of the following statements about unique Constraints are TRUE?
A. You can have only one unique constraint applied to a table.
B. Unique constraints can be used to establish a link between two tables.
C. You can only have one column assigned to a unique constraint.
D. If a primary key was already assigned to a table, the unique constraint would identify a special column used for indexing,
None of the above. They're all wrong.

That's what I was thinking, although I thought the transaction logs might be truncated when you backup your database and thought the statements about unique constraints were all wrong (although D might be the best answer, since SQL Server 2008 creates a unique index when you create a unique constraint...).