Microsoft 70-461 Exam Practice Test Instant Access

Question 1

You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table.

You need to assign a PartitionNumber for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies the following conditions:

* Always starts with 1.

* Starts again from 1 after it reaches 100.

Which Transact-SQL statement should you use?

ACREATE SEQUENCE CustomerSequence AS int
START WITH 0
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence

BCREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
CYCLE
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence

CCREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1
DROP SEQUENCE CustomerSequence

DCREATE SEQUENCE CustomerSequence AS int
START WITH 1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 100
CYCLE
UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
DROP SEQUENCE CustomerSequence

Answer : B

http://msdn.microsoft.com/en-us/library/ff878091.aspx

Question 2

Answer : D

This topic describes SQL Server 2008 collation options for instances of SQL Server that require compatibility with versions of SQL Server that do not use collations.

The following SQL collations are listed on the Collation Settings page of the SQL Server Installation Wizard.

References: https://msdn.microsoft.com/it-it/library/ms144250(v=sql.105).aspx

Question 3

You need to build a table structure for a stored procedure to support data entry from a website form. The website form must meet the following requirements:

Which two actions should you perform? Each correct answer presents part of the solution.

AAdd the CHECK constraint to the table structure.

BAdd the DATEPART function to the stored procedure.

CAdd the DEFAULT constraint to the table structure.

DAdd the SYSDATETIMEOFFSET function to the stored procedure.

EAdd the ISDATE function to the stored procedure.

Answer : D, E

SYSDATETIMEOFFSET returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running.

ISDATE returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

References:https://msdn.microsoft.com/en-us/library/bb677334.aspx

Question 4

You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code.

You need to protect the code from being viewed by your customers.

Which stored procedure option should you use?

AENCRYPTBYKEY

BENCRYPTION

CENCRYPTBYPASSPHRASE

DENCRYPTBYCERT

Answer : B

http://technet.microsoft.com/en-us/library/bb510663.aspx

http://technet.microsoft.com/en-us/library/ms174361.aspx

http://msdn.microsoft.com/en-us/library/ms187926.aspx

http://technet.microsoft.com/en-us/library/ms190357.aspx

http://technet.microsoft.com/en-us/library/ms188061.aspx

Question 5

You are maintaining a Microsoft SQL Server database. You run the following query:

You observe performance issues when you run the query. You capture the following query execution plan:

You need to ensure that the query performs returns the results as quickly as possible.

Which action should you perform?

AAdd a new index to the ID column of the Person table.

BAdd a new index to the EndDate column of the History table.

CCreate a materialized view that is based on joining data from the ActiveEmployee and History tables.

DCreate a computed column that concatenates the GivenName and SurName columns.

Answer : A

Cost is 53% for the Table Scan on the Person (p) table. This table scan is on the ID column, so we should put an index on it.

Question 6

You administer a Microsoft SQL Server 2012 database that includes a table named Products. The Products table has columns named ProductId, ProductName, and CreatedDateTime.

The table contains a unique constraint on the combination of ProductName and CreatedDateTime.

You need to modify the Products table to meet the following requirements:

Remove all duplicates of the Products table based on the ProductName column.

Retain only the newest Products row.

Which Transact-SQL query should you use?

AWITH CTEDupRecords
AS
(
SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
cte.ProductName = p.ProductName
AND cte.CreatedDateTime >
p.CreatedDateTime

BWITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName
AND p.CreatedDateTime <
cte.CreatedDateTime

CWITH CTEDupRecords
AS
(
SELECT MIN(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName

DWITH CTEDupRecords
AS
(
SELECT MAX(CreatedDateTime) AS CreatedDateTime, ProductName
FROM Products
GROUP BY ProductName
HAVING COUNT(*) > 1
)
DELETE p
FROM Products p
JOIN CTEDupRecords cte ON
p.ProductName = cte.ProductName

Answer : B

Question 7

You use Microsoft SQL Server 2012 to develop a database application.

You create a stored procedure named DeleteJobCandidate.

You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number.

Which Transact-SQL statement should you use?

ADECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;
EXEC DeleteJobCandidate
SELECT @ErrorVar = @@ERROR,
@RowCountVar = @@ROWCOUNT;
IF (@ErrorVar <> 0)
PRINT N'Error = ' + CAST(@@ErrorVar AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@RowCountVar AS NVARCHAR(8));
GO

BDECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;
EXEC DeleteJobCandidate
SELECT @ErrorVar = ERROR_STATE(),
@RowCountVar = @@ROWCOUNT;
IF (@ErrorVar <> 0)
PRINT N'Error = ' + CAST(ERROR_STATE() AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@RowCountVar AS NVARCHAR(8));
GO

CEXEC DeleteJobCandidate
IF (ERROR_STATE() != 0)
PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));
GO

DEXEC DeleteJobCandidate
PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8));
GO

Answer : A

http://msdn.microsoft.com/en-us/library/ms190193.aspx

http://msdn.microsoft.com/en-us/library/ms188790.aspx

Microsoft Querying Microsoft SQL Server 2012/2014 70-461 Exam Practice Test