SQL Server Mock Test 8

10%

Question 1 of 10

1. 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_OPERATION = '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

None of the mentioned

Question 1 of 10

Question 2 of 10

2. To produce execution plan output by using most Showplan Transact-SQL SET options, users must have _________

None of the mentioned

SHOWPLAN

SHOW SHOWPLAN

SHOW PLAN

Question 2 of 10

Question 3 of 10

3. Point out the wrong statement.

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

None of the mentioned

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

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

Question 3 of 10

Question 4 of 10

4. Which of the following is the biggest disadvantage of change tracking?

All of the mentioned

Schema changes are required in the server database

Logic for maintaining the correct row versions and deletions is easy

Triggers are fired for each change that has been made to a row

Question 4 of 10

Question 5 of 10

5. Which of the following script enable a Table for all and Net Changes Queries?

All of the mentioned

\USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'MyTable',
@role_name     = N'MyRole',
@supports_net_changes = 0
GO

\USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'MyTable',
@role_name     = N'MyRole',
@supports_net_changes = NULL
GO

\USE MyDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'MyTable',
@role_name     = N'MyRole',
@supports_net_changes = 1
GO

Question 5 of 10

Question 6 of 10

6. Which of the following rebuilds the IX_Employee_OrganizationLevel_OrganizationNode index with a fill factor of 80 on the HumanResources.Employee table?

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80);

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources
REBUILD WITH (FILLFACTOR = 80);

All of the mentioned

CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
REBUILD WITH (FILLFACTOR = 80);

Question 6 of 10

Question 7 of 10

7. 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);

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

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

None of the mentioned

Question 7 of 10

Question 8 of 10

8. Which of the following scenario favours cached mode?

All of the mentioned

Less frequent uploads

Data collection and uploading of jobs on different schedules

Continuous collection of data

Question 8 of 10

Question 9 of 10

9. Point out the correct statement.

The function cdc.fn_cdc_get_all_changes_<capture_instance> returns all changes that occurred for the specified interval

A typical application scenario for querying for change data is to periodically request change data by using a receiver window bounded by timestamp values

The update mask that is returned from a query function is a compact representation that identifies few columns that changed in a row of change data

None of the mentioned

Question 9 of 10

Question 10 of 10

10. Which of the following code snippet demonstrates ALTER statement with STATISTICS_NORECOMPUTE in ONLINE state?

ALTER INDEX ALL ON Production.Product
BUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

All of the mentioned

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON,
              STATISTICS_RECOMPUTE = ON);

Question 10 of 10

« Prev - SQL Server Mock Test 7

» Next - SQL Server Mock Test 9