Exam 70-433 : SQL Server 2008 Part – 5

Question: Which of the following are T-SQL ranking functions that can be used for ranking data

1. ROW_NUMBER
2. DENSE_RANK
3. RANK
4. NTILE
5. All of the above.

Correct Answer: 5

---

Question: Which of the following options can be used for viewing lock status within your computer running SQL Server?

1. We can use SQL Profiler to capture lock and blocking information.
2. We can use the System Monitor that is part of the performance console to capture statistics on lock wait times, locks per second, and so on.
3. We can use the Activity Monitor in SSMS to see information on blocking processes.
4. We can use the sys.dm_tran_locks dynamic management view to gather information on locks being held by transactions.
5. All of the above.

Correct Answer: 5

---

Question: Which of the following is true?

1. BUILT-IN functions in SQL Server are Nondeterministic.
2. ISNULL function in SQL Server is Deterministic.
3. All of the above.
4. None of the above.

Correct Answer: 3

---

Question: What is the default join type used when only the keyword JOIN is specified?

1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN
3. SELF JOIN
4. INNER JOIN

Correct Answer: 4

---

Question: Which of the following is correct?

1. The DELETE statement logs information on each row deleted.
2. The TRUNCATE TABLE statement logs information on each row deleted.
3. The TRUNCATE TABLE statement executes fast and requires fewer resources on the server.
4. All of the above.
5. Only 1 and 3.

Correct Answer: 5

---

Question: Assuming UserProfile is a table containing a column Profession which accepts NULL value. What is the result of the below query.
SET ANSI_NULLS ON SELECT Profession FROM UserProfile WHERE (Profession NULL)

1. Returns records which has values in profession column
2. Gives an error.
3. Does not retrieve any records even if profession column contains values.
4. None of the above.

Correct Answer: 3

---

Question: Which of the following are new SQLSERVER 2008 date functions ?

1. SYSDATETIME
2. SYSDATETIMEOFFSET
3. SYSUTCDATETIME
4. All of the above.

Correct Answer: 1