Note: The answer is for reference only, you need to understand all question.
Exam A
QUESTION 1
You need to create a stored procedure that accepts a table-valued parameter named @Customers.
Which code segment should you use?
A.
CREATE PROCEDURE AddCustomers (@Customers varchar(max))
B.
CREATE PROCEDURE AddCustomers (@Customers Customer READONLY)
C.
CREATE PROCEDURE AddCustomers (@Customers CustomerType OUTPUT)
D.
CREATE PROCEDURE ADDCUSTOMERS
(@Customers varchar (max)) AS EXTERNAL NAME Customer.Add.NewCustomer
Answer: B
QUESTION 2
You are writing a batch that contains multiple UPDATE statements to modify existing products. You have placed these updates into one explicit transaction. You need to set an option at the beginning of the transaction to roll back all changes if any of the updates in the transaction fail. Which option should you enable?
A.
ARITHABORT
B.
XACT_ABORT
C.
IMPLICIT_TRANSACTIONS
D.
REMOTE_PROC_TRANSACTIONS
Answer: B
QUESTION 3
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?
A.
LEFT(TelephoneNumber, 3)
B.
SUBSTRING(TelephoneNumber, 3, 3)
C.
SUBSTRING (TelephoneNumber, 3, 1)
D.
CHARINDEX('[0-9][0-9][0-9]', TelephoneNumber, 3)
Answer: A
QUESTION 4
You have an application that is used by international clients. All clients connect by using Windows Authentication.
You need to ensure that system and user-defined error messages are displayed in the localized language for the clients. What should you do? (Each correct answer represents part of the solution. Choose two.)
A.
Use @@LANGUAGE function
B.
Use default language for each login
C.
Use @lang parameter of sp_addmessage
D.
Use the "set language" option of sp_configure
Answer: BC
QUESTION 5
You have implemented change tracking on a table named Sales.SalesOrder.
You need to determine all columns that have changed since the minimum valid version.
Which function should you use?
A.
CHANGE_TRACKING_CURRENT_VERSION
B.
CHANGE_TRACKING_IS_COLUMN_IN_MASK
C.
CHANGETABLE with the CHANGES argument
D.
CHANGETABLE with the VERSION argument
Answer: C
QUESTION 6
You need to build CREATE INDEX statements for all the missing indexes that SQL Server has identified.
Which dynamic management view should you use?
A.
sys.dm_db_index_usage_stats
B.
sys.dm_db_missing_index_details
C.
sys.dm_db_missing_index_columns
D.
sys.dm_db_missing_index_group_stats
Answer: B
QUESTION 7
You administer a SQL Server 2008 database that contains a table name dbo.Sales, which contains the following table definition:
CREATE TABLE [dbo].[Sales](
[SalesID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalesPersonID] [int] NULL,
[CommentDate] [date] NULL);
This table contains millions of orders. You run the following query to determine when sales persons comment in the dbo.Sales table:
SELECT SalesID,CustomerID,SalesPersonID,CommentDate FROM dbo.Sales
WHERE CommentDate IS NOT NULL AND SalesPersonID IS NOT NULL;
You discover that this query runs slow. After examining the data, you find only 1% of rows have comment dates and the SalesPersonID is null on 10% of the rows. You need to create an index to optimize the query. The index must conserve disk space while optimizing your query. Which index should you create?
A.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CustomerID)
INCLUDE (CommentDate,SalesPersonID);
B.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (SalesPersonID)
INCLUDE (CommentDate,CustomerID);
C.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CustomerID)
INCLUDE(CommentDate)
WHERE SalesPersonID IS NOT NULL;
D.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CommentDate, SalesPersonID)
INCLUDE(CustomerID)
WHERE CommentDate IS NOT NULL;
Answer: D
QUESTION 8
Your company manufactures and distributes bicycle parts. You have a full-text catalog on the Inventory table which contains the PartName and Description columns. You also use a full-text thesaurus to expand common bicycle terms. You need to write a full-text query that will not only match the exact word in the search, but also the meaning.
Which Transact-SQL statement should you use?
A.
SELECT * FROM Inventory
WHERE FREETEXT (*, 'cycle'))
B.
SELECT * FROM Inventory
WHERE CONTAINS (*, 'cycle')
C.
SELECT * FROM Inventory
WHERE Description LIKE '%cycle%'
D.
SELECT * FROM Inventory
WHERE CONTAINS (*, 'FormsOf(Inflectional, cycle)')
Answer: A
QUESTION 9
You need to capture and record a workload for analysis by the Database Engine Tuning Advisor (DTA).
Which tool should you use?
A.
DTA utility
B.
Activity Monitor
C.
SQL Server Profiler
D.
Performance Monitor
Answer: C
QUESTION 10
You administer a SQL Server 2008 database that contains a table name dbo.Sales, which contains the following table definition:
CREATE TABLE [dbo].[Sales](
[SalesID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalesPersonID] [int] NULL,
[CommentDate] [date] NULL);
This table contains millions of orders. You run the following query to determine when sales persons comment in the dbo.Sales table:
SELECT SalesID,CustomerID,SalesPersonID,CommentDate FROM dbo.Sales
WHERE CommentDate IS NOT NULL AND SalesPersonID IS NOT NULL;
You discover that this query runs slow. After examining the data, you find only 1% of rows have comment dates and the SalesPersonID is null on 10% of the rows. You need to create an index to optimize the query. The index must conserve disk space while optimizing your query. Which index should you create?
A.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CustomerID)
INCLUDE (CommentDate,SalesPersonID);
B.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (SalesPersonID)
INCLUDE (CommentDate,CustomerID);
C.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CustomerID)
INCLUDE(CommentDate)
WHERE SalesPersonID IS NOT NULL;
D.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CommentDate, SalesPersonID)
INCLUDE(CustomerID)
WHERE CommentDate IS NOT NULL;
Answer: D
QUESTION 11
You have been tasked with creating a table named dbo.Widgets. You need to insert five rows into the dbo.Widgets table and return WidgetID for each of the five rows that have been inserted. Which Transact-SQL batch should you use?
A.
CREATE TABLE dbo.Widgets ( WidgetID INT IDENTITY PRIMARY KEY, WidgetName VARCHAR(25));
GO INSERT dbo.Widgets (WidgetName) OUTPUT inserted.WidgetID, inserted.WidgetName VALUES ('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');
B.
CREATE TABLE dbo.Widgets ( WidgetID INT IDENTITY PRIMARY KEY, WidgetName VARCHAR(25) );
GO
INSERT dbo.Widgets (WidgetName)
VALUES
('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');
SELECT SCOPE_IDENTITY();
C.
CREATE TABLE dbo.Widgets ( WidgetID UNIQUEIDENTIFIER PRIMARY KEY, WidgetName VARCHAR(25) );
GO
INSERT dbo.Widgets (WidgetName)
VALUES ('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');
SELECT SCOPE_IDENTITY();
D.
CREATE TABLE dbo.Widgets ( WidgetID UNIQUEIDENTIFIER PRIMARY KEY, WidgetName VARCHAR(25));
GO INSERT dbo.Widgets (WidgetName) OUTPUT inserted.WidgetID, inserted.WidgetName VALUES
('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');
Answer: A
QUESTION 12
You have been tasked to delete a number of Database Mail messages that have been sent.
You need to delete all the emails that were sent more than one month ago.
Which Transact-SQL statements should you run?
A.
DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
EXEC msdb.dbo.sysmail_delete_log_sp @OneMonthAgo
B.
DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
EXEC msdb.dbo.sysmail_delete_mailitems_sp @OneMonthAgo
C.
DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
EXEC msdb.dbo.sysmail_delete_log_sp @OneMonthAgo,'Success'
D.
DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
EXEC msdb.dbo.sysmail_delete_mailitems_sp @OneMonthAgo,'Sent'
Answer: D
Exam B
QUESTION 1
You have a user named John. He has SELECT access to the Sales schema. You need to eliminate John's SELECT access rights from the Sales.SalesOrder table without affecting his other permissions.
Which Transact-SQL statement should you use?
A.
DROP USER John;
B.
DENY SELECT ON Sales.SalesOrder TO John;
C.
GRANT DELETE ON Sales.SalesOrder TO John;
D.
REVOKE SELECT ON Sales.SalesOrder FROM John;
Answer: B
QUESTION 2
You need to create a column that allows you to create a unique constraint.
Which two column definitions should you choose? (Each correct answer presents a complete solution. Choose two.)
A.
nvarchar(100) NULL
B.
nvarchar(max) NOT NULL
C.
nvarchar(100) NOT NULL
D.
nvarchar(100) SPARSE NULL
Answer: AC
QUESTION 3
You manage a SQL Server 2008 database that is located at your company's corporate headquarters.
The database contains a table named dbo.Sales. You need to create different views of the dbo.Sales table that will be used by each region to insert, update, and delete rows. Each regional office must only be able to insert, update, and delete rows for their respective region.
Which view should you create for Region1?
A.
CREATE VIEW dbo.Region1Sales
AS
SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales
WHERE RegionID = 1;
B.
CREATE VIEW dbo.Region1Sales
AS
SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales
WHERE RegionID = 1 WITH CHECK OPTION;
C.
CREATE VIEW dbo.Region1Sales
WITH SCHEMABINDING
AS SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales WHERE RegionID = 1;
D.
CREATE VIEW dbo.Region1Sales
WITH VIEW_METADATA
AS
SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales
WHERE RegionID = 1;
Answer: B
QUESTION 4
You administer a SQL Server 2008 database that contains a table name dbo.Sales, which contains the following table definition:
CREATE TABLE [dbo].[Sales](
[SalesID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalesPersonID] [int] NULL,
[CommentDate] [date] NULL);
This table contains millions of orders. You run the following query to determine when sales persons comment in the dbo.Sales table:
SELECT SalesID,CustomerID,SalesPersonID,CommentDate FROM dbo.Sales
WHERE CommentDate IS NOT NULL AND SalesPersonID IS NOT NULL;
You discover that this query runs slow. After examining the data, you find only 1% of rows have comment dates and the SalesPersonID is null on 10% of the rows. You need to create an index to optimize the query. The index must conserve disk space while optimizing your query. Which index should you create?
A.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CustomerID)
INCLUDE (CommentDate,SalesPersonID);
B.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (SalesPersonID)
INCLUDE (CommentDate,CustomerID);
C.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CustomerID)
INCLUDE(CommentDate)
WHERE SalesPersonID IS NOT NULL;
D.
CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CommentDate, SalesPersonID)
INCLUDE(CustomerID)
WHERE CommentDate IS NOT NULL;
Answer: D
QUESTION 5
Your database is 5GB and contains a table named SalesHistory. Sales information is frequently inserted and updated.
You discover that excessive page splitting is occurring.
You need to reduce the occurrence of page splitting in the SalesHistory table.
Which code segment should you use?.
A.
ALTER DATABASE Sales MODIFY FILE
(NAME = Salesdat3, SIZE = 10GB);
B.
ALTER INDEX ALL ON Sales.SalesHistory REBUILD WITH (FILLFACTOR = 60);
C.
EXEC sys.sp_configure 'fill factor (%)', '60';
D.
UPDATE STATISTICS Sales.SalesHistory(Products)
WITH FULLSCAN, NORECOMPUTE;
Answer: B
QUESTION 6
You have a table named dbo.Customers. The table was created by using the following Transact-SQL statement:
CREATE TABLE dbo.Customers
(
CustomerID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
AccountNumber nvarchar(25) NOT NULL,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
AddressLine1 nvarchar(255) NOT NULL,
AddressLine2 nvarchar(255) NOT NULL,
City nvarchar(50) NOT NULL,
StateProvince nvarchar(50) NOT NULL,
Country nvarchar(50) NOT NULL,
PostalCode nvarchar(50) NOT NULL,
CreateDate datetime NOT NULL DEFAULT(GETDATE()),
ModifiedDate datetime NOT NULL DEFAULT(GETDATE())
)
You create a stored procedure that includes the AccountNumber, Country, and StateProvince columns from the dbo.Customers table. The stored procedure accepts a parameter to filter the output on the AccountNumber column. You need to optimize the performance of the stored procedure. You must not change the existing structure of the table. Which Transact-SQL statement should you use?
A.
CREATE STATISTICS ST_Customer_AccountNumber ON dbo.Customer (AccountNumber) WITH FULLSCAN;
B.
CREATE CLUSTERED INDEX IX_Customer_AccountNumber ON dbo.Customer (AccountNumber);
C.
CREATE NONCLUSTERED INDEX IX_Customer_AccountNumber ON dbo.Customer (AccountNumber) WHERE AccountNumber = '';
D.
CREATE NONCLUSTERED INDEX IX_Customer_AccountNumber ON dbo.Customer (AccountNumber) INCLUDE (Country, StateProvince);
Answer: D
QUESTION 7
You have a table named Customer.
You need to ensure that customer data in the table meets the following requirements:
credit limit must be zero unless customer identification has been verified.
credit limit must be less than 10,000.
Which constraint should you use?
A.
CHECK (CreditLimt BETWEEN 1 AND 10000)
B.
CHECK (Verified = 1 AND CreditLimt BETWEEN 1 AND 10000)
C.
CHECK ((CreditLimt = 0 AND Verified = 0) OR (CreditLimt BETWEEN 1 AND 10000 AND Verified = 1))
D.
CHECK ((CreditLimt = 0 AND Verified = 0) AND (CreditLimt BETWEEN 1 AND 10000 AND Verified = 1))
Answer: C
QUESTION 8
You have a table named AccountsReceivable. The table has no indexes. There are 75,000 rows in the
table. You have a partition function named FG_AccountData. The AccountsReceivable table is defined in
the following Transact-SQL statement:
CREATE TABLE AccountsReceivable (
column_a INT NOT NULL,
column_b VARCHAR(20) NULL)
ON [PRIMARY];
You need to move the AccountsReceivable table from the PRIMARY file group to FG_AccountData. Which Transact-SQL statement should you use?
A.
CREATE CLUSTERED INDEX idx_AccountsReceivable ON AccountsReceivable(column_a) ON [FG_AccountData];
B.
CREATE NONCLUSTERED INDEX idx_AccountsReceivable ON AccountsReceivable(column_a) ON [FG_AccountData];
C.
CREATE CLUSTERED INDEX idx_AccountsReceivable ON AccountsReceivable(column_a) ON FG_AccountData(column_a);
D.
CREATE NONCLUSTERED INDEX idx_AccountsReceivable ON AccountsReceivable(column_a) ON FG_AccountData(column_a);
Answer: C
QUESTION 9
You have a SQL Server 2008 database named Contoso with a table named Invoice. The primary key of the table is InvoiceId, and it is populated by using the identity property. The Invoice table is related to the InvoiceLineItem table. You remove all constraints from the Invoice table during a data load to increase load speed. You notice that while the constraints were removed, a row with InvoiceId = 10 was removed from the database. You need to re-insert the row into the Invoice table with the same InvoiceId value. Which Transact-SQL statement should you use?
A.
INSERT INTO Invoice (InvoiceId, ... VALUES (10, ...
B.
SET IDENTITY_INSERT Invoice ON;
INSERT INTO Invoice (InvoiceId, ...
VALUES (10, ...
SET IDENTITY_INSERT Invoice OFF;
C.
ALTER TABLE Invoice;
ALTER COLUMN InvoiceId int;
INSERT INTO Invoice (InvoiceId, ...
VALUES (10, ...
D.
ALTER DATABASE Contoso SET SINGLE_USER;
INSERT INTO Invoice (InvoiceId, ...
VALUES (10, ...
ALTER DATABASE Contoso SET MULTI_USER;
Answer: B
QUESTION 10
You are developing a new database. The database contains two tables named SalesOrderDetail and Product.
You need to ensure that all products referenced in the SalesOrderDetail table have a corresponding record in the Product table.
Which method should you use?
A.
JOIN
B.
DDL trigger
C.
Foreign key constraint
D.
Primary key constraint
Answer: C
QUESTION 11
You are creating a table that stores the GPS location of customers.
You need to ensure that the table allows you to identify customers within a specified sales boundary and to calculate the distance between a customer and the nearest store.
Which data type should you use?
A.
geometry
B.
geography
C.
nvarchar(max)
D.
varbinary(max) FILESTREAM
Answer: B
QUESTION 12
You plan to add a new column named SmallKey to the Sales.Product table that will be used in a
unique constraint. You are required to ensure that the following information is applied when adding the
new column:
'a1' and 'A1' are treated as different values
'a' and 'A' sort before 'b' and 'B' in an ORDER BY clause You need to select the collation that meets the requirements for the new column. Which collation should you select?
A.
Latin1_General_BIN
B.
SQL_Latin1_General_CP1_CI_AI
C.
SQL_Latin1_General_CP1_CI_AS
D.
SQL_Latin1_General_CP1_CS_AS
Answer: D
QUESTION 13
You have multiple tables that represent properties of the same kind of entities. The property values are comprised of text, geometry, varchar(max), and user-defined types specified as 'bit NOT NULL' data types.
You plan to consolidate the data from multiple tables into a single table. The table will use semi-structured storage by taking advantage of the SPARSE option.
You are tasked to identify the data types that are compatible with the SPARSE option.
Which data type is compatible with the SPARSE option?
A.
text
B.
geometry
C.
varchar(max)
D.
A user-defined type defined as 'bit NOT NULL'
Answer: C
QUESTION 14
You currently store date information in two columns. One column contains the date in local time and one column contains the difference between local time and UTC time. You need to store this data in a single column.
Which data type should you use?
A.
time
B.
datetime2
C.
datetime2(5)
D.
datetimeoffset
Answer: D
QUESTION 15
You have two partitioned tables named Transaction and TransactionHistory.
You need to archive one of the partitions of the Transaction table to the TransactionHistory table.
Which method should you use?
A.
ALTER TABLE ...
SWITCH ...
B.
INSERT ... SELECT ...; TRUNCATE TABLE
C.
ALTER PARTITION FUNCTION ... MERGE ...
D.
ALTER PARTITION FUNCTION ...
SPLIT ...
Answer: B
QUESTION 16
You are creating a new table in a database. Your business requires you to store data in the table for only seven days.
You need to implement a partitioned table to meet this business requirement.
Which tasks should you complete?
A.
Create the partition function
Create the partition scheme
Create the table
B.
Create the partition function
Create the table
Create a filtered index
C.
Add a secondary file to the primary filegroups
Create the table
Create the distributed partitioned view
D.
Create the partition function
Create the partition scheme
Create the distributed partitioned view
Answer: A
QUESTION 17
You need to alter stored procedures to use the WITH RECOMPILE option. Which types of stored procedures should you alter? (Each correct answer represents a complete solution. Choose two.)
A.
Stored procedures implemented from CLR assemblies.
B.
Stored procedures that require the FOR REPLICATION option.
C.
Stored procedures that require the WITH ENCRYPTION option.
D.
Stored procedures that contain queries that use the OPTION (RECOMPILE) hint.
Answer: CD
QUESTION 18
You have a SQL Server database. The database contains two schemas named Marketing and Sales.
The Marketing schema is owned by a user named MarketingManager. The Sales schema is owned by a user named SalesManager.
A user named John must be able to access the Sales.Orders table by using a stored procedure named Marketing.GetSalesSummary.
John is not granted a SELECT permission on the Sales.Orders table.
A user named SalesUser does have SELECT permission on the Sales.Orders table.
You need to implement appropriate permissions for John and the stored procedure Marketing.GetSalesSummary.
What should you do?
A.
Marketing.GetSalesSummary should be created by using the EXECUTE AS 'SalesUser' clause.
John should be granted EXECUTE permission on Marketing.GetSalesSummary.
B.
Marketing.GetSalesSummary should be created by using the EXECUTE AS OWNER clause.
John should be granted EXECUTE WITH GRANT OPTION on Marketing.GetSalesSummary.
C.
Marketing.GetSalesSummary should be created by using the EXECUTE AS CALLER clause.
John should be granted IMPERSONATE permission for the user named SalesUser.
D.
Marketing.GetSalesSummary should be created without an EXECUTE AS clause.
John should be granted SELECT permission on the Sales.Orders table.
Answer: A
QUESTION 19
You need to create a stored procedure that accepts a table-valued parameter named @Customers.
Which code segment should you use?
A.
CREATE PROCEDURE AddCustomers (@Customers varchar(max))
B.
CREATE PROCEDURE AddCustomers (@Customers Customer READONLY)
C.
CREATE PROCEDURE AddCustomers (@Customers CustomerType OUTPUT)
D.
CREATE PROCEDURE ADDCUSTOMERS
(@Customers varchar (max)) AS EXTERNAL NAME Customer.Add.NewCustomer
Answer: B
QUESTION 20
You have a computed column that is implemented with a user-defined function. The user-defined function returns a formatted account number. The column must be indexed to provide adequate search performance.
You plan to create an index on the computed column. You need to identify the valid combination of ObjectPropertyEX values for the user-defined function.
Which combination should you use?
A.
IsDeterministic = True
IsSystemVerified = True
UserDataAccess = False
SystemDataAccess = False
B.
IsDeterministic = True
IsSystemVerified = True
IsPrecise = True
IsTableFunction = True
C.
IsDeterministic = False
IsSystemVerified = True
UserDataAccess = False
SystemDataAccess = False
D.
IsDeterministic = False
IsSystemVerified = True
IsPrecise = True
SystemDataAccess = False
Answer: A
QUESTION 21
You need to identify, within a given clause, if the month of February will contain 29 days for a specified year.
Which object should you use?
A.
DML trigger
B.
Stored procedure
C.
Table-valued function
D.
Scalar-valued function
Answer: D
QUESTION 22
You are creating a function that references a table.
You need to prevent the table from being dropped.
Which option should you use when you create the function?
A.
WITH ENCRYPTION
B.
WITH EXECUTE AS
C.
WITH SCHEMABINDING
D.
WITH RETURNS NULL ON NULL INPUT
Answer: C
QUESTION 23
You are developing a database using Microsoft SQL Server 2008. The database contains the tables shown in the exhibit. You are required to prevent parts from being deleted if they belong to a kit. If a part belongs to a kit, the delete should not occur and the IsDeleted column for the row should be changed to 'True'. Parts can be deleted if they do not belong to a kit. You have the following Transact-SQL statement to be used in a trigger:
UPDATE p SET IsDeleted = 1 FROM KitPart kp
JOIN deleted d ON kp.PartID = d.PartID
JOIN Part p ON kp.PartID = p.PartID; DELETE FROM p FROM Part p
JOIN deleted d ON p.PartID = d.PartID
LEFT OUTER JOIN KitPart kp ON p.PartID = kp.PartID WHERE kp.KitID IS NULL;
You need to implement the Transact-SQL statement in a trigger. Which trigger syntax should you use?
Exhibit:
A.
CREATE TRIGGER tr_Part_d ON Part AFTER DELETE AS BEGIN
END
B.
CREATE TRIGGER tr_Part_d ON Part INSTEAD OF DELETE AS BEGIN
END
C.
CREATE TRIGGER tr_KitPart_d ON KitPart AFTER DELETE AS BEGIN
END
D.
CREATE TRIGGER tr_KitPart_d ON KitPart INSTEAD OF DELETE AS BEGIN
END
Answer: B
QUESTION 24
You have a third-party application that inserts data directly into a table.
You add two new columns to the table. These columns cannot accept NULL values and cannot use default constraints.
You need to ensure that the new columns do not break the third-party application.
What should you do?
A.
Create a DDL trigger.
B.
Create a stored procedure.
C.
Create an AFTER INSERT trigger.
D.
Create an INSTEAD OF INSERT trigger.
Answer: D
QUESTION 25
Your database contains two tables named Order and OrderDetails that store order information. They relate to each other using the OrderID column in each table. Your business requires that the LastModifiedDate column in the Order table must reflect the date and time when a change is made in the OrderDetails table for the related order.
You need to create a trigger to implement this business requirement.
Which Transact-SQL statement should you use?
A.
CREATE TRIGGER [uModDate] ON [OrderDetails]
INSTEAD OF UPDATE FOR REPLICATION
AS
UPDATE [Order]
SET [LastModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[OrderID] = [Order].[OrderID];
B.
CREATE TRIGGER [uModDate] ON [Order]
INSTEAD OF UPDATE NOT FOR REPLICATION
AS
UPDATE [Order]
SET [LastModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[OrderID] = [Order].[OrderID];
C.
CREATE TRIGGER [uModDate] ON [Order]
AFTER UPDATE FOR REPLICATION
AS
UPDATE [Order]
SET [LastModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[OrderID] = [Order].[OrderID];
D.
CREATE TRIGGER [uModDate] ON [OrderDetails]
AFTER UPDATE NOT FOR REPLICATION
AS
UPDATE [Order]
SET [LastModifiedDate] = GETDATE()
FROM inserted
WHERE inserted.[OrderID] = [Order].[OrderID];
Answer: D
QUESTION 26
You need to ensure that tables are not dropped from your database. What should you do?
A.
Create a DDL trigger that contains COMMIT.
B.
Create a DML trigger that contains COMMIT.
C.
Create a DDL trigger that contains ROLLBACK.
D.
Create a DML trigger that contains ROLLBACK.
Answer: C
QUESTION 27
You are responsible for a SQL Server database. You require the tables to be added or altered only on the first day of the month. You need to ensure that if the tables are attempted to be modified or created on any other day, an error is received and the attempt is not successful.
Which Transact-SQL statement should you use?
A.
CREATE TRIGGER TRG_TABLES_ON_FIRST
ON DATABASE FOR CREATE_TABLE
AS
IF DATEPART(day,getdate())>1
BEGIN
RAISERROR ('Must wait til next month.', 16, 1)
END
B.
CREATE TRIGGER TRG_TABLES_ON_FIRST ON DATABASE FOR CREATE_TABLE,ALTER_TABLE AS
IF DATEPART(day,getdate())>1
BEGIN
RAISERROR ('Must wait til next month.', 16, 1)
END
C.
CREATE TRIGGER TRG_TABLES_ON_FIRST ON DATABASE FOR CREATE_TABLE,ALTER_TABLE AS
IF DATEPART(day,getdate())>1
BEGIN
ROLLBACK
RAISERROR ('Must wait til next month.', 16, 1)
END
D.
CREATE TRIGGER TRG_TABLES_ON_FIRST ON ALL SERVER FOR ALTER_DATABASE AS
IF DATEPART(day,getdate())>1
BEGIN
ROLLBACK
RAISERROR ('Must wait til next month.', 16, 1)
END
Answer: C
QUESTION 28
You have a single CLR assembly in your database. The assembly only references blessed assemblies from the Microsoft .NET Framework and does not access external resources.
You need to deploy this assembly by using the minimum required permissions. You must ensure that your database remains as secure as possible.
Which options should you set?
A.
PERMISSION_SET = SAFE
TRUSTWORTHY ON
B.
PERMISSION_SET = SAFE
TRUSTWORTHY OFF
C.
PERMISSION_SET = UNSAFE
TRUSTWORTHY ON
D.
PERMISSION_SET = EXTERNAL_ACCESS
TRUSTWORTHY OFF
Answer: B
QUESTION 29
You have created an assembly that utilizes unmanaged code to access external resources.
You need to deploy the assembly with the appropriate permissions.
Which permission set should you use?
A.
SAFE
B.
UNSAFE
C.
EXTERNAL_ACCESS
D.
Default permission set
Answer: B
QUESTION 30
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?
A.
LEFT(TelephoneNumber, 3)
B.
SUBSTRING(TelephoneNumber, 3, 3)
C.
SUBSTRING (TelephoneNumber, 3, 1)
D.
CHARINDEX('[0-9][0-9][0-9]', TelephoneNumber, 3)
Answer: A
QUESTION 31
You are using TRY...CATCH error handling.
You need to raise an error that will pass control to the CATCH block.
Which severity level should you use?
A.
0
B.
9
C.
10
D.
16
Answer: D
QUESTION 32
You have a table named Orders. You have been tasked to modify your company's main database to remove all inactive order rows. You are developing a stored procedure that will enable you to delete these rows. You have written the following code segment to accomplish this task. (Line numbers are included for reference only.) 01 BEGIN TRY
02 DECLARE @RowCount INT = 1000
03 WHILE @RowCount = 1000
04 BEGIN
05 DELETE TOP (1000) FROM Orders WHERE Status = 'Inactive';
06 SET @RowCount = @@ROWCOUNT
07 ...
08 END
09 END TRY
10 BEGIN CATCH
11 PRINT ERROR_MESSAGE()
12 END CATCH
You need to insert a Transact-SQL statement that will notify you immediately after each batch of rows is deleted. Which Transact-SQL statement should you insert at line 07?
A.
RAISERROR ('Deleted %i rows', 6, 1, @RowCount)
B.
RAISERROR ('Deleted %i rows', 16, 1, @RowCount)
C.
RAISERROR ('Deleted %i rows', 10, 1, @RowCount) WITH NOWAIT
D.
RAISERROR ('Deleted %i rows', 11, 1, @RowCount) WITH NOWAIT
Answer: C
QUESTION 33
You have a transaction that uses the repeatable read isolation level.
This transaction causes frequent blocking problems. You need to reduce blocking. You also need to avoid dirty reads and non-repeatable reads.
Which transaction isolation level should you use?
A.
SNAPSHOT
B.
SERIALIZABLE
C.
READ COMMITTED
D.
READ UNCOMMITTED
Answer: A
QUESTION 34
You are writing a batch that contains multiple UPDATE statements to modify existing products. You have placed these updates into one explicit transaction. You need to set an option at the beginning of the transaction to roll back all changes if any of the updates in the transaction fail. Which option should you enable?
A.
ARITHABORT
B.
XACT_ABORT
C.
IMPLICIT_TRANSACTIONS
D.
REMOTE_PROC_TRANSACTIONS
Answer: B
QUESTION 35
You have a table named JobCandidate. You are tasked to delete a row in the JobCandidate table. You need to write a transaction that allows the database to be restored to the exact point the record was deleted without knowing the time of execution. Which query should you use?
A.
BEGIN TRANSACTION
DELETE FROM JobCandidate
WHERE JobCandidateID = 10;
COMMIT TRANSACTION;
B.
BEGIN TRANSACTION WITH MARK N'Deleting a Job Candidate';
DELETE FROM JobCandidate
WHERE JobCandidateID = 10;
COMMIT TRANSACTION
C.
BEGIN TRANSACTION Delete_Candidate WITH MARK
DELETE FROM JobCandidate
WHERE JobCandidateID = 10;
COMMIT TRANSACTION Delete_Candidate;
D.
DECLARE @CandidateName varchar(50) = 'Delete_Candidate'
BEGIN TRANSACTION @CandidateName
DELETE FROM JobCandidate
WHERE JobCandidateID = 10;
COMMIT TRANSACTION @CandidateName;
Answer: C
QUESTION 36
You have the following table named Sales.
You need to return sales data ordered by customer name and date of sale. For each customer, the most recent sale must be listed first.
Which query should you use?
A.
SELECT CustomerName, SalesDate
FROM Sales
ORDER BY CustomerName, SalesDate;
B.
SELECT CustomerName, SalesDate
FROM Sales
ORDER BY SalesDate DESC, CustomerName;
C.
SELECT CustomerName, SalesDate
FROM Sales
ORDER BY CustomerName, SalesDate DESC;
D.
SELECT CustomerName,
SalesDate FROM Sales ORDER BY CustomerName DESC;
Answer: C
QUESTION 37
You have a table named Sales.SalesOrderHeader and a table named Person.Person. You are tasked to write a query that returns SalesOrderID and SalesPersonName that have an OrderDate greater than 20040101. SalesPersonName should be made up by concatenating the columns named FirstName and LastName from the table named Person.Person. You need to write a query to return data, sorted in alphabetical order, by the concatenation of FirstName and LastName. Which Transact-SQL statement should you use?
A.
SELECT SalesOrderID, FirstName + ' ' + LastName as SalesPersonName FROM Sales.SalesOrderHeader H
JOIN Person.Person P on BusinessEntityID = H.SalesPersonID
WHERE OrderDate > '20040101' ORDER BY FirstName ASC, LastName ASC
B.
SELECT SalesOrderID, FirstName + ' ' + LastName as SalesPersonName FROM Sales.SalesOrderHeader H
JOIN Person.Person P on BusinessEntityID = H.SalesPersonID
WHERE OrderDate > '20040101' ORDER BY FirstName DESC, LastName DESC
C.
SELECT SalesOrderID, FirstName +' ' + LastName as SalesPersonName FROM Sales.SalesOrderHeader H
JOIN Person.Person P on BusinessEntityID = H.SalesPersonID
WHERE OrderDate > '20040101' ORDER BY SalesPersonName ASC
D.
SELECT SalesOrderID, FirstName + ' ' + LastName as SalesPersonName FROM Sales.SalesOrderHeader H
JOIN Person.Person P on BusinessEntityID = H.SalesPersonID
WHERE OrderDate > '20040101' ORDER BY SalesPersonName DESC
Answer: C
QUESTION 38
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?
A.
select * from Sales.PotentialClients
where EmailAddress like '_%@_%.com'
B.
select * from Sales.PotentialClients
where EmailAddress like '%@%.com'
C.
select * from Sales.PotentialClients
where EmailAddress like '_%@_.com'
D.
select * from Sales.PotentialClients
where EmailAddress like '%@%[.]com'
Answer: A
QUESTION 39
You have a table named Orders. OrderID is defined as an IDENTITY(1,1). OrderDate has a default value of 1.
You need to write a query to insert a new order into the Orders table for CustomerID 45 with today's date and a cost of 89.00.
Which statement should you use?
Exhibit:
A.
INSERT INTO Orders (CustomerId, OrderDate, Cost) VALUES (45, DEFAULT, 89.00);
B.
INSERT INTO Orders (OrderID, CustomerId, OrderDate, Cost) VALUES (1, 45, DEFAULT, 89.00);
C.
INSERT INTO Orders (CustomerId, OrderDate, Cost) VALUES (45, CURRENT_TIMESTAMP, 89.00);
D.
INSERT INTO Orders (OrderID, CustomerId, OrderDate, Cost) VALUES (1, 45, CURRENT_TIMESTAMP, 89.00);
Answer: C
QUESTION 40
You have the following two tables.
The foreign key relationship between these tables has CASCADE DELETE enabled.
You need to remove all records from the Orders table.
Which Transact-SQL statement should you use?
Exhibit:
A.
DROP TABLE Orders
B.
DELETE FROM Orders
C.
TRUNCATE TABLE Orders
D.
DELETE FROM OrderDetails
Answer: B
QUESTION 41
You have been tasked to delete 1000 rows from a table named NewWidgets. There are 2000 rows in which the column ToBeDeleted set to 1.
You need to write a Transact-SQL batch that will delete exactly 1000 rows.
Which Transact-SQL batch should you use?
A.
DELETE TOP (1000) dbo.NewWidgets
WHERE ToBeDeleted = 1;
B.
DECLARE @BatchSize INT = 10;
WHILE (@BatchSize = 10)
DELETE TOP (@BatchSize) dbo.NewWidgets
WHERE ToBeDeleted = 1;
C.
DELETE TOP ((SELECT COUNT(*) FROM dbo.NewWidgets
WHERE ToBeDeleted = 1)) w FROM dbo.NewWidgets w WHERE w.ToBeDeleted = 1;
D.
DECLARE @TotalRowCount INT = 0; WHILE (@TotalRowCount <= 1000)
BEGIN
DELETE TOP (10) dbo.NewWidgets
WHERE ToBeDeleted = 1;
SET @TotalRowCount += @@ROWCOUNT;
END
Answer: A
QUESTION 42
You have tables named Sales.SalesOrderDetails and Sales.SalesOrderHeader.
You have been tasked to update the discount amounts for the sales of a particular salesperson. You need to set UnitPriceDiscount to 0.1 for all entries in Sales.SalesOrderDetail that only correspond to SalesPersonID 290. Which Transact-SQL statement should you use?
A.
UPDATE d
SET UnitPriceDiscount = .1 FROM Sales.SalesOrderDetail d
INNER JOIN Sales.SalesOrderHeader h ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesPersonID = 290;
B.
UPDATE Sales.SalesOrderDetail
SET UnitPriceDiscount = .1 FROM Sales.SalesOrderHeader h WHERE h.SalesPersonID = 290;
C.
UPDATE Sales.SalesOrderDetail
SET UnitPriceDiscount = .1
WHERE EXISTS ( SELECT * FROM Sales.SalesOrderHeader h WHERE h.SalesPersonID = 290);
D.
UPDATE Sales.SalesOrderDetail
SET UnitPriceDiscount = .1 FROM Sales.SalesOrderDetail d
WHERE EXISTS (
SELECT * FROM Sales.SalesOrderHeader h
WHERE h.SalesPersonID = 290);
Answer: A
QUESTION 43
You have a table named Product.
You need to increase product prices for only the vendor named Coho Winery by 10 percent and then return a list of the products and updated prices.
Which code segment should you use?
A.
UPDATE Product SET Price = Price * 1.10, ProductName = ProductName
WHERE Product.VendorName = 'Coho Winery'
B.
UPDATE Product SET Price = Price * 1.10 OUTPUT inserted.ProductName, deleted.Price WHERE Product.VendorName = 'Coho Winery'
C.
UPDATE Product SET Price = Price * 1.10 OUTPUT inserted.ProductName, inserted.Price WHERE Product.VendorName = 'Coho Winery'
D.
UPDATE Product SET Price = Price * 1.10, VendorName = 'Coho Winery'
OUTPUT inserted.ProductName, inserted.Price
Answer: C
QUESTION 44
You have two tables named dbo.Products and dbo.PriceChange. Table dbo.Products contains ten
products. Five products are priced at $20 per unit and have PriceIncrease set to 1. The other five products
are priced at $10 per unit and have PriceIncrease set to 0.
You have the following query:
INSERT dbo.PriceChange (ProductID, Change, ChangeDate)
SELECT ProductID, inPrice -delPrice, SYSDATETIME()
FROM
(
UPDATE dbo.Products
SET Price *= 1.1
OUTPUT inserted.ProductID, inserted.Price, deleted.Price
WHERE PriceIncrease = 1 ) p (ProductID, inPrice, delPrice);
You need to predict the results of the query. Which results should the query produce?
A.
Five rows are updated in dbo.Products.
Five rows are inserted into dbo.PriceChange.
B.
Five rows are updated in dbo.Products.
No rows are inserted into dbo.PriceChange.
C.
No rows are updated in dbo.Products.
Five rows are inserted into dbo.PriceChange.
D.
No rows are updated in dbo.Products.
No rows are inserted into dbo.PriceChange.
Answer: A
QUESTION 45
You have two tables named MainTable and ArchiveTable.
You need to move data older than 30 days from MainTable into ArchiveTable.
Which code segment should you use?
A.
DELETE FROM MainTable
OUTPUT deleted.* WHERE RecordDate < DATEADD(D,-30,GETDATE())
B.
DELETE FROM MainTable
OUTPUT DELETED.* INTO ArchiveTable WHERE RecordDate < DATEADD(D,-30,GETDATE())
C.
INSERT INTO ArchiveTable SELECT * FROM MainTable WHERE RecordDate < DATEADD(D,-30,GETDATE())
D.
INSERT INTO ArchiveTable SELECT * FROM MainTable WHERE RecordDate < DATEADD(D,-30,GETDATE())
DELETE FROM MainTable
Answer: B
QUESTION 46
You have been tasked with creating a table named dbo.Widgets. You need to insert five rows into the dbo.Widgets table and return WidgetID for each of the five rows that have been inserted. Which Transact-SQL batch should you use?
A.
CREATE TABLE dbo.Widgets ( WidgetID INT IDENTITY PRIMARY KEY, WidgetName VARCHAR(25));
GO INSERT dbo.Widgets (WidgetName) OUTPUT inserted.WidgetID, inserted.WidgetName VALUES ('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');
B.
CREATE TABLE dbo.Widgets ( WidgetID INT IDENTITY PRIMARY KEY, WidgetName VARCHAR(25) );
GO
INSERT dbo.Widgets (WidgetName)
VALUES
('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');
SELECT SCOPE_IDENTITY();
C.
CREATE TABLE dbo.Widgets ( WidgetID UNIQUEIDENTIFIER PRIMARY KEY, WidgetName VARCHAR(25) );
GO
INSERT dbo.Widgets (WidgetName)
VALUES ('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');
SELECT SCOPE_IDENTITY();
D.
CREATE TABLE dbo.Widgets ( WidgetID UNIQUEIDENTIFIER PRIMARY KEY, WidgetName VARCHAR(25));
GO INSERT dbo.Widgets (WidgetName) OUTPUT inserted.WidgetID, inserted.WidgetName VALUES ('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');
Answer: A
QUESTION 47
You have the following two tables.
Products
ProductID ProductName VendorID
1 Product1 0
2 Product2 1
3 Product3 1
4 Product4 0
ProductChanges
ProductID ProductName VendorID
1 Product1 1
2 Product2 1
3 NewProduct3 2
5 Product5 1
You execute the following statement.
MERGE Products USING ProductChanges ON (Products.ProductID = ProductChanges.ProductID)
WHEN MATCHED AND Products.VendorID = 0 THEN DELETE WHEN MATCHED
THEN UPDATE SET Products.ProductName = ProductChanges.ProductName Products.VendorID = ProductChanges.VendorID;
You need to identify the rows that will be displayed in the Products table. Which rows will be displayed?
A.
ProductID ProductName VendorID
2 Product2 1
3 NewProduct3 2
B.
ProductID ProductName VendorID
2 Product2 1
3 NewProduct3 2
4 Product4 0
C.
ProductID ProductName VendorID
1 Product1 1
2 Product2 1
3 NewProduct3 2
5 Product5 1
D.
ProductID ProductName VendorID
1 Product1 1
2 Product2 1
3 NewProduct3 2
4 Product4 0
5 Product5 1
Answer: B
QUESTION 48
You have two tables.
A table named Student.CurrentStudents contains the names of all students enrolled for the current year.
Another table named Student.NewYearRoster contains the names of students who have enrolled for the upcoming year.
You have been tasked to write a MERGE statement to:
Insert into Student.CurrentStudents the names of students who are enrolled for the upcoming year but not for the current year.
Update information in Student.CurrentStudents for students who are enrolled both in the current year and in the upcoming year.
Delete from Student.CurrentStudents the names of students who are not enrolled for the upcoming year.
You need to write the appropriate MERGE statement. Which Transact-SQL statement should you use?
A.
MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T.FirstName
WHEN MATCHED THEN UPDATE SET Address = S.Address, Age = S.Age
WHEN NOT MATCHED BY TARGET THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
B.
MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T.FirstName
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age)
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET Address = T.Address, Age = T.Age;
C.
MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T.FirstName
WHEN MATCHED AND NOT T.Address = S.Address OR NOT T.Age = S.Age THEN UPDATE SET T.Address = S.Address, T.Age = S.Age
WHEN NOT MATCHED THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age)
WHEN MATCHED THEN DELETE;
D.
MERGE Student.CurrentStudents AS T USING Student.NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T.FirstName
WHEN MATCHED AND NOT T.Address = S.Address AND NOT T.Age = S.Age THEN UPDATE SET T.Age = S.Age, T.Address = S.Address
WHEN NOT MATCHED BY TARGET THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
Answer: A
QUESTION 49
You create and populate two tables by using the following Transact-SQL statements:
CREATE TABLE CurrentStudents (LastName VARCHAR(50), FirstName VARCHAR(50), Address VARCHAR(100), Age INT);
INSERT INTO CurrentStudents VALUES ('Fritz', 'David', '181 Kline Street', 14)
,('Reese', 'Paul' , '4429 South Union', 14)
,('Brown', 'Jake' , '5401 Washington Ave',14)
,('Smith', 'Tom' , '124 Water St', 14)
,('Holtz', 'Mary' , '984 Mass Ct', 14)
,('Robbins', 'Jan' , '4449 Union Ave', 14)
,('Larsen', 'Frank' , '5812 Meadow St', 14)
,('Bishop', 'Cathy' , '14429 Skyhigh Ave', 14)
,('Francis', 'Thomas' , '15401 120th St', 14)
CREATE TABLE NewYearRoster(LastName VARCHAR(50), FirstName VARCHAR(50), Address VARCHAR(100), Age INT);
INSERT INTO NewYearRoster VALUES ('Fritz', 'David', '181 Kline Street', 15)
,('Reese', 'Paul', '1950 Grandview Place', 15)
,('Adams', 'Wilbur', '4231 W. 93rd', 15)
,('Adams', 'Norris', '100 1st Ave', 15)
,('Thomas', 'Paul', '18176 Soundview Dr', 15)
,('Linderson', 'Danielle', '941 W. 37 Ave', 15)
,('Moore', 'Joshua', '2311 10st Ave', 15)
,('Dark', 'Shelby', '1987 Fifth Ave', 15)
,('Scharp', 'Mary', '1902 W. 303rd', 15)
,('Morris', 'Walt', '100 12st St', 15);
You run the following MERGE statement to update, insert and delete rows in the CurrentStudents table
MERGE TOP (3) CurrentStudents AS T
USING NewYearRoster AS S ON S.LastName = T.LastName AND S.FirstName = T.FirstName
WHEN MATCHED AND NOT (T.Age = S.Age OR T.Address = S.Address) THEN UPDATE SET Address = S.Address, Age = S.Age
WHEN NOT MATCHED BY TARGET THEN INSERT (LastName, FirstName, Address, Age) VALUES (S.LastName, S.FirstName, S.Address, S.Age)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
You need to identify the total number of rows that are updated, inserted, and deleted in the CurrentStudent table. Which total number of rows should you choose?
A.
0
B.
3
C.
6
D.
9
Answer: B
QUESTION 50
You are writing a query that returns a list of products that have grossed more than $10,000.00 during the year 2007.
You need to insert the following filter expression into the query.
SUM([Order Details].UnitPrice * [Order Details].Quantity) > 10000 Into which clause should you insert this expression?
A.
ON
B.
WHERE
C.
HAVING
D.
GROUP BY
Answer: C
QUESTION 51
You have a table named Sales. You are tasked to list products that have been sold to less than ten customers.
You need to write a query to achieve the task.
Which Transact-SQL statement should you use?
A.
SELECT ProductID, COUNT(*) AS CustomerCount FROM Sales GROUP BY ProductID, CustomerID HAVING COUNT(*) < 10;
B.
SELECT ProductID, COUNT(DISTINCT CustomerID) AS CustomerCount FROM Sales GROUP BY ProductID HAVING COUNT(DISTINCT CustomerID) < 10;
C.
SELECT ProductID, CustomerID, COUNT(DISTINCT CustomerID) AS CustomerCount FROM Sales
GROUP BY ProductID, CustomerID
HAVING COUNT(DISTINCT CustomerID) < 10;
D.
SELECT * FROM (SELECT ProductID, RANK() OVER (ORDER BY CustomerID DESC) AS Rnk
FROM Sales) s WHERE s.Rnk <= 10;
Answer: B
QUESTION 52
You have two tables named Customers and Orders.
for customers that have placed at least one order, you need to produce a list of customer names and the number of orders for each customer.
Which query should you use?
A.
SELECT c.CustomerName, SUM(o.OrderID) AS [OrderCount]
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName
B.
SELECT COUNT(o.OrderId) AS [OrderCount]
FROM CUSTOMERS c JOIN ORDERS o ON c.CUSTOMERID = o.CUSTOMERID
C.
SELECT c.CustomerName, COUNT(o.OrderID) AS [OrderCount]
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName HAVING COUNT(o.OrderID) > 1
D.
SELECT c.CustomerName, COUNT(o.OrderId) AS [OrderCount]
FROM Customers c JOIN Orders o ON c.CustomerId = o.CustomerId
GROUP BY c.CustomerName
Answer: D
QUESTION 53
You have a table named Products. The table contains a column named Color.
You need to write a Transact-SQL statement that calculates the percentage of products of each product color.
Which Transact-SQL statement should you use?
A.
SELECT Color COUNT(*) OVER(PARTITION BY Color)
/ (COUNT(*) * 1.0) AS PercentColor FROM Products GROUP BY Color;
B.
SELECT Color COUNT(*) OVER() / (COUNT(*) * 1.0) AS PercentColor
/ (COUNT(*) * 1.0) AS PercentColor FROM Products GROUP BY Color;
C.
SELECT Color, (COUNT(*) * 1.0)/ COUNT(*) OVER() AS PercentColor FROM Products GROUP BY Color;
D.
SELECT Color, COUNT(*) * 1.0) / COUNT(*) OVER(PARTITION BY Color) AS PercentColor FROM Products GROUP BY Color;
Answer: C
QUESTION 54
You have two tables named SalesPerson and SalesTerritory.
You need to create sample data by using a Cartesian product that contains the data from the SalesPerson and SalesTerritory tables.
Which code segment should you use?
A.
SELECT p.SalesPersonId, t.Name AS [Territory]
FROM Sales.SalesPerson p FULL JOIN Sales.SalesTerritory t ON p.TerritoryId = t.TerritoryId
B.
SELECT p.SalesPersonId, Name AS [Territory]
FROM Sales.SalesPerson p INNER JOIN Sales.SalesTerritory t ON p.TerritoryId = t.TerritoryId
C.
SELECT p.SalesPersonId, t.Name AS [Territory] FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t WHERE p.TerritoryId = t.TerritoryId
D.
SELECT p.SalesPersonId, t.Name AS [Territory] FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t;
Answer: D
QUESTION 55
You have a table named Employees.
You want to identify the supervisor to which each employee reports. You write the following query.
SELECT e.EmloyeeName AS [EmployeeName], s.EmployeeName AS [SuperVisorName] FROM Employees e
You need to ensure that the query returns a list of all employees and their respective supervisor. Which join clause should you use to complete the query?
A.
LEFT JOIN Employees s ON e.ReportsTo = s.EmployeeId
B.
RIGHT JOIN Employees s ON e.ReportsTo = s.EmployeeId
C.
INNER JOIN Employees s ON e.EmployeeId = s.EmployeeId
D.
LEFT JOIN Employees s ON e.EmployeeId = s.EmployeeId
Answer: A
QUESTION 56
You have a table named Subcategories that contains subcategories for socks, vests and helmets. You have another table named Products that contains products only from the subcategories socks and vests. You have the following query:
SELECT s.Name, p.Name AS ProductName
FROM Subcategories s OUTER APPLY (SELECT * FROM Products pr WHERE pr.SubcategoryID = s.SubcategoryID) p
WHERE s.Name IS NOT NULL;
You need to predict the results of the query. What results should the query produce?
A.
Name ProductName
Socks Mountain Bike
Socks, Socks Mountain Bike
Socks, Socks Racing Socks, M
Socks Racing Socks, L
Vests Classic Vest, S
Vests Classic Vest, M
Vests Classic Vest, L
B.
Name ProductName
Socks Mountain Bike
Socks, Socks Mountain Bike
Socks, Socks Racing Socks, M
Socks Racing Socks, L
Vests Classic Vest, S
Vests Classic Vest, M
Vests Classic Vest, L
Helmets NULL
C.
Name ProductName
Socks Mountain Bike
Socks, Socks Mountain Bike
Socks, Socks Racing Socks, M
Socks Racing Socks, L
Vests Classic Vest, S
Vests Classic Vest, M
Vests Classic Vest, L
Helmets NULL
NULL NULL
D.
Name ProductName
Socks Mountain Bike
Socks, Socks Mountain Bike
Socks, Socks Racing Socks, M
Socks Racing Socks, L
Vests Classic Vest, S
Vests Classic Vest, M
Vests Classic Vest, L
NULL Mountain Bike
Socks, NULL Mountain Bike
Socks, NULL Racing Socks, M
NULL Racing Socks, L
NULL Classic Vest, S
NULL Classic Vest, M
NULL Classic Vest, L
Helmets NULL
NULL NULL
Answer: B
QUESTION 57
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, Name FROM dbo.ArchiveProducts;
You need to predict the list of products that the query will produce. Which list of products should the query return?
A.
Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts but not in both.
B.
Products that have a matching ProductID and Name in dbo.CurrentProducts or dbo.ArchiveProducts.
C.
Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts. Products that appear in both tables are listed only once.
D.
Products that appear in dbo.CurrentProducts or dbo.ArchiveProducts. Products that appear in both tables are listed multiple times.
Answer: D
QUESTION 58
You have two tables named Products and NewProducts that have identical structures. You have the following query (Line numbers are included for reference only):
01 SELECT Product, Description
02 FROM dbo.Products
03 ........
04 SELECT Product, Description
05 FROM dbo.NewProducts
You need to choose the appropriate Transact-SQL operator to display rows that exist in both tables.
Which Transact-SQL operator should you insert in line 03?
A.
UNION
B.
EXCEPT
C.
UNION ALL
D.
INTERSECT
Answer: D
QUESTION 59
You are tasked to create a table that has a column that must store the current time accurate to ten microseconds.
You need to use a system function in conjunction with the DEFAULT option in the column definition.
Which system function should you use?
A.
DATEADD
B.
GETUTCDATE
C.
SYSDATETIME
D.
CURRENT_TIMESTAMP
Answer: C
QUESTION 60
You need to round the value 1.75 to the nearest whole number.
Which code segment should you use?
A.
Select ROUND(1.75,0)
B.
Select ROUND(1.75,2)
C.
Select ROUND(1.75,1.0)
D.
Select ROUND(1.75,2.0)
Answer: A
QUESTION 61
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?
A.
LEFT(TelephoneNumber, 3)
B.
SUBSTRING(TelephoneNumber, 3, 3)
C.
SUBSTRING (TelephoneNumber, 3, 1)
D.
CHARINDEX('[0-9][0-9][0-9]', TelephoneNumber, 3)
Answer: A
QUESTION 62
You are a database developer located in Seattle. You have a client in Melbourne, which is in a different time zone from Seattle. You have been using the datetimeoffset data type and storing data by using the Seattle offset.
You need to display the dates in the Melbourne offset.
Which function should you use?
A.
CONVERT
B.
DATEADD
C.
SWITCHOFFSET
D.
TODATETIMEOFFSET
Answer: C
QUESTION 63
You have a database that contains two tables named ProductCategory and ProductSubCategory.
You need to write a query that returns a list of product categories that contain more than ten sub-categories.
Which query should you use?
A.
SELECT [Name] FROM ProductSubCategory
WHERE ProductCategoryID IN (
SELECT ProductCategoryID FROM ProductCategory)
GROUP BY [Name] HAVING COUNT(*) > 10 )
B.
SELECT [Name] FROM ProductSubCategory
WHERE ProductCategoryID NOT IN (
SELECT ProductCategoryID FROM ProductCategory)
GROUP BY [Name] HAVING COUNT(*) > 10)
C.
SELECT [Name] FROM Product Category c
WHERE EXISTS (
SELECT ProductCategoryID FROM ProductSubCategory
WHERE ProductCategoryID = c.ProductCategoryID
GROUP BY ProductCategoryID
HAVING COUNT(*) > 10)
D.
SELECT [Name] FROM Product Category c
WHERE NOT EXISTS (
SELECT ProductCategoryID FROM ProductSubCategory
WHERE ProductCategoryID = c.ProductCategoryID
GROUP BY ProductCategoryID
HAVING COUNT(*) > 10)
Answer: C
QUESTION 64
You have a database that uses stored procedures to perform INSERT, UPDATE, DELETE, and SELECT statements.
You are tasked with providing a recommendation of indexes to be created and dropped from the database.
You need to select the appropriate method to accomplish the task.
Which method should you use?
A.
Index Usage DMVs
B.
Missing Index DMVs
C.
SQL Server Profiler
D.
Database Engine Tuning Advisor
Answer: D
QUESTION 65
Your company stores vendor and price information in a database. All items in the database have a list price.
You need to increase the list price for all products of only the vendor named Fabrikam by 20.00.
Which query should you use?
A.
UPDATE Production.Product
SET ListPrice = ListPrice + 20.00
WHERE NOT EXISTS (
SELECT VendorId FROM Purchasing.Vendor
WHERE VendorName = 'Fabrikam');
B.
UPDATE Production.Product SET ListPrice = ListPrice + 20.00
WHERE VendorId NOT IN (
SELECT VendorId FROM Purchasing.Vendor
WHERE VendorName = 'Fabrikam');
C.
UPDATE Production.Product SET ListPrice = ListPrice + 20.00
WHERE EXISTS (
SELECT VendorId FROM Purchasing.Vendor
WHERE VendorName = 'Fabrikam');
D.
UPDATE Production.Product SET ListPrice = ListPrice + 20.00 WHERE VendorId IN
(SELECT VendorId FROM Purchasing.Vendor
WHERE VendorName = 'Fabrikam');
Answer: D
QUESTION 66
You have two tables named Customer and SalesOrder.
You need to identify all customers that have not yet made any purchases and those that have only made orders with an OrderTotal less than 100.
Which query should you use?
A.
SELECT * FROM Customer
WHERE 100 > ALL (
SELECT OrderTotal FROM SalesOrder
WHERE Customer.CustomerID = SalesOrder.CustomerID)
B.
SELECT * FROM Customer
WHERE 100 > SOME (
SELECT OrderTotal FROM SalesOrder
WHERE Customer.CustomerID = SalesOrder.CustomerID)
C.
SELECT * FROM Customer
WHERE 100 > (
SELECT MAX(OrderTotal) FROM SalesOrder
WHERE Customer.CustomerID = SalesOrder.CustomerID)
D.
SELECT * FROM Customer
WHERE EXISTS (
SELECT SalesOrder.CustomerID FROM SalesOrder
WHERE Customer.CustomerID = SalesOrder.CustomerID AND SalesOrder.OrderTotal <= 100)
Answer: A
QUESTION 67
You have two tables named Customer and SalesOrder.
In the Customer table you have 1000 customers, of which 900 customers have orders in the SalesOrder table.
You execute the following query to list all customers that have had at least one sale.
SELECT * FROM Customer WHERE Customer.CustomerID IN (SELECT Customer.CustomerID FROM SalesOrder)
You need to identify the results of the query. Which results will the query return?
A.
No rows
B.
A warning message
C.
The 1000 rows in the Customer table
D.
The 900 rows in the Customer table with matching rows in the SalesOrder table
Answer: C
QUESTION 68
You have the following rows in the
Customer Table:
CustomerId Status
1 Active
2 Active
3 Inactive
4 NULL
5 Dormant
6 Dormant
You write the following query to return all customers that do not have NULL or 'Dormant' for their status:
SELECT * FROM Customer
WHERE Status NOT IN (NULL, 'Dormant')
You need to identify the results of the query.
Which result should you expect?
A.
CustomerId Status
B.
CustomerId Status
1 Active
2 Active
3 Inactive
C.
CustomerId Status
1 Active
2 Active
3 Inactive
4 NULL
D.
CustomerId Status
1 Active
2 Active
3 Inactive
4 NULL
5 Dormant
6 Dormant
Answer: B
QUESTION 69
You have a table named Employee.
You document your company's organizational hierarchy by inserting the EmployeeID of each employee's manager in the ReportsTo column.
You need to write a recursive query that produces a list of employees and their manager.
The query must also include the employee's level in the hierarchy.
You write the following code segment. (Line numbers are included for reference only.)
01 WITH EmployeeList (EmployeeID, FullName, ManagerName, Level)
02 AS (
03 .........
04 )
05 SELECT EmployeeID, FullName, ManagerName, Level
06 FROM EmployeeList;
Which code segment should you insert at line 3?
A.
SELECT EmployeeID, FullName, '' AS [ReportsTo], 1 AS [Level]
FROM Employee WHERE ReportsTo IS NULL
UNION ALL
SELECT emp.EmployeeID, emp.FullNName, mgr.FullName, 1 + 1 AS [Level]
FROM Employee emp
JOIN Employee mgr ON emp.ReportsTo = mgr.EmployeeID
B.
SELECT EmployeeID, FullName, '' AS [ReportsTo], 1 AS [Level]
FROM Employee WHERE ReportsTo IS NULL
UNION ALL
SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1
FROM EmployeeList mgr
JOIN Employee emp ON emp.ReportsTo = mgr.EmployeeId
C.
SELECT EmployeeID, FullName, '' AS [Reports To], 1 AS [Level]
FROM Employee
UNION ALL
SELECT emp.EmployeeID, emp.FullName, mgr.FullName, 1 + 1 AS [Level]
FROM Employee emp
LEFT JOIN Employee mgr ON emp.ReportsTo = mgr.EmployeeID
D.
SELECT EmployeeID, FullName, '' AS [ReportsTo], 1 AS [Level]
FROM Employee
UNION ALL
SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1
FROM EmployeeList mgr
JOIN Employee emp ON emp.ReportsTo = mgr.EmployeeID
Answer: B
QUESTION 70
You need to determine the result of executing this code segment.
DECLARE @RangeStart INT = 0;
DECLARE @RangeEnd INT = 10000;
DECLARE @RangeStep INT = 1;
WITH NumberRange(ItemValue)
AS (
SELECT ItemValue
FROM (SELECT @RangeStart AS ItemValue) AS t
UNION ALL
SELECT ItemValue + @RangeStep
FROM NumberRange
WHERE ItemValue < @RangeEnd)
SELECT ItemValue
FROM NumberRange
OPTION (MAXRECURSION 100)
Which result will be returned?
A.
101 rows will be returned with no error.
B.
10,001 rows will be returned with no error.
C.
101 rows will be returned with a maximum recursion error.
D.
10,001 rows will be returned with a maximum recursion error.
Answer: C
QUESTION 71
You need to implement a common table expression (CTE). Which code segment should you use?
A.
CREATE VIEW SalesByYear AS
SELECT Year, Region, SUM(OrderTotal)
FROM Orders
GROUP BY Year, Region;
GO
SELECT Year, Region, Total
FROM SalesByYear;
B.
WITH SalesByYear(Year,Region,Total)
AS (SELECT Year, Region, SUM(OrderTotal)
FROM Orders GROUP BY Year,Region)
SELECT Year, Region, Total FROM SalesByYear;
C.
SELECT Year, Region, Total
FROM (
SELECT Year, Region, SUM(OrderTotal) AS Total
FROM Orders
GROUP BY Year, Region) AS [SalesByYear];
D.
SELECT DISTINCT Year, Region, (
SELECT SUM(OrderTotal) FROM Orders SalesByYear
WHERE Orders.Year = SalesByYear.YEAR AND Orders.Region = SalesByYear.Region) AS [Total]
FROM Orders;
Answer: B
QUESTION 72
You are tasked to analyze blocking behavior of the following query:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
WITH Customers AS (
SELECT * FROM Customer ),
SalesTotal AS ( SELECT CustomerId, SUM(OrderTotal) AS AllOrderTotal FROM SalesOrder)
SELECT CustomerId, AllOrderTotal
FROM SalesTotal
WHERE AllOrderTotal > 10000.00;
You need to determine if other queries that are using the Customer table will be blocked by this query. You also need to determine if this query will be blocked by other queries that are using the Customer table.
What behavior should you expect?
A.
The other queries will be blocked by this query. This query will be blocked by the other queries.
B.
The other queries will be blocked by this query. This query will not be blocked by the other queries.
C.
The other queries will not be blocked by this query. This query will be blocked by the other queries.
D.
The other queries will not be blocked by this query. This query will not be blocked by the other queries.
Answer: D
QUESTION 73
You create and populate a table named SiteNavigation by using the following statements:
CREATE TABLE SiteNavigation (
SiteNavigationId INT PRIMARY KEY,
Linktext VARCHAR(10),
LinkUrl VARCHAR(40),
ParentSiteNavigationId INT NULL REFERENCES SiteNavigation(SiteNavigationId)
)
INSERT INTO SiteNavigation VALUES (1,'First','http://first',NULL)
,(2,'Second','http://second',1)
,(3,'Third','http://third',1)
,(4,'Fourth','http://fourth',2)
,(5,'Fifth','http://fifth',2)
,(6,'Sixth','http://sixth',2)
,(7,'Seventh','http://seventh',6)
,(8,'Eighth','http://eighth',7)
You are tasked to write a query to list all site references that are more than two levels from the root node.
The query should produce the following results:
LinkText LinkUrl DistanceFromRoot
Fourth http://fourth 2
Fifth http://fifth 2
Sixth http://sixth 2
Seventh http://seventh 3
Eighth http://eighth 4
You have written the following query:
WITH DisplayHierarchy AS (SELECT LinkText, LinkUrl, SiteNavigationId, ParentSiteNavigationId, 0 AS DistanceFromRoot
FROM SiteNavigation
WHERE ParentSiteNavigationId IS NULL
UNION ALL
SELECT SiteNavigation.LinkText, SiteNavigation.LinkUrl, SiteNavigation.SiteNavigationId, SiteNavigation.ParentSiteNavigationId,
dh.DistanceFromRoot + 1 AS DistanceFromRoot
FROM SiteNavigation
INNER JOIN DisplayHierarchy dh
ON SiteNavigation.ParentSiteNavigationId = dh.SiteNavigationId)
SELECT LinkText, LinkUrl, DistanceFromRoot FROM DisplayHierarchy
You need to append a WHERE clause to the query. Which clause should you use?
A.
WHERE DistanceFromRoot =2
B.
WHERE DistanceFromRoot < 2
C.
WHERE DistanceFromRoot >= 2
D.
WHERE DistanceFromRoot IN (2,3)
Answer: C
QUESTION 74
You have two views named Sales.SalesSummaryOverall and Sales.CustomerAndSalesSummary.
They are defined as follows:
CREATE VIEW Sales.SalesSummaryOverall
AS
SELECT CustomerId, SUM(SalesTotal) AS OverallTotal
FROM Sales.SalesOrder
GROUP BY CustomerId
GO
CREATE VIEW Sales.CustomerAndSalesSummary
AS
SELECT Customer.Name, SalesSummaryOverall.OverallTotal, (SELECT AVG(OverallTotal)
FROM Sales.SalesSummaryOverall
WHERE SalesSummaryOverall.CustomerId = Customer.CustomerId) AS avgOverallTotal,
(SELECT MAX(OverallTotal) FROM Sales.SalesSummaryOverall
WHERE SalesSummaryOverall.CustomerId =Customer.CustomerId) AS maxOverallTotal,
FROM Sales.Customer
LEFT OUTER JOIN Sales. Sales.SalesSummaryOverall
ON SalesSummaryByYear.CustomerId = Customer.CustomerId
GO
You have been tasked to modify the Sales.CustomerAndSalesSummary view to remove references to other views.
You need to identify a feature to use in the modified version of the Sales.CustomerAndSalesSummary object to achieve the task.
Which feature should you use?
A.
Table variables
B.
Temporary tables
C.
User-defined table types
D.
Common table expressions
Answer: D
QUESTION 75
You need to write a query that allows you to rank total sales for each salesperson into four groups, where the top 25 percent of results are in group 1, the next 25 percent are in group 2, the next 25 percent are in group 3, and the lowest 25 percent are in group 4.
Which Transact-SQL statement should you use?
A.
NTILE(1)
B.
NTILE(4)
C.
NTILE(25)
D.
NTILE(100)
Answer: B
QUESTION 76
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?
A.
RANK
B.
NTILE(10)
C.
DENSE_RANK
D.
ROW_NUMBER
Answer: D
QUESTION 77
You have a table named ProductCounts that contains 1000 products as well as the number of units that have been sold for each product. You need to write a query that displays the top 5% of products that have been sold most frequently.
Which Transact-SQL code segments should you use?
A.
WITH Percentages AS (
SELECT *, NTILE(5) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts)
SELECT * FROM percentages
WHERE groupingColumn =1;
B.
WITH Percentages AS (
SELECT *, NTILE(5) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts)
SELECT * FROM Percentages
WHERE groupingColumn = 5;
C.
WITH Percentages AS (
SELECT *, NTILE(20) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts)
SELECT * FROM Percentages
WHERE groupingColumn = 1;
D.
WITH Percentages AS (
SELECT *, NTILE(20) OVER (ORDER BY UnitsSold) AS groupingColumn FROM ProductCounts)
SELECT * FROM Percentages
WHERE groupingColumn = 20;
Answer: D
QUESTION 78
You work for an international charity organization. You are writing a query to list the highest 100 different amounts that were donated. You have written the following code segment (Line numbers are included for reference only):
01 SELECT *
02 FROM (SELECT Customer.CustomerID, SUM(TotalDue) AS TotalGiven,
03 ....................
04 FROM Customer
05 JOIN SalesOrder
06 ON Customer.CustomerID = SalesOrder.CustomerID
07 GROUP BY Customer.CustomerID) AS DonationsToFilter
08 WHERE FilterCriteria <= 100
You need to insert a Transact-SQL clause in line 03 to complete the query. Which Transact-SQL clause should you insert?
A.
RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
B.
NTILE(100) OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
C.
ROW_NUMBER() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
D.
DENSE_RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
Answer: D
QUESTION 79
You have a database server that has four quad-core processors. This database server executes complex queries that are used to generate reports.
You need to force a query to use only one processor core without affecting other queries.
Which option should you use?
A.
OPTION (FAST 1)
B.
OPTION (MAXDOP 1)
C.
OPTION (RECOMPILE)
D.
OPTION (MAXRECURSION 1)
Answer: B
QUESTION 80
You notice that for a particular set of parameter values the following query sometimes executes
quickly and other times executes slowly. You also notice that 90 percent of the rows in the Address table contain the same value for the city.
SELECT AddressId, AddressLine1, City, PostalCode
FROM Person.Address
WHERE City = @city_name
AND PostalCode = @postal_code
You need to use a query hint that, for the particular set of parameter values, will result in a more consistent query execution time. Which query hint should you use?
A.
FAST
B.
MAXDOP
C.
OPTIMIZE FOR
D.
PARAMETERIZATION FORCED
Answer: C
QUESTION 81
You have been tasked to write a query to select one million rows.
You need to optimize the query to return the first 50 rows as quickly as possible.
What query hint should you use?
A.
FAST 50
B.
MAXDOP 50
C.
OPTIMIZE FOR @ROWS=50
D.
TABLE HINT(table, INDEX(50))
Answer: A
QUESTION 82
You have the following query:
SELECT EmployeeID, ManagerID, LoginID FROM dbo.Employees WHERE ManagerID = 1500 ORDER BY ManagerID;
You have been tasked to force the query to use the execution plan in the exhibit.
You need to use an appropriate hint to perform the task.
Which hint should you use?
Exhibit:
A.
INDEX(0)
B.
INDEX(1)
C.
INDEX(PK_Employees)
D.
INDEX(IX_Employees)
Answer: D
QUESTION 83
You are working with a SQL Server 2008 instance that is configured to use the Latin1_General_CS_AS collation. You create a database by using the following statements.
CREATE DATABASE TestDB COLLATE Estonian_CS_AS;
GO
USE TestDB;
GO
CREATE TABLE TestPermTab (PrimaryKey int PRIMARY KEY, Col1 nchar );
You implement a temporary table named #TestTempTab that uses the following code.
use TestDB;
GO
CREATE TABLE #TestTempTab (PrimaryKey int PRIMARY KEY, Col1 nchar );
INSERT INTO #TestTempTab
SELECT * FROM TestPermTab;
You need to identify which collation will be assigned to #TestTempTab. Which collation will be assigned?
A.
No-collation
B.
Estonian_CS_AS
C.
Latin1_General_CS_AS
D.
The collation selected by the Windows system locale of the server
Answer: C
QUESTION 84
You have a table named Person that contains a nvarchar column named Surname. The Person table currently has a clustered index on PersonID. The Surname column contains Russian and Japanese characters.
The following code segment will be used to search by Surname.
IF @lang ='Russian'
SELECT PersonID, Surname
FROM Person WHERE Surname = @SearchName COLLATE Cyrillic_General_CI_AS
if @lang = 'Japanese'
SELECT PersonID, Surname FROM Person WHERE Surname = @SearchName COLLATE Japanese_CI_AS_KS
You need to enable SQL Server to perform an index seek for these queries. What should you do?
A.
Create an index on the Surname column.
B.
Create a computed column for each collation that needs to be searched. Create an index on the Surname column.
C.
Create a computed column for each collation that needs to be searched. Create an index on each computed column.
D.
Create a new column for each collation that needs to be searched and copy the data from the Surname column. Create an index on each new column.
Answer: C
QUESTION 85
You have an application that is used by international clients. All clients connect by using Windows Authentication.
You need to ensure that system and user-defined error messages are displayed in the localized language for the clients. What should you do? (Each correct answer represents part of the solution. Choose two.)
A.
Use @@LANGUAGE function
B.
Use default language for each login
C.
Use @lang parameter of sp_addmessage
D.
Use the "set language" option of sp_configure
Answer: BC
QUESTION 86
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 MyDB;
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?
A.
CREATE TABLE #Person (LastName nvarchar(128) SPARSE);
B.
CREATE TABLE #Person (LastName nvarchar(128) COLLATE database_default);
C.
CREATE TABLE #Person (LastName nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS);
D.
CREATE TABLE tmpPerson (LastName nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS);
Answer: B
QUESTION 87
You have a SQL Server 2008 database. You have not installed a MAPI client. You need to send e-mail from a stored procedure. Which system stored procedure should you use?
A.
xp_sendmail
B.
xp_startmail
C.
sp_send_dbmail
D.
sysmail_start_sp
Answer: C
QUESTION 88
You are using Database Mail to deliver email notification and are notified that an employee has not been receiving emails.
You need to determine if any email notifications sent by Database Mail have been unsuccessful.
Which object from the msdb database should you use?
A.
msdb.dbo.sysmail_event_log
B.
msdb.dbo.sysmail_sentitems
C.
msdb.dbo.sysmail_unsentitems
D.
msdb.dbo.sysmail_faileditems
Answer: D
QUESTION 89
You have been tasked to delete a number of Database Mail messages that have been sent.
You need to delete all the emails that were sent more than one month ago.
Which Transact-SQL statements should you run?
A.
DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
EXEC msdb.dbo.sysmail_delete_log_sp @OneMonthAgo
B.
DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
EXEC msdb.dbo.sysmail_delete_mailitems_sp @OneMonthAgo
C.
DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
EXEC msdb.dbo.sysmail_delete_log_sp @OneMonthAgo,'Success'
D.
DECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
EXEC msdb.dbo.sysmail_delete_mailitems_sp @OneMonthAgo,'Sent'
Answer: D
QUESTION 90
You have a table named Books that has columns named BookTitle and Description. There is a full-text index on these columns. You need to return rows from the table in which the word 'computer' exists in either column. Which code segment should you use?
A.
SELECT * FROM Books
WHERE FREETEXT(*,'computer')
B.
SELECT * FROM Books
WHERE BookTitle LIKE '%computer%'
C.
SELECT * FROM Books
WHERE BookTitle = '%computer%'
OR Description = '%computer%'
D.
SELECT * FROM Books
WHERE FREETEXT(BookTitle,'computer')
Answer: A
QUESTION 91
You need to configure Full-Text Search to ignore specific words. Which Full-Text Search component should you use?
A.
iFilter
B.
Stoplist
C.
Thesaurus file
D.
Word breakers
Answer: B
QUESTION 92
Your company manufactures and distributes bicycle parts. You have a full-text catalog on the Inventory table which contains the PartName and Description columns. You also use a full-text thesaurus to expand common bicycle terms. You need to write a full-text query that will not only match the exact word in the search, but also the meaning.
Which Transact-SQL statement should you use?
A.
SELECT * FROM Inventory
WHERE FREETEXT (*, 'cycle'))
B.
SELECT * FROM Inventory
WHERE CONTAINS (*, 'cycle')
C.
SELECT * FROM Inventory
WHERE Description LIKE '%cycle%'
D.
SELECT * FROM Inventory
WHERE CONTAINS (*, 'FormsOf(Inflectional, cycle)')
Answer: A
QUESTION 93
Your company manufactures and distributes bowling balls. You have a full-text catalog named ftCatalog which contains the ftInventory index on the Products table. Your marketing department has just inserted a new bowling ball into the Inventory table. You notice only the new bowling ball is not being included in the results of the full-text searches. You have confirmed that the row exists in the Products table. You need to update the full-text catalog in the least amount of time. Which Transact-SQL statement should you use?
A.
ALTER FULLTEXT INDEX ON ftInventory START FULL POPULATION
B.
ALTER FULLTEXT INDEX ON ftInventory RESUME POPULATION
C.
ALTER FULLTEXT INDEX ON ftInventory START UPDATE POPULATION
D.
ALTER FULLTEXT CATALOG ftCatalog REBUILD
Answer: C
QUESTION 94
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?
A.
$Server.Databases | Where-Object{($_.Size * $MultipleOfGB) -gt 100GB\} | Select-Object Name, Size
B.
$Server | Where-Object{($_.DatabaseSize * $MultipleOfGB) -match 100GB\} | Select-Object Name, DatabaseSize
C.
$Server | Where-Object{($_.DatabaseSize * $MultipleOfGB) -gt 100GB\} | Select-Object Name, DatabaseSize
D.
$Server.Databases | Where-Object{($_.Size * $MultipleOfGB) -match 100GB\} | Select-Object Name, Size
Answer: A
QUESTION 95
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?
A.
Get-Item
B.
Get-Location
C.
Get-ChildItem
D.
Get-ItemProperty
Answer: C
QUESTION 96
You are configuring Service Broker to process messages within a single database. You have performed the following steps.
CREATE MESSAGE TYPE
CREATE CONTRACT
CREATE QUEUE
You need to complete the Service Broker configuration. What should be the next step?
A.
CREATE ROUTE
B.
CREATE SERVICE
C.
CREATE ENDPOINT
D.
CREATE BROKER PRIORITY
Answer: B
QUESTION 97
You have a database named Contoso. The Contoso database has a Service Broker queue named VacationRequestQueue.
The Contoso database has been restored to a new server. Since restoring the database, Service Broker is no longer able to send new messages.
You need to configure Service Broker in order to resolve the issue.
Which Transact-SQL statement should you use?
A.
ALTER DATABASE Contoso SET NEW_BROKER;
B.
ALTER DATABASE Contoso SET ENABLE_BROKER;
C.
ALTER QUEUE VacationRequestQueue WITH STATUS = ON;
D.
ALTER QUEUE VacationRequestQueue WITH ACTIVATION (STATUS = ON);
Answer: A
QUESTION 98
You created a Service Broker queue by using the following Transact-SQL statement:
CREATE QUEUE VacationRequestQueue WITH RETENTION = OFF, ACTIVATION (
PROCEDURE_NAME = dbo.VacationRequestProcess,
MAX_QUEUE_READERS = 5, EXECUTE AS SELF
);
You need to modify the Service Broker queue to prevent it from processing received messages.
The queue should continue to receive messages.
Which Transact-SQL statement should you use?
A.
ALTER QUEUE VacationRequestQueue WITH RETENTION = ON;
B.
ALTER QUEUE VacationRequestQueue WITH STATUS = OFF;
C.
ALTER QUEUE VacationRequestQueue WITH ACTIVATION (STATUS = OFF);
D.
ALTER QUEUE VacationRequestQueue WITH ACTIVATION (EXECUTE AS OWNER);
Answer: C
QUESTION 99
You use the same Service Broker configuration to support a Web site and an internal application. The Web site generates a greater workload than the internal application.
You need to configure Service Broker to ensure that messages sent by the internal application are processed before those sent by the Web site.
Which Transact-SQL statement should you use?
A.
ALTER SERVICE
B.
CREATE CONTRACT
C.
CREATE BROKER PRIORITY
D.
ALTER QUEUE WITH ACTIVATION
Answer: C
QUESTION 100
You are using Microsoft SQL Server 2008 Enterprise Edition. You need to maintain a history of all data modifications made to a table, including the type of modification and the values modified. Which tracking method should you use?
A.
Database Audit
B.
Change Tracking
C.
C2 Audit Tracing
D.
Change Data Capture
Answer: D
QUESTION 101
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
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?
A.
FROM Sales RIGHT JOIN CHANGETABLE (CHANGES Sales, 10) AS C ...
B.
FROM Sales INNER JOIN CHANGETABLE (CHANGES Sales, 10) AS C ...
C.
FROM Sales INNER JOIN CHANGETABLE (CHANGES SalesArchive, 10) AS C ...
D.
FROM Sales RIGHT JOIN CHANGETABLE (CHANGES SalesArchive, 10) AS C ...
Answer: A
QUESTION 102
You are required to modify a table named Sales.SalesOrder. The table has change tracking enabled on it.
You need to disable change tracking prior to modifying the Sales.SalesOrder table.
Which Transact-SQL statement should you use?
A.
EXEC sys.sp_cdc_disable_db
B.
ALTER DATABASE Contoso
SET CHANGE_TRACKING = OFF
C.
ALTER TABLE Sales.SalesOrder
DISABLE CHANGE_TRACKING
D.
EXEC sys.sp_cdc_disable_table
@source_schema = N'Sales',
@source_name = N'SalesOrder',
@capture_instance = N'Sales_SalesOrder'
Answer: C
QUESTION 103
You have implemented change tracking on a table named Sales.SalesOrder.
You need to determine all columns that have changed since the minimum valid version.
Which function should you use?
A.
CHANGE_TRACKING_CURRENT_VERSION
B.
CHANGE_TRACKING_IS_COLUMN_IN_MASK
C.
CHANGETABLE with the CHANGES argument
D.
CHANGETABLE with the VERSION argument
Answer: C
QUESTION 104
You have two tables named Customers and Orders. They are related by a foreign key constraint on the CustomerID on each table. You need to generate the following XML structure that includes all customers and their related orders.
Customer1
1/1/2008
422
4/8/2008
300
...
...
Which query should you use?
A.
SELECT CustomerName, OrderDate, OrderValue
FROM Customers c JOIN Orders o ON o.CustomerID = c.CustomerID
FOR XML AUTO, TYPE
B.
SELECT * FROM (
SELECT CustomerName, NULL AS OrderDate, NULL AS OrderValue
FROM Customers
UNION ALL
SELECT NULL, OrderDate, OrderValue FROM Orders) CustomerOrders
FOR XML AUTO, ROOT('Root')
C.
SELECT CustomerName, (
SELECT OrderDate, OrderValue
FROM Orders
FOR XML PATH('Order'))
FROM Customers FOR XML PATH('Customer'), ROOT('Root'), TYPE
D.
SELECT CustomerName, (
SELECT OrderDate, OrderValue
FROM Orders
WHERE Orders.CustomerId = Customers.CustomerId FOR XML PATH('Order'), TYPE) Orders
FROM Customers FOR XML PATH('Customer'), ROOT('Root')
Answer: D
QUESTION 105
You need to generate the following XML document.
Product1
Product2
Product3
Product4
Which query should you use?
A.
SELECT Price, ProductName
FROM Products AS ProductExport
FOR XML PATH('Product')
B.
SELECT Price, ProductName
FROM Products
FOR XML AUTO, ROOT('ProductExport')
C.
SELECT Price [@Price],
ProductName AS [*] FROM Products AS ProductExport FOR XML AUTO, ELEMENTS
D.
SELECT Price [@Price],
ProductName AS [*] FROM Products FOR XML PATH('Product'),ROOT('ProductExport')
Answer: D
QUESTION 106
Your company's database contains Customers and Orders tables.
You have been tasked to write a SELECT statement that outputs customer and order data as a valid and well-formed XML document. You are required to mix attribute and element based XML within the document. You have determined that using the FOR XML AUTO clause will not be suitable.
You need to identify the correct FOR XML clause to meet the requirement.
Which FOR XML statement should you use? (Each correct answer represents a complete solution. Choose two.)
A.
FOR BROWSE
B.
FOR XML RAW
C.
FOR XML PATH
D.
FOR XML EXPLICIT
Answer: CD
QUESTION 107
Your company's database contains Customers and Orders tables.
You have been tasked to write a SELECT statement that exposes the data as a valid and well-formed
XML document. The XML data must be attribute-based, and the order data XML must be nested in the customer data XML.
You need to write a SELECT statement to meet the requirements.
Which Transact-SQL statement should you use?
A.
SELECT c.ContactName, o.OrderDate, o.RequiredDate FROM Customers c INNER JOIN Orders o
ON c.CustomerID = o.CustomerID FOR XML RAW('Contact'), ROOT('ContactOrderDate')
B.
SELECT c.ContactName, o.OrderDate, o.RequiredDate FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID FOR XML PATH('ContactOrderDate')
C.
SELECT c.ContactName, o.OrderDate, o.RequiredDate FROM Customers c INNER JOIN Orders o
ON c.CustomerID = o.CustomerID FOR XML AUTO
D.
SELECT c.ContactName, o.OrderDate, o.RequiredDate FROM Customers c INNER JOIN Orders o
ON c.CustomerID = o.CustomerID FOR XML AUTO, ROOT('ContactOrderDate')
Answer: D
QUESTION 108
You have a table named Customer that has an XML column named Locations. This column stores an
XML fragment that contains details of one or more locations, as show in the following examples.
You need to write a query that returns a row for each of the customer's locations. Each resulting row must
include the customer name, city, and an XML fragment that contains the location details.
Which query should you use?
A.
SELECT CustomerName, Locations.query('for $i in /Location return data($i/@City)'), Locations.query('for $i in
/Location return $i')
FROM Customer
B.
SELECT CustomerName, Locations.query('for $i in /Location return element Location {$i/@City, $i}')
FROM Customer
C.
SELECT CustomerName, Locations.query('data(/Location/@City)'), Locations.query('/Location') FROM Customer
D.
SELECT CustomerName, Loc.value('@City','varchar(100)'), Loc.query('.')
FROM Customer CROSS APPLY Customer.Locations.nodes ('/Location') Locs(Loc)
Answer: D
QUESTION 109
Click the Exhibit button.
You have the following XML:
You are tasked to query the sites listed in the XML by using OPENXML.
The results will have two columns, ParentSiteURL and SiteURL.
The ParentSiteURL column should contain the URL attribute of the parent site.
The SiteURL column should contain the URL attribute of the site itself.
The output should look like that in the exhibit.
You need to write the OPENXML query.
Which Transact-SQL statement should you use?
Exhibit:
A.
SELECT ParentSiteURL, SiteURL
FROM OPENXML (@XMLDocHandle, '//@Site', 1)
WITH ( ParentSiteURL nVarChar(512) '../URL',
SiteURL nVarChar(512) 'URL')
B.
SELECT ParentSiteURL, SiteURL
FROM OPENXML (@XMLDocHandle, '//URL', 1)
WITH ( ParentSiteURL nVarChar(512) '../@URL',
SiteURL nVarChar(512) '@URL')
C.
SELECT ParentSiteURL, SiteURL
FROM OPENXML (@XMLDocHandle, '//Site', 1)
WITH ( ParentSiteURL nVarChar(512) '../@URL',
SiteURL nVarChar(512) '@URL')
D.
SELECT ParentSiteURL, SiteURL
FROM OPENXML (@XMLDocHandle, '//@URL', 1)
WITH ( ParentSiteURL nVarChar(512) '../URL', SiteURL nVarChar(512) 'URL')
Answer: C
QUESTION 110
Your company uses an application that passes XML to the database server by using stored procedures.
The database server has a large number of XML handles that are currently active. You determine that the XML is not being flushed from SQL Server memory.
You need to identify the system stored procedure to flush the XML from memory.
Which Transact-SQL statement should you use?
A.
sp_xml_removedocument
B.
sp_xml_preparedocument
C.
sp_reserve_http_namespace
D.
sp_delete_http_namespace_reservation
Answer: A
QUESTION 111
You work for a company that provides marketing data to other companies.
You have the following Transact-SQL statement:
DECLARE @CustomerDemographics XML SET @CustomerDemographics=N'
0
1
1
1
1
'
DECLARE @OutputAgeOfCoffeeDrinkers XML
SET @OutputAgeOfCoffeeDrinkers = @CustomerDemographics.query('
for $output in /child::CustomerDemographics/child::Customer[( child::IsCoffeeDrinker[1] cast as xs:boolean )]
return { $output/attribute::Age \} ')
SELECT @OutputAgeOfCoffeeDrinkers
You need to determine the result of the query. What result should you expect?
A.
B.
C.
D.
Answer: A
QUESTION 112
You have a table named Stores that has an XML column named OpenHours.
This column contains the opening and closing times.
...
You need to write a query that returns a list of stores and their opening time for a specified day.
Which code segment should you use?
A.
DECLARE @Day VARCHAR(10) = 'Tuesday'
SELECT StoreName, OpenHours.value('/hours[1]/@open','time')
FROM Stores WHERE OpenHours.value('/hours[1]/@dayofWeek','varchar(20)') = @Day
B.
DECLARE @Day VARCHAR(10) = 'Tuesday'
SELECT StoreName, OpenHours.value('/hours[1]/@open','time')
FROM Stores WHERE OpenHours.exist('/hours[@dayofWeek=sql:variable("@Day")]') = 1
C.
DECLARE @Day VARCHAR(10) = 'Tuesday'
SELECT Storename, OpenHours.query('data(/hours[@dayofWeek=sql:variable("@Day")]/@open)')
FROM Stores D. DECLARE @Day VARCHAR(10) = 'Tuesday'
D.
SELECT StoreName, OpenHours.value('/hours[1][@dayofWeek=sql:variable("@Day")]/@open','time')
FROM Stores
Answer: C
QUESTION 113
You have the following XML document that contains Product information.
DECLARE @prodList xml ='
...
';
You need to return a list of products that contains the Product Name, Category, and Price of each product.
Which query should you use?
A.
SELECT prod.value('.[1]/@Name','varchar(100)'), prod.value('.[1]/@Category','varchar(20)'), prod.value('.[1]/@Price','money')
FROM @prodList.nodes('/ProductList/Product') ProdList(prod);
B.
SELECT prod.value('@Name','varchar(100)'), prod.value('@Category','varchar(20)'), prod.value('@Price','money')
FROM @prodList.nodes('/ProductList/Product') ProdList(prod);
C.
WITH XMLNAMESPACES(DEFAULT 'urn;Wide_World_Importers/schemas/Products' as o)
SELECT prod.value('Name[1]','varchar(100)'), prod.value('Category[1]','varchar(20)'), prod.value('Price[1]','money')
FROM @prodList.nodes('/o:ProductList/o:Product') ProdList(prod);
D.
WITH XMLNAMESPACES(DEFAULT 'urn:Wide_World_Importers/schemas/Products')
SELECT prod.value('./@Name','varchar(100)'), prod.value('./@Category','varchar(20)'), prod.value('./@Price','money')
FROM @prodList.nodes('/ProductList/Product') ProdList(prod);
Answer: D
QUESTION 114
You have a table named Products.Product. The table has columns ProductID, Name, Size, and Category.
You have a variable named @XML with following XML value:
You are tasked to write a query that lists the products in Products.Product that match the categories listed in the XML document. You need to write a query to accomplish the task. Which query should you write?
A.
SELECT p.ProductID, p.Name, p.Size, p.Category
FROM Production.Product p CROSS APPLY @XML.nodes('//Category') as x(s)
B.
SELECT p.ProductID, p.Name, p.Size, p.Category
FROM Production.Product p OUTER APPLY @XML.nodes('//Category') as x(s)
C.
WITH XMLTable AS (
SELECT s.value('@Name','varchar(20)') as Category FROM @XML.nodes('//Category') as x(s) )
SELECT p.ProductID, p.Name, p.Size, p.Category FROM Production.Product p
INNER JOIN XMLTable x ON p.Category = x.Category
D.
WITH XMLTable AS (
SELECT s.value('@Category','varchar(20)') as Category FROM @XML.nodes('//Category') as x(s) )
SELECT p.ProductID, p.Name, p.Size, p.Category FROM Production.Product p INNER JOIN XMLTable x ON p.Category = x.Category
Answer: C
QUESTION 115
Your company exchanges information with other companies by using XML and Web services. Your manager asks you to remove a schema collection that is no longer used. Before dropping the schema, you should confirm that it is no longer in use.
You need to use a catalog view to determine if the schema collection is being used. Which catalog view should you use?
A.
sys.xml_schema_components
B.
sys.xml_schema_namespaces
C.
sys.xml_schema_collections
D.
sys.column_xml_schema_collection_usages
Answer: D
QUESTION 116
You have an XML schema that you must use to validate XML data in your database. You need to store this XML schema. Which code segment should you use?
A.
CREATE SCHEMA CustomerSchema
B.
CREATE DEFAULT CustomerSchema AS 'XML'
C.
CREATE PRIMARY XML INDEX CustomerSchema
D.
CREATE XML SCHEMA COLLECTION CustomerSchema
Answer: D
QUESTION 117
You have a table named Customers that has an XML column named CustomerData. There are currently no indexes on the table.
You use the following WHERE clause in a query:
WHERE CustomerData.exist ('/CustomerDemographic/@Age[.>="21"]') = 1
You need to create indexes for the query.
Which Transact-SQL statements should you use?
A.
CREATE CLUSTERED INDEX CL_IDX_Customer ON Customers(CustomerID);
CREATE PRIMARY XML INDEX PXML_IDX_Customer ON Customers(CustomerData);
CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData)
USING XML INDEX PXML_IDX_Customer FOR PATH;
B.
CREATE PRIMARY XML INDEX PXML_IDX_Customer ON Customers(CustomerData);
CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData)
USING XML INDEX PXML_IDX_Customer FOR VALUE;
C.
CREATE PRIMARY XML INDEX PXML_IDX_Customer ON Customers(CustomerData);
CREATE XML INDEX SXML_IDX_Customer ON Customer(CustomerData)
USING XML INDEX PXML_IDX_Customer FOR PATH;
D.
CREATE CLUSTERED INDEX CL_IDX_Customer ON Customers(CustomerID);
CREATE PRIMARY XML INDEX PXML_IDX_Customer ON Customers(CustomerData);
CREATE XML INDEX SXML_IDX_Customer_Property ON Customer(CustomerData)
USING XML INDEX PXML_IDX_Customer FOR VALUE;
Answer: A
QUESTION 118
You need to capture the execution plan for a query. Which statement should you use?
A.
SET FORCEPLAN ON;
B.
SET SHOWPLAN_XML ON;
C.
SET STATISTICS IO ON;
D.
SET STATISTICS TIME ON;
Answer: B
QUESTION 119
You are troubleshooting query performance on SQL Server 2008. You are tasked to create an estimated execution plan by using Transact-SQL. You should be able to view the plan graphically in SQL Server Management Studio. You need to ensure that the execution plan can be saved as a .sqlplan file.
Which Transact-SQL setting should you use?
A.
SET SHOWPLAN_ALL ON;
B.
SET SHOWPLAN_XML ON;
C.
SET STATISTICS XML ON;
D.
SET STATISTICS PROFILE ON;
Answer: B
QUESTION 120
You are troubleshooting query performance on SQL Server 2008. You are tasked to capture a graphical execution plan. You need to save the plan to a file that can be used by SQL Server Management Studio to display the graphical execution plan.
Which file extension should you use?
A.
.gif
B.
.xml
C.
.psql
D.
.sqlplan
Answer: D
QUESTION 121
You have run a server side trace that created 45 trace files. You want to load the trace files on your workstation in a database table called PerfData for further analysis. You need to load three files starting at c:\my_trace_38.trc.
Which Transact-SQL statement should you use?
A.
SELECT * INTO PerfData
FROM ::fn_trace_gettable('c:\my_trace.trc', 3)
B.
SELECT * INTO PerfData
FROM ::fn_trace_gettable('c:\my_trace_38.trc', 3)
C.
SELECT * INTO PerfData
FROM ::fn_trace_gettable('c:\my_trace38.trc', default)
D.
SELECT * INTO PerfData
FROM ( SELECT * FROM ::fn_trace_gettable ('c:\my_trace_38.trc', default)
UNION ALL
SELECT * FROM ::fn_trace_gettable ('c:\my_trace_39.trc', default)
UNION ALL
SELECT * FROM ::fn_trace_gettable ('c:\my_trace_40.trc', default) ) Trc
Answer: B
QUESTION 122
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?
A.
Lock:Deadlock
B.
Showplan XML
C.
Deadlock Graph
D.
Lock:Deadlock Chain
Answer: C
QUESTION 123
You are troubleshooting query performance on SQL Server 2008. You have profiler trace data in a table named PerfData. You need to determine which events are taking longer than one second of CPU time or run for more than two seconds.
Which Transact-SQL statement should you use?
A.
SELECT TextData, Duration, CPU FROM PerfData
WHERE EventClass = 12 AND ( CPU > 1000 OR Duration > 2000 )
B.
SELECT TextData, Duration, CPU FROM PerfData
WHERE EventClass = 12 AND ( CPU > 1000 OR Duration > 2000000 )
C.
SELECT TextData, Duration, CPU FROM PerfData
WHERE EventClass = 12 AND ( CPU > 1000000 OR Duration > 2000 )
D.
SELECT TextData, Duration, CPU FROM PerfData
WHERE EventClass = 12 AND ( CPU > 1000000 OR Duration > 2000000 )
Answer: B
QUESTION 124
You are using the Database Engine Tuning Advisor (DTA) to analyze a workload. You need to save the recommendations generated by the DTA. Which command should you use?
A.
Preview Workload Table
B.
Export Session Results
C.
Import Session Definition
D.
Export Session Definition
Answer: B
QUESTION 125
You need to capture and record a workload for analysis by the Database Engine Tuning Advisor (DTA).
Which tool should you use?
A.
DTA utility
B.
Activity Monitor
C.
SQL Server Profiler
D.
Performance Monitor
Answer: C
QUESTION 126
You have a database that uses stored procedures to perform INSERT, UPDATE, DELETE, and SELECT statements.
You are tasked with providing a recommendation of indexes to be created and dropped from the database.
You need to select the appropriate method to accomplish the task.
Which method should you use?
A.
Index Usage DMVs
B.
Missing Index DMVs
C.
SQL Server Profiler
D.
Database Engine Tuning Advisor
Answer: D
QUESTION 127
You are tasked with creating a workload that will be used by the Database Engine Tuning Advisor (DTA).
You need to create a workload in an appropriate format.
Which format should you choose? (Each correct answer represents a complete solution. Choose three.)
A.
XML File
B.
Transact-SQL Script
C.
SQL Server Event Log
D.
SQL Server Transaction Log
E.
SQL Server Profiler Trace File
F.
Performance Counter Log File
Answer: ABE
QUESTION 128
You need to build CREATE INDEX statements for all the missing indexes that SQL Server has identified.
Which dynamic management view should you use?
A.
sys.dm_db_index_usage_stats
B.
sys.dm_db_missing_index_details
C.
sys.dm_db_missing_index_columns
D.
sys.dm_db_missing_index_group_stats
Answer: B
QUESTION 129
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?
A.
Disk
B.
CPU
C.
Memory
D.
Network
Answer: B
QUESTION 130
You attempt to query sys.dm_db_index_usage_stats to check the status on the indexes in the Contoso database. The query fails and you receive the following error:
"The user does not have permission to perform this action."
You need to have the least amount of permissions granted to access the dynamic management views.
Which permissions should be granted?
A.
CONTROL
B.
VIEW SERVER STATE
C.
VIEW DATABASE STATE
D.
CREATE EXTERNAL ACCESS ASSEMBLY
Answer: B
QUESTION 131
You are given a database design to evaluate. All of the tables in this database should have a clustered index.
You need to determine the tables that are missing a clustered index by using the system catalog views.
Which Transact-SQL statement should you use?
A.
SELECT name AS table_name FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
ORDER BY name;
B.
SELECT name AS table_name
FROM sys.tables WHERE OBJECTPROPERTY(object_id,'TableHasUniqueCnst') = 0 ORDER BY name;
C.
SELECT name AS table_name FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0 AND
OBJECTPROPERTY(object_id,'TableHasUniqueCnst') = 1 ORDER BY name;
D.
SELECT name AS table_name FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 1 AND OBJECTPROPERTY(object_id,'TableHasUniqueCnst') = 1
ORDER BY name;
Answer: A
QUESTION 132
You need to identify which tables are referenced by name in a stored procedure that does not use dynamic SQL.
Which catalog view should you use?
A.
sys.procedures
B.
INFORMATION_SCHEMA.TABLES
C.
INFORMATION_SCHEMA.ROUTINES
D.
sys.sql_expression_dependencies
Answer: D
QUESTION 133
You manage a SQL Server 2008 database that is located at your company's corporate headquarters.
The database contains a table named dbo.Sales. You need to create different views of the dbo.Sales table that will be used by each region to insert, update, and delete rows. Each regional office must only be able to insert, update, and delete rows for their respective region.
Which view should you create for Region1?
A.
CREATE VIEW dbo.Region1Sales
AS
SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales
WHERE RegionID = 1;
B.
CREATE VIEW dbo.Region1Sales
AS
SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales
WHERE RegionID = 1 WITH CHECK OPTION;
C.
CREATE VIEW dbo.Region1Sales
WITH SCHEMABINDING
AS SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales WHERE RegionID = 1;
D.
CREATE VIEW dbo.Region1Sales
WITH VIEW_METADATA
AS
SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales
WHERE RegionID = 1;
Answer: B