- (Exam Topic 2)
You administer a Microsoft SQL Server 2016 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?
Correct Answer:
A
- (Exam Topic 3)
Your company has a SQL Azure subscription.
You implement a database named Database1. Database1 has two tables named Table1 and Table2. You create a stored procedure named sp1. Sp1 reads data from Table1 and inserts data into Table2. A user named User1 informs you that he is unable to run sp1.
You verify that User1 has the SELECT permission on Table1 and Table2. You need to ensure that User1 can run sp1.
The solution must minimize the number of permissions assigned to User1. What should you do?
Correct Answer:
D
References:
http://msdn.microsoft.com/en-us/library/ms191291.aspx
- (Exam Topic 2)
You want to reproduce the same SQL Server 2016 installation configuration across five servers. Which of the following files will you generate by using SQL Server Setup to accomplish this goal?
Correct Answer:
D
- (Exam Topic 4)
You manage a Microsoft SQL Server that has a database named salesOrders. Users connect to the database by using a client application.
Users report that the application cannot connect to the database. You observe that the database storage has experienced a failure.
You need to repair the database and ensure that applications can connect to the database.
Which three action should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Solution:
The specified database must be in single-user mode to use one of the following repair options. REPAIR_REBUILD
Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.
Does this meet the goal?
Correct Answer:
A
- (Exam Topic 4)
You have a Microsoft SQL Server instance that hosts a database named DB1 that contains 800 gigabyte (GB) of data. The database is used 24 hours each day. You implement indexes and set the value of the Auto Update Statistics option set to True.
Users report that queries take a long time to complete.
You need to identify tables that meet the following requirements:
< > >< > >
Solution:
Example:
SELECT obj.name, obj.object_id, stat.name, stat.stats_id, last_updated, modification_counter FROM sys.objects AS obj
JOIN sys.stats stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp WHERE modification_counter > 1000
order by modification_counter desc;
sys.sysindexes contains one row for each index and table in the current database.
rowmodctr counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.
Example 2: SELECT
id AS [Table ID]
, OBJECT_NAME(id) AS [Table Name]
, name AS [Index Name]
, STATS_DATE(id, indid) AS [LastUpdated]
, rowmodctr AS [Rows Modified] FROM sys.sysindexes
WHERE STATS_DATE(id, indid)<=DATEADD(DAY,-1,GETDATE())
AND rowmodctr>10 AND (OBJECTPROPERTY(id,'IsUserTable'))=1 References:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/493b90e3-cdb8-4a16-8249-849ba0f82fcb/how-to-fin
Does this meet the goal?
Correct Answer:
A
