You are a database developer. You plan to design a database solution by using SQL Server
2008. You create a stored procedure that uses the TRY/CATCH syntax in a new database.
When the stored procedure is executed, it logs information about each step in the TRY
block into a table named dbo.ExecutionLog. When an error occurs, the stored procedure
must perform the following tasks: ·Roll back the changes made to the target tables. ·Retain
the log entries stored in the dbo.ExecutionLog table. You need to ensure that the stored
procedure performs the given tasks. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server
2008. There are two schemas named Sales and Marketing. You are the owner of the Sales
schema and the Marketing schema is owned by a user named MarketingUser. Users of the
Marketing schema do not have permissions to access the Sales schema. You have
permissions to create objects in all schemas in the database. The Sales schema has a table
named Customers. You plan to create a stored procedure in the Marketing schema for the
marketing team. The stored procedure will select data from the Customers table and will be
owned by MarketingUser. You need to ensure that the marketing team is able to execute
the stored procedure. What should you do?

You have an instance of SQL Server 2008 that has xp_cmdshell enabled. You need to
design a stored procedure that meets the following requirements: ·Allows authorized users

to retrieve lists of files ·Minimizes permissions assigned to objects ·Minimizes security risks
What should you include in the design?

You are a database developer. You plan to design a database solution by using SQL Server
2008. A database contains a view that has the following features: ·It contains a WHERE
clause that filters specific records. ·It allows data updates. You need to prevent data
modifications that do not conform to the WHERE clause. You want to achieve this goal by
using minimum effort. What should you do?

You are a SQL Server 2008 developer. You create an online transaction processing (OLTP)
database by using SQL Server 2008 in an enterprise environment. The database contains a
table named SalesDetails. Each record in the table contains data in any one of the following
pairs of nullable columns: ·InternetSalesTargets and InternetSales ·ResellerSalesTargets
and ResellerSales ·ForeignSalesTargets and ForeignSales The table also contains three
NOT NULL key columns. A large number of records are inserted on a daily basis into the
SalesDetails table. Summary reports are generated from the SalesDetails table. Each report
is based on aggregated data from any one of the pairs of nullable columns. You need to
design a view or views to meet the following requirements: ·The SalesDetails table cannot
be directly modified. ·The performance of the reports is maximized. ·The amount of storage
space for each report is minimized. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server
2008. You have a database that contains a table and a table-valued function. The
table-valued function accepts the primary key from the table as a parameter. You plan to
write a query that joins the table to the results of the table-valued function. You need to
ensure that only rows from the table that produce a result set from the table-valued function
are returned. Which join predicate should you use?

You have a table that has 10 million rows. The table has a view that returns all of the rows.
You discover performance degradation when you run an unfiltered view. You need to
recommend a solution to replace the view. The solution must require that the returned data
is filtered by a parameter. What should you use?

You are a database developer. You plan to design a database solution by using SQL Server
2008. The database will contain a common language runtime (CLR) user-defined scalar
function. The function will return an integer value. You need to ensure that the computed
columns that use the result from this function can be indexed. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server
2008. You configure a database on a server to use a common language runtime (CLR). You
need to create a CLR assembly that enables the CLR stored procedure to access
environment variables available on the server. You also need to ensure that the CLR
assembly has the minimum permissions assigned. What should you do?

You are a database developer. You develop solutions by using SQL Server 2008 in an
enterprise environment. You plan to create a stored procedure that queries a sales table
and produces forecast data. You do not have administrative permissions, and you are not
the owner of the database. You have permissions to create stored procedures. Users will
only have permissions to execute your stored procedures. You need to ensure that users
can execute the stored procedures. What should you do?