Developing SQL Databases - Exam 70-762 Certification Exam

Dynamic data masking is use to mask the data so user that have permission can see the actual data. In SQL server 2016 which user always has permission to perform operation like INSERT, UPDATE, DELETE, SELECT?

Options are :

  • dbo (Correct)
  • any user
  • HR
  • admin

Answer : dbo

Explanation In SQL server 2016, dbo is the default user so it has all permission. When we create user then we have to provide permission.

70-764 Administering a SQL Database Infrastructure Mock Exam

You have to estimate the memory usage of a Microsoft SQL server instance.

Requirement                          Details

Req-1                                   Total amount of memory currently use by SQL server

Req-2                                     Total amount of memory required by SQL server

Req-3                                     Total amount of memory used by a process

Which of the following performance factor order you use for each requirement?

i) SQL Server: Memory Manager: Total Server Memory(KB)

ii)Memory: Available Bytes

iii)SQL Server: Memory Manager: Granted Workspace Memory(KB)

iv) Process: Working Set

Options are :

  • (ii) – (i) – (iv)
  • (i) – (iii) – (iv) (Correct)
  • (iv) – (ii) – (i)
  • None of all

Answer : (i) – (iii) – (iv)

Explanation In req-1 we have to find total amount of memory currently use by SQL server so we use SQL server: Memory Manager: Total Server Memory(KB), for req-2 we have to find total amount of memory required by SQL server so we use Granted workspace memory it returns the total memory allocated to SQL server. For reu-3 we have to return total amount of memory use by a process so, we use process: working set.

In SQL Server use transaction isolation levels to manage conflict between two transactions attempting to change the same data at the same time. Which of the following isolation level is default in SQL server 2016?

Options are :

  • Repeatable Read
  • Serializable
  • Snapshot
  • Read Committed (Correct)

Answer : Read Committed

Explanation In all version of SQL server the default transaction isolation level is Read Committed. In this isolation level, a transaction cannot read uncommitted data that is currently added or change by other transaction.

70-764 Administering a SQL Database Infrastructure Question and Answers

You are monitoring the SQL azure database.

The database experience high use of CPU. You have to determine which SQL query consume more CPU. How you use the query?

Select

highest_cpu_queries.plan_handle,

highest_cpu_queries.total_worker_time,

q.dbid,

q.objectid,

q.number

FROM

                  <option1>

ORDER BY o.total_worker_time desc) as highest_cpu_queries

CROSS APPLY sys.dm.exec_sql_text(plan_handle) as q

ORDER BY <option2>

Select appropriate answer for option 1 and 2?

Options are :

  • Option 1: sys.dm_exec_query_stats o Option 2: highest_cpu_queries.total_worker_time DESC (Correct)
  • Option 1: highest_cpu_queries.total_worker_time DESC Option 2: sys.dm_exec_query_stats o
  • Option 1: sys.dm_tran_locks Option 2: sys.dm_exec_query_stats o
  • Option 1: sys.dm_os_waiting_tasks Option 2: highest_cpu_queries.total_worker_time DESC

Answer : Option 1: sys.dm_exec_query_stats o Option 2: highest_cpu_queries.total_worker_time DESC

Explanation Here we have to determine which query consume more CPU so we have to use sys.dm_exec_query_stats system stored procedure. And we present this time into descending order.

In SQL server 2016 which of the following resource pool is used for resources required to run the database engine?

Options are :

  • Default
  • External
  • User-defined resource pool
  • Internal (Correct)

Answer : Internal

Explanation Internal resource pool is required to run the database engine. We cannot change the resource configuration for the internal resource configuration.

Which of the following Dynamic Management View(DVM) provides the information about existing locks and locks that have been requested but not granted?

Options are :

  • sys.dm_os_waiting_tasks
  • sys.dm_os_wait_stats
  • sys.dm_tran_locks (Correct)
  • All of the options are TRUE

Answer : sys.dm_tran_locks

Explanation Sys.dm_os_waiting_tasks DVM provides information about tasks that are waiting for resources. Sys.dm_os_wait_stats DVM provides information about processes that are waiting while locks are taken. ¬¬¬

70-764 Administering a SQL Database Infrastructure Question and Answers

Which of the following is true related to the default SQL SERVER INSTANCE?

Options are :

  • You cannot install a named instance without installing the default instance first
  • Only one instance can exist on each SQL Server
  • You cannot connect to the default instance
  • Uses the same name as the computer name on which it is installed (Correct)

Answer : Uses the same name as the computer name on which it is installed

Explanation Each instance of SQL server contains of a different set of services that have specific setting for collection and other options. Only one instance of SQL server, regardless of version, can be the default instance at one time.

You have database that have following tables:

PostCategory, PostEntry, ItemReview, Item, and SalesPeople. The tables are created using following script:

CREATE TABLE PostCategory

(

            Cat_id int NOT NULL PRIMARY KEY,

            Cat_name varchar (20)

);

CREATE TABLE PostEntry

(

            Entry int NOT NULL PRIMARY KEY,

            Entry_title varchar (50),

            Category int NOT NULL FOREIGN KEY REFERENCES PostCategory(Cat_id)

);

CREATE TABLE ItemReview

(

            Itemreview_id int IDENTITY(1, 1) PRIMARY KEY,

            Item int NOT NULL,

            Review nvarchar (1000) NOT NULL

);

CREATE TABLE Item

(

            Item_id int IDENTITY(1, 1) PRIMARY KEY,

            Name varchar (1000) NOT NULL

);

CREATE TABLE SalesPeople

(

            Salepeople_id int IDENTITY(1, 1) PRIMARY KEY,

            Name varchar (1000) NOT NULL,

            Sales_id int NOT NULL

);

You also have Order, ItemTypes and Sales_History tables. The Transact-SQL statements for these tables are not available.

You have to add constraint to the Sales_History table.

  • a constraint on the Sale_id column allows the filed contains the record identifier
  • a constraint that uses the Item_id column to references the item column of the ItemTypes table.
  • a constraint on the Cate_id column allows one row with null value in column.
  • a constraint that limits the Sale_price column to values greater than four Finance department

Users must be able to retrieve data from the SalesHistory table for sales people where the value of the SalesYTD column is above a certain threshold.

You have to update the SalesHistory table. Complete the T-SQL statement.

To answer? select the apportiate option.

CREATE TABLE Sales_History

(

            Sale_id int OPTION 1,

            Item_id Smallint Null,

            Sale_date datetime NULL,

            Sale_Price money OPTION 2,

            Cat_id int OPTION 3,

            OPTION 4

);

Options are :

  • Option 1= IDENTITY(1,4) NOT NULL PRIMARY KEY Option 2= NULL CHECK(Sale_price>4) Option 3= UNIQUE Option 4= CONSTRAINT fk_SalesHistory(Item_id) REFERENCES ItemTypes(Item_id) (Correct)
  • Option 1= IDENTITY(1,4) Option 2= NOT NULL Option 3= NULL CHECK(Sale_price>4) Option 4= CONSTRAINT fk_SalesHistory(Item_id) REFERENCES Sales_History(Cat_id)
  • Option 1= UNIQUE Option 2= NOT NULL Option 3= NOT NULL Option 4= CONSTRAINT fk_SalesHistory(Item_id) REFERENCES Item(Item_id)
  • Option 1= IDENTITY(1,4) NOT NULL Option 2= NULL CHECK(Sale_price>2) Option 3= PRIMARY KEY Option 4= CONSTRAINT fk_SalesHistory(Sale_id) REFERENCES ItemTypes(Item_id)

Answer : Option 1= IDENTITY(1,4) NOT NULL PRIMARY KEY Option 2= NULL CHECK(Sale_price>4) Option 3= UNIQUE Option 4= CONSTRAINT fk_SalesHistory(Item_id) REFERENCES ItemTypes(Item_id)

Explanation Here we have to add record identifier for Sale_id column so we have to use PRIMARY KEY and it must be auto generated so we add IDENTITY(1,4) NOT NULL PRIMARY KEY in place of option 1. In option 2 we have to ensure that Sale_price column contains value greater than four. In option 3 we have to ensure that Cate_id allow one NULL value in column so add UNIQUE constraint. In Option 4 a foreign key constraint must be put on the Item_id referencing the ItemTypes table, as a Constraint that uses the Item_id column to the reference the Item Column of the ItemTypes table is required.

The Bank_account table is created using the following T-SQL statement:

CREATE TABLE Bank_account

(

            Account_no int NOT NULL,

            Item_code char(2) NOT NULL,

            Status int NOT NULL,

            Open_date date NOT NULL,

            Close_date date,

            Balance decimal(15, 2),

            Available_bal decimal(15,2) 

)

There are more than 2 billon records in the Bank_account table. The Account_no column is uniquely identifying each account. The Item_code column has 200 different values. The value is evenly distributed in table.

You currently run the below T-SQL Query:

SELECT Item_code, SUM(Balance) as Totalbal FROM Bank_account where Item_code<> ‘AB’ GROUP BY Item_code;

SELECT Account_no, Balance FROM Bank_account WHERE Production=’AB’;

You have to avoid table scan when you run the query.

You need to create one or more indexes for the table Bank_Account

Which T-SQL statement you run?

Options are :

  • CREATE NONCLUSTERED INDEX pk_bankaccount ON Bank_account (Account_no); CREATE NONCLUSTERED INDEX ix_bankacc_itemcode ON Bank_account (Item_code) INCLUDE (Balance);
  • CREATE CLUSTERED INDEX pk_bankaccount ON Bank_account (Account_no); CREATE NONCLUSTERED INDEX ix_bankacc_itemcode ON Bank_account (Item_code) INCLUDE (Balance); (Correct)
  • CREATE CLUSTERED INDEX pk_bankaccount ON Bank_account (ProductCode);
  • CREATE NONCLUSTERED INDEX ix_bankacc_itemcode ON Bank_account (Item_code) INCLUDE (Account_no);

Answer : CREATE CLUSTERED INDEX pk_bankaccount ON Bank_account (Account_no); CREATE NONCLUSTERED INDEX ix_bankacc_itemcode ON Bank_account (Item_code) INCLUDE (Balance);

Explanation Here we have to create clustered index on the Account_no so it will be unique. Create a nonclustered index on Item_code and it include Balance column.

70-764 Administering a SQL Database Infrastructure Tests

You have database that experience deadlock issue when user run the query.

You have to ensure that all deadlocks are record in XML format. What You do?

Options are :

  • Enable trace flag 1222 in the startup options for Microsoft SQL Server. (Correct)
  • Use the Microsoft SQL Server Profiler Lock:Deadlock event class.
  • Create a Microsoft SQL Server Integration Services package that uses sys.dm_tran_locks.
  • Enable trace flag 1224 by using the Database Cpmsistency Checker(BDCC).

Answer : Enable trace flag 1222 in the startup options for Microsoft SQL Server.

Explanation When deadlock occur, trace flag 1222 and 1204 returns information about SQL server error log. Flag 1204 reports the deadlock information formatted by each node involve in deadlock. Flag 1222 formats deadlock information first by Processes and then by resources and returns information in XML format.

You have to create an application to track Orders.

For that you have to create a database that meet the following requirements:

  • Return a 0 value if data insertion is successful into the Orders table.
  • Return a 1 value if data insertion is fail into the Orders table.
  • Support TRY…CATCH error handling
  • Be written by using T-SQL Statement.

What should you create?

Options are :

  • Scalar-valued function
  • CLR procedure
  • DML trigger
  • User-defined procedure (Correct)

Answer : User-defined procedure

Explanation Stored Procedures can return values but a trigger cannot return a value. user defined functions not support error handling using try/catch.

You have database that have following tables:

PostCategory, PostEntry, ItemReview, Item, and SalesPeople. The tables are created using following script:

CREATE TABLE PostCategory

(

            Cat_id int NOT NULL PRIMARY KEY,

            Cat_name varchar (20)

);

CREATE TABLE PostEntry

(

            Entry int NOT NULL PRIMARY KEY,

            Entry_title varchar (50),

            Category int NOT NULL FOREIGN KEY REFERENCES PostCategory(Cat_id)

);

CREATE TABLE ItemReview

(

            Itemreview_id int IDENTITY(1, 1) PRIMARY KEY,

            Item int NOT NULL,

            Review nvarchar (1000) NOT NULL

);

CREATE TABLE Item

(

            Item_id int IDENTITY(1, 1) PRIMARY KEY,

            Name varchar (1000) NOT NULL

);

CREATE TABLE SalesPeople

(

            Salepeople_id int IDENTITY(1, 1) PRIMARY KEY,

            Name varchar (1000) NOT NULL,

            Sales_id int NOT NULL

);

You have to create a memory-optimized table Sales_order. The table must meet following requirements:

  • Table must hold 10 million unique records.
  • Table must use checkpoints to minimize I/O operations and not use transaction logging.
  • Data loss is acceptable.

Performance for queries against the Sales_Order table that use Where clauses with exact equality operations must be optimized.

Choose appropriate options to complete the table definition?

CREATE TABLE Sales_Order(

Saleorder_id int PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000000), Salesorder_code varchar(5) NOT NULL INDEX ix_salesorder OPTION 1,

Emp_id int NOT NULL,

Cust_id int NOT  NULL,

Sales_amt money

)

WITH (Memory_OPTIMIZED=ON, DURABILITY = OPTION 2)

Options are :

  • OPTION 1= CLUSTERED OPTION 2= SCHEMA_AND_DATA
  • OPTION 1= NONCLUSTERED OPTION 2= SCHEMA_AND_DATA
  • OPTION 1= NONCLUSTERED OPTION 2= SCHEMA_ONLY
  • OPTION 1= NONCLUSTERED HASH WITH(BUCKET_COUNT=10000000 ) OPTION 2= SCHEMA_ONLY (Correct)

Answer : OPTION 1= NONCLUSTERED HASH WITH(BUCKET_COUNT=10000000 ) OPTION 2= SCHEMA_ONLY

Explanation Option 1= NONCLUSTERED HASH WITH (BUCKET_COUNT=10000000 )because hash is more preferable over nonclustered index when query test indexed column using WHERE condition we should use a bucket count of 10 million. Option 2= SCHEMA_ONLY because it indicates the table is non-durable. The table schema is persisted but any update to data is not persisted upon restart or failure of the database. This is allowed when MEMORY_OPTIMIZED= ON. The SCHEMA_AND_DATA indicates table is durable means data is persisted over restart and failure of database. It is the default value.

70-764 Administering a SQL Database Infrastructure

You have a view that has an aggregate. You must be able to change the value of column in view and changes must reflect the table that the view use. You have to ensure that you update the view.

What should you use?

Options are :

  • Partitioned view
  • DML Trigger (Correct)
  • Schema-bound view
  • Encrypted view

Answer : DML Trigger

Explanation If the View is having AGGREGATE functions, it will not allow to do update on the view. To do so INSTEAD OF Trigger is used. and INSTEAD OF trigger is type of DML Trigger.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions