You need to ensure that you can view an estimated execution plan containing the most likely actions
You are a database developer. You plan to design a database solution by using SQL Server
2008. Users report poor performance of a long-running query in a database. You fail to
detect the cause of poor performance when you examine the estimated execution plan. On
viewing the actual execution plan, you discover that several tasks are CPU-intensive. You
need to ensure that you can view an estimated execution plan containing the most likely
actions. You want to achieve this goal by incurring minimum possible CPU overhead. What
should you do?
Which Transact-SQL query should you use?
You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year
marks for students. The table has marks obtained by 50 students for various subjects.
You need to ensure that the top half of the students arranged by their average marks must be given
a rank of 1 and the remaining students must be given a rank of 2. Which Transact-SQL query should
you use?
What should the design include?
You have a database that has 20 tables. The tables are not configured to have any
referential integrity. All tables have primary keys. Each table contains over 5 million rows.
Ten percent of the rows contain outdated information. You need to design a maintenance
process to delete all outdated rows. The solution must meet the following requirements: •
Minimize execution time • Minimize development effort • Minimize blocking other processes
• Prevent deletion failures from affecting the entire deletion process What should the design
include?
What should you include in the query?
You are creating a new update query. You need to ensure that the query terminates if a
shared lock is encountered white the data is updating. What should you include in the
query?
Which Transact-SQL query should you use?
You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year
marks for students. The table has marks obtained by 50 students for various subjects.
You need to ensure that the following requirements are met:
Students must be ranked based on their average marks.
If one or more students have the same average, the same rank must be given to these students.
Consecutive ranks must be skipped when the same rank is assigned.
Which Transact-SQL query should you use?
You need to ensure that the SQL Server 2008 server places the minimum possible number of locks on the table wh
You are a database developer. You plan to design a database solution by using SQL Server
2008. A database contains a large non-partitioned table with 4 million rows. You write a
stored procedure that updates approximately 4,000 rows in the table. The stored procedure
is scheduled to run only during peak hours. The stored procedure runs frequently. Users
report performance issues. You discover that when the stored procedure runs, it results in
memory contention because of a high number of locks placed on the table. You need to
ensure that the SQL Server 2008 server places the minimum possible number of locks on
the table while the stored procedure is being executed. What should you do?
Which Transact-SQL query should you use?
You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year
marks for students. The table has marks obtained by 50 students for various subjects.
You need to retrieve the students who scored the highest marks for each subject along with the
marks.
Which Transact-SQL query should you use?
You need to design a solution to guarantee that a stored procedure is part of every transaction
You need to design a solution to guarantee that a stored procedure is part of every
transaction. What should you do?
Which type of user should you create?
You use a contained database named ContosoDb within a domain. You need to create a user who
can log on to the ContosoDb database. You also need to ensure that you can port the database to
different database servers within the domain without additional user account configurations.
Which type of user should you create?
You need to minimize the number of blocking processes
You have a line-of-business application. The application generates reports based on data
from database v iews. U sers report that i t t akes a l ong t ime t o g enerate reports. Y ou r
un a S QL Server Profiler trace and discover that the application is generating many
blocking processes. You need to minimize the number of blocking processes. You must
achieve this goal without modifying the statements that the application sends to the server.
What should you do?