Microsoft-TS Microsoft SQL Server 2008 - Database Development Set 1

You are a database developer writing reports for a sales management application. A customer table has the following definition: CREATE TABLE customer (CustomerID INT, FirstName VARCHAR(30), LastName VARCHAR(50), StreetAddress VARCHAR(100), City VARCHAR(100), [State] VARCHAR(25), PostalCode VARCHAR(5)); An order table has the following definition: CREATE TABLE [order] (OrderID INT, ProductID INT, CustomerID INT, OrderDate DATETIME); You need to write a report that contains the following columns: You also need to ensure that the report meets the following requirements:

A table named Contacts includes a column named SmtpAddress. You must develop a report that returns e-mail addresses from the Contacts table that have the following format: at least one character, the at sign (@), at least one character, and then ".org". You need to return data that meets the requirements. Which Transact-SQL statement should you use?

You have a column named TelephoneNumber that stores numbers as varchar(20). You need to write a query that returns the first three characters of a telephone number. Which expression should you use?

You are using SQL Server Profiler to gather deadlock information. You need to capture an XML description of a deadlock. Which event should you use?

You develop a new stored procedure for an existing database. You create two tables named Customer and Orders. The tables have the following definitions: CREATE TABLE Customer (CustomerID int NOT NULL PRIMARY KEY CLUSTERED, CustomerName nvarchar(255) NOT NULL, CustomerAddress nvarchar (1024) NOT NULL) CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY CLUSTERED, CustomerID int NOT NULL FOREIGN KEY REFERENCES Customer(CustomerID), OrderDetails nvarchar(MAX)) Users are restricted from accessing table objects directly. You need to ensure that users are able to retrieve customer data. You need to create a stored procedure that meets the following requirements:

A database contains tables named Sales and SalesArchive. SalesArchive contains historical sales data. You configure Change Tracking on the Sales table. The minimum valid version of the Sales table is 10. You need to write a query to export only sales data that changed since version 10, including the primary key of deleted rows. Which method should you use?

You administer a Microsoft SQL Server 2008 database that contains a stored procedure named dbo.SalesOrderDetails. The stored procedure has following definition: CREATE PROCEDURE dbo.SalesOrderDetails @CustomerID int, @OrderDate datetime, @SalesOrderID int AS SELECT h.SalesOrderID, h.OrderDate, d.OrderQty, d.ProductID FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesOrderDetail d ON d.SalesOrderID = h.SalesOrderID WHERE h.CustomerID = @CustomerID or h.OrderDate > @OrderDate or h.SalesOrderID > @SalesOrderID GO Parameter values passed to the stored procedure largely vary. You discover that the stored procedure executes quickly for some parameters but slowly for other parameters. You need to ensure that the query plan generated is optimized to provide the most consistent execution times for any set of parameters passed to the stored procedure. Which query hint should you use?

You notice that a database server is responding slowly to queries. You run the following dynamic management views (DMV) query on the server. SELECT TOP (10) wait_type, wait_time_ms FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC; The query returns a top wait type of SOS_SCHEDULER_YIELD. You need to identify what is causing the server response issues. Which resource should you investigate first?

You administer a Microsoft SQL Server 2008 database that contains a table named dbo.[order]. There are no triggers on the table. You plan to create a stored procedure that will have the following parameters:

You have a table named Inventory. You open a Microsoft Windows PowerShell session at the following location by using the SQL Server Windows PowerShell provider. PS SQLSERVER:\SQL\CONTOSO\DEFAULT\Databases\ReportServer\Tables\dbo.Inventory\Columns> Using the SQL Server Windows PowerShell provider, you need to query all the columns in the table. Which cmdlet should you use?