2015 Latest released Microsoft Official 70-464 Practice Exam Question Free Download From Braindump2go Now! All New Updated 191 Questions And Answers are Real Questions from Microsoft Exam Center!
Vendor: Microsoft
Exam Code: 70-464
Exam Name: Developing Microsoft SQL Server 2014 Databases Exam
QUESTION 141
You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema.
Some users must be prevented from deleting records in any of the tables in the Sales schema.
You need to manage users who are prevented from deleting records in the Sales schema.
You need to achieve this goal by using the minimum amount of administrative effort.
What should you do?
A. Create a custom database role that includes the users.
Deny Delete permissions on the Sales schema for the custom database role.
B. Include the Sales schema as an owned schema for the db_denydatawriter role.
Add the users to the db_denydatawriter role.
C. Deny Delete permissions on each table in the Sales schema for each user.
D. Create a custom database role that includes the users.
Deny Delete permissions on each table in the Sales schema for the custom database role.
Answer: A
QUESTION 142
You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 environment.
All DBAs are members of the DOMAIN\JrDBAs Active Directory group.
You grant DOMAIN\JrDBAs access to the SQL Server.
You need to create a server role named SpecialDBARole that can perform the following functions:
– View all databases.
– View the server state.
– Assign GRANT, DENY, and REVOKE permissions on logins.
You need to add DOMAIN\JrDBAs to the server role.
You also need to provide the least level of privileges necessary.
Which SQL statement or statements should you use? Choose all that apply.
A. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin;
B. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs];
C. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin;
D. GRANT VIEW DEFINITION TO [SpecialDBARole];
E. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin;
F. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole];
Answer: BCF
QUESTION 143
You administer a Microsoft SQL Server 2012 database.
You provide temporary securityadmin access to User1 to the database server.
You need to know if User1 adds logins to securityadmin.
Which server-level audit action group should you use?
A. SERVER_STATE_CHANGE_GROUP
B. SERVER_PRINCIPAL_IMPERSONATION_GROUP
C. SUCCESSFUL_LOGIN_GROUP
D. SERVER_ROLE_MEMBER_CHANGE_GROUP
Answer: D
QUESTION 144
You administer a Microsoft SQL Server 2012 instance.
You need to stop a blocking process that has an SPID of 64 without stopping other processes. What should you do?
A. Execute the following Transact-SQL statement:
EXECUTE sp_KillSPID 64
B. Restart the SQL Server service.
C. Execute the following Transact-SQL statement:
KILL 64
D. Execute the following Transact-SQL statement:
ALTER SESSION KILL ’64’
Answer: C
QUESTION 145
You administer a Microsoft SQL Server 2012 database.
You need to ensure that the size of the transaction log file does not exceed 2 GB.
What should you do?
A. Execute sp_configure ‘max log size’, 2G.
B. use the ALTER DATABASE…SET LOGFILE command along with the maxsize parameter.
C. In SQL Server Management Studio, right-click the instance and select Database Settings.
Set the maximum size of the file for the transaction log.
D. in SQL Server Management Studio, right-click the database, select Properties, and then
click Files.
Open the Transaction log Autogrowth window and set the maximum size of the file.
Answer: D
QUESTION 146
You administer a Microsoft SQL Server 2012 server.
You plan to deploy new features to an application.
You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance.
What should you do?
A. Query the sys.dm_db_index_usage_stats DMV.
B. Query the sys.dm_db_missing_index_details DMV.
C. Use the Database Engine Tuning Advisor.
D. Query the sys.dm_db_missing_index_columns DMV.
Answer: C
QUESTION 147
You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to write messages to the Application Log when users are added to or removed from a fixed server role in Server01.
What should you create?
A. a Database Audit Specification
B. a Policy
C. an Alert
D. a SQL Profiler Trace
E. a Resource Pool
F. an Extended Event session
G. a Server Audit Specification
Answer: G
QUESTION 148
You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to be notified immediately when fatal errors occur on Server01.
What should you create?
A. an Alert
B. a Server Audit Specification
C. an Extended Event session
D. a Resource Pool
E. a Policy
F. a SQL Profiler Trace
G. a Database Audit Specification
Answer: A
QUESTION 149
You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01.
You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server.
What should you create?
A. an Extended Event session
B. a Policy
C. a Database Audit Specification
D. an Alert
E. a Server Audit Specification
F. a SQL Profiler Trace
G. a Resource Pool
Answer: F
QUESTION 150
You create an availability group that has replicas named HA/Server01 and HA/Server02. Currently, HA/Server01 is the primary replica.
You have multiple queries that read data and produce reports from the database.
You need to offload the reporting workload to the secondary replica when HA/Server01 is the primary replica.
What should you do?
A. Set the Availability Mode property of HA/Server02 to Asynchronous commit.
B. Set the Readable Secondary property of HA/Server02 to Read-intent only.
C. Set the Connections in Primary Role property of HA/Server01 to Allow read/write connections.
D. Set the Availability Mode property of HA/Server01 to Asynchronous commit.
Answer: B
QUESTION 151
You administer two Microsoft SQL Server 2012 servers.
Each server resides in a different, untrusted domain.
You plan to configure database mirroring.
You need to be able to create database mirroring endpoints on both servers.
What should you do?
A. Configure the SQL Server service account to use Network Service.
B. Use a server certificate.
C. Use a database certificate.
D. Configure the SQL Server service account to use Local System.
Answer: B
QUESTION 152
You administer a Microsoft SQL Server 2012 instance.
You need to configure a new database to support FILETABLES.
What should you do? Choose all that apply.
A. Disable FILESTREAM on the Database.
B. Enable FILESTREAM on the Server Instance.
C. Configure the Database for Partial Containment.
D. Create a non-empty FILESTREAM file group.
E. Enable Contained Databases on the Server Instance.
F. Set the FILESTREAM directory name on the Database.
Answer: BDF
QUESTION 153
You administer two instances of Microsoft SQL Server 2012.
You deploy an application that uses a database on the named instance.
The application is unable to connect to the database on the named instance.
You need to ensure that the application can connect to the named instance.
What should you do?
A. Configure the application as data-tiered.
B. Open port 1433 on the Windows firewall on the server.
C. Configure the named SQL Server instance to use an account that is a member of the Domain
Admins group.
D. Start the SQL Server Browser Service.
Answer: D
QUESTION 154
You have a SQL Server 2012 database named Database1.
You execute the following code:
You insert 3 million rows into Sales.
You need to reduce the amount of time it takes to execute Proc1.
What should you do?
A. Run the following:
ALTER TABLE Sales ALTER COLUMN OrderDate datetime NOT NULL;
B. Change the WHERE clause to the following:
WHERE OrderDate BETWEEN CAST(@date1,char(10))
AND CAST(@date2,char(10))
C. Remove the ORDER BY clause from the stored procedure.
D. Run the following:
DROP INDEX IX_Sales_OrderDate;
GO
CREATE INDEX IX_Sales_OrderDate ON Sales(OrderDate);
GO
Answer: C
Explanation:
http://www.c-sharpcorner.com/UploadFile/skumaar_mca/good-practices-to-write-the-stored-procedures-in-sqlserver/
QUESTION 155
Your network contains a server that has SQL Server 2012 installed.
You create a table by using the following script:
You need to recommend a solution to ensure that each combination of ProductName and ProductManufacturer is not duplicated.
What should you recommend creating?
A. A UNIQUE constraint
B. A CHECK constraint
C. A filtered index
D. A column store index
Answer: A
Braindump2go New Published Exam Dumps: Microsoft 70-464 Practice Tests Questions, 191 Latest Questions and Answers from Official Exam Centre Guarantee You a 100% Pass! Free Download Instantly!
