SQL Server Mock Test 8

10%

Question 1 of 10

1. Which of the following is a valid syntax for WITH CHANGE_TRACKING_CONTEXT?

WITH CHANGE_TRACKI_CONTEXT (@sync_client_id_binary)
INSERT INTO Sales.Customer (CustomerId, CustomerName, SalesPerson,
 CustomerType)
VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType)
SET @sync_row_count = @@rowcount

All of the mentioned

WITH CHANGE_TRACKING_CON (@sync_client_id_binary)
INSERT INTO Sales.Customer (CustomerId, CustomerName, SalesPerson,
 CustomerType)
VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType)
SET @sync_row_count = @@rowcount

WITH CHANGE_TRACKING_CONTEXT (@sync_client_id_binary)
INSERT INTO Sales.Customer (CustomerId, CustomerName, SalesPerson,
 CustomerType)
VALUES (@CustomerId, @CustomerName, @SalesPerson, @CustomerType)
SET @sync_row_count = @@rowcount

Question 1 of 10

Question 2 of 10

2. Which of the following query selects incremental inserts from the Sales.Customer table at the server?

IF @sync_initialized = 0
  SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] 
  FROM Sales.Customer LEFT OUTER JOIN 
  CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT
  ON CT.[CustomerId] = Sales.Customer.[CustomerId]
ELSE
BEGIN
  SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType]
  FROM Sales.Customer JOIN CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT
  ON CT.[CustomerId] = Sales.Customer.[CustomerId]
  WHERE (CT.SYS_CHANGE = 'I' AND CT.SYS_CHANGE_CREATION_VERSION 
  <= @sync_new_received_anchor)
END

IF @sync_initialized == 0
  SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] 
  FROM Sales.Customer LEFT OUTER JOIN 
  CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT
  ON CT.[CustomerId] = Sales.Customer.[CustomerId]
ELSE
BEGIN
  SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType]
  FROM Sales.Customer JOIN CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT
  ON CT.[CustomerId] = Sales.Customer.[CustomerId]
  WHERE (CT.SYS_CHANGE = 'I' AND CT.SYS_CHANGE_CREATION_VERSION 
  <= @sync_new_received_anchor)
END

IF @sync_initialized =0
  SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType] 
  FROM Sales.Customer LEFT OUTER JOIN 
  CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT
  ON CT.[CustomerId] = Sales.Customer.[CustomerId]
ELSE
BEGIN
  SELECT Sales.Customer.[CustomerId], [CustomerName], [SalesPerson], [CustomerType]
  FROM Sales.Customer JOIN CHANGETABLE(CHANGES Sales.Customer, @sync_last_received_anchor) CT
  ON CT.[CustomerId] = Sales.Customer.[CustomerId]
  WHERE (CT.SYS_CHANGE_OPERATION = 'I' AND CT.SYS_CHANGE_CREATION_VERSION 
  <= @sync_new_received_anchor)
END

None of the mentioned

Question 2 of 10

Question 3 of 10

3. Which of the following query returns the average latency for the most recent sessions?

SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0

SELECT latency FROM sys.dm_cdc_audit_scan_sessions WHERE session_id = 0

SELECT latency FROM sys.dm_cdc_log_scan_ WHERE session_id = 0

SELECT latency FROM sys.dm_cdc_log_audit_sessions WHERE session_id = 0

Question 3 of 10

Question 4 of 10

4. Use to bind an integer variable to the first parameter marker in an SQL statement.

SQLParameter

All of the mentioned

SQLBind

SQLBindParameter

Question 4 of 10

Question 5 of 10

5. Which of the following index operation require additional disk space?

All of the mentioned

DROP INDEX MOVE TO

ALTER INDEX ADD CONSTRAINT

UPDATE INDEX

Question 5 of 10

Question 6 of 10

6. Which of the DDL statement creates a session object that contains the metadata?

None of the mentioned

CREATE EVENT SESSION

CREATE SESSION

CREATE EVENT SESSION METADATA

Question 6 of 10

Question 7 of 10

7. Point out the wrong statement.

The purpose of the named role is to control access to the change data

By default, all of the columns in the source table are not identified as captured columns

By default, the change table is located in the default filegroup of the database

None of the mentioned

Question 7 of 10

Question 8 of 10

8. Which of the following disable database for Change Data Capture?

EXEC sys.sp_cdc_ensable_db

All of the mentioned

EXEC sys.sp_disable_db

EXEC sys.sp_cdc_disable_db

Question 8 of 10

Question 9 of 10

9. Point out the wrong statement.

Change data capture supports up to two capture instances for a single tracked source table

The name of the function to wrap the all changes query is fn_all_changes_ followed by the capture instance name.

cdc.lsn_time_mapping applies to SQL Server 2014

Datetime wrappers are owned by the user, and not are created in the default schema of the caller

Question 9 of 10

Question 10 of 10

10. Which of the following query is used to enable a disabled index using CREATE INDEX?

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
   (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = OFF);

None of the mentioned

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
   (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON);

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
   (OrganizationLevel, OrganizationNode)
WITH (DROP_EXIST = ON);

Question 10 of 10

« Prev - SQL Server Mock Test 7

» Next - SQL Server Mock Test 9