SQL Server Questions and Answers – Change Tracking

This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Change Tracking”.

1. Which of the following is a change tracking function?
a) WITH CHANGE_TRACK_CONTEXT
b) CHANGE_TRACKING_MAX_VALID_VERSION()
c) CHANGE_TRACKING_CURRENT_VERSION
d) All of the mentioned
View Answer

Answer: c
Explanation: You can use this version the next time you enumerate changes by using CHANGETABLE.

2. Point out the correct statement.
a) A good example of one-way synchronization application is an occasionally connected application
b) Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications
c) After change tracking is configured for a table, any DDL statement that affects rows in the table will cause change tracking
d) None of the mentioned
View Answer

Answer: b
Explanation: Change tracking is used to enable applications to query for changes to data in a database and access information that is related to the changes, application developers had to implement custom change tracking mechanisms.

3. Which of the following is the biggest disadvantage of change tracking?
a) Schema changes are required in the server database
b) Triggers are fired for each change that has been made to a row
c) Logic for maintaining the correct row versions and deletions is easy
d) All of the mentioned
View Answer

Answer: b
Explanation: Triggers for each change has performance implications.

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

advertisement
advertisement
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

b)

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

c)

advertisement
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

d) None of the mentioned
View Answer

Answer: c
Explanation: After change tracking is enabled, Sync Framework applications use change tracking functions and anchors to determine which inserts, updates, and deletes to download. An anchor is just a point in time that is used to define a set of changes to synchronize.

advertisement

5. Point out the wrong statement.
a) Change tracking is used for one way synchronization applications only
b) Applications that have to synchronize data with an instance of the SQL Server Database Engine must be able to query for changes
c) Two-way synchronization applications can also be built that use change tracking
d) The two-way synchronization applications must be able to detect conflicts
View Answer

Answer: a
Explanation: Change tracking is used for one way and two way synchronization applications.

6. Which of the following code snippet returns a version integer that is associated with the last committed transaction that was tracked by change tracking?
a) SELECT @sync_new_received_anchor = change_tracking_cur_version()
b) SELECT @sync_new_received_anchor = change_tracking_current_ver()
c) SELECT @sync_new_received_anchor = change_tracking_current_version()
d) SELECT @sync_new_received_anchor = change_track_current_version()
View Answer

Answer: c
Explanation: The integer value is stored in the client database and is used by the commands that synchronize changes.

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

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

b)

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

c)

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

d) All of the mentioned
View Answer

Answer: a
Explanation: To track which client made a data change that is applied at the server, use the WITH CHANGE_TRACKING_CONTEXT clause.

8. _____________ obtains tracking information for all changes to a table that have occurred since a specific version.
a) CHANGETABLE_CONTEXT
b) CHANGETABLE
c) CHANGE_TRACKING_IS_COLUMN_IN_MASK
d) All of the mentioned
View Answer

Answer: b
Explanation: CHANGETABLE obtains latest change tracking information for a specified row.

9. Which of the following is a catalog view used for change tracking?
a) sys.change_tracking_tables
b) sys.change_tracking_views
c) sys.change_tracking_columns
d) none of the mentioned
View Answer

Answer: a
Explanation: sys.change_tracking_tables returns one row for each database in the instance of SQL Server that has change tracking enabled.

10. Which of the following feature distinguishes change data capture from change tracking?
a) Historical data
b) DML
c) DML type
d) Whether a column was changed
View Answer

Answer: a
Explanation: Change tracking cannot track historical data.

Sanfoundry Global Education & Learning Series – SQL Server.

To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.