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

You need to build CREATE INDEX statements for all the missing indexes that SQL Server has identified. Which dynamic management view should you use?

You need to write a query that uses a ranking function that returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Which Transact-SQL statement should you use?

You administer a Microsoft SQL Server 2008 database. The database contains tables named Customer, Subscriptions, and Orders that have the following definitions: CREATE TABLE dbo.Customer ( CustomerID int NOT NULL PRIMARY KEY, FirstName varchar(255) NOT NULL, LastName varchar(255) NOT NULL, CustomerAddress varchar(1024)) CREATE TABLE dbo.Subscriptions (SubscriptionID int NOT NULL PRIMARY KEY SubscriptionName varchar(255) NOT NULL CustomerID int FOREIGN KEY NOT NULL REFERENCES Customer(CustomerID)) CREATE TABLE dbo.Orders ( OrderID int NOT NULL PRIMARY KEY OrderText varchar(255) NOT NULL CustomerID int FOREIGN KEY NOT NULL REFERENCES Customer(CustomerID)) Customers are considered active if they meet the following requirements:

You are the database developer for an order-processing application. After a customer places an order, a confirmation message must be sent to the customer. The following Transact-SQL batch has been run in the database: ALTER DATABASE NORTHWIND SET ENABLE_BROKER; CREATE MESSAGE TYPE EmailMessageVALIDATION = NONE; CREATE CONTRACT EmailContract(EmailMessage SENT BY INITIATOR); CREATE QUEUE EmailSendQueue; CREATE QUEUE EMailReceiveQueue; CREATE SERVICE EmailSendService ON QUEUE EmailSendQueue (EmailContract); CREATE SERVICE EmailReceiveService ON QUEUE EmailReceiveQueue (EmailContract); You need to place the message in the EmailSendQueue for the email system to process. Which Transact-SQL batch should you use?

You have a table named Sales.PotentialClients. This table contains a column named EmailAddress. You are tasked to develop a report that returns valid ".com" email addresses from Sales.PotentialClients. A valid email address must have at least one character before the @ sign, and one character after the @ sign and before the ".com." You need to write a Transact-SQL statement that returns data to meet the business requirements. Which Transact-SQL statement should you use?

Your server collation is SQL_Latin1_General_CP1_CI_AS. You have a database named Contoso that has a collation setting of SQL_Scandinavian_Cp850_CI_AS. You create and populate a temporary table #Person from table dbo.Person in Contoso using the following statements: USE Contoso; CREATE TABLE #Person (LastName nchar(128)); INSERT INTO #Person SELECT LastName FROM dbo.Person; You then run the following command: SELECT * FROM dbo.Person a JOIN #Person b ON a.LastName = b.LastName; This command returns the following error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Scandinavian_Cp850_CI_AS" in the equal to operation. You need to resolve the collation conflict. Which Transact-SQL statement should you use?

You have a server named Contoso with multiple databases. You have been tasked to write a PowerShell script to determine which databases on the server are larger than 100GB. You open PowerShell from SQL Server Management Studio. You create two variables as follows: PS SQLSERVER:\SQL\Contoso> $MultipleOfGB = 1024 * 1024 PS SQLSERVER:\SQL\Contoso>$Server = Get-Item You need to determine which script will produce the desired list of databases. What script should you use?

You are updating a database table. You need to partition the table to store only the last 1000 rows of data in the table. What should you do?

You have two tables named dbo.CurrentProducts and dbo.ArchiveProducts. You have the following query: SELECT ProductID, Name FROM dbo.CurrentProducts UNION ALL SELECT ProductID, NameFROM dbo.ArchiveProducts; You need to predict the list of products that the query will produce. Which list of products should the query return?

You need to identify which products will be inserted when you execute the following code block. BEGIN TRANSACTION INSERT INTO Product (ProductName) VALUES ('food') BEGIN TRANSACTION INSERT INTO Product (ProductName)) VALUES ('beverage') COMMIT TRANSACTION ROLLBACK TRANSACTION Which products will be inserted?