Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 56 discussion

Actual exam question from Microsoft's 70-762

Question #: 56
Topic #: 1

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have a database that contains a table named Employees. The table stored information about the employees of your company.
You need to implement the following auditing rules for the Employees table:
- Record any changes that are made to the data in the Employees table.
- Customize the data that is recorded by the audit operations.
Solution: You implement a user-defined function on the Employees table.
Does the solution meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: A 🗳️
SQL Server 2016 provides two features that track changes to data in a database: change data capture and change tracking. These features enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database.
Change data is made available to change data capture consumers through table-valued functions (TVFs).
References:
https://msdn.microsoft.com/en-us/library/cc645858.aspx

by cyen at Dec. 30, 2019, 3:42 a.m.

Comments

Submit Cancel

cyen

Highly Voted 5 years, 6 months ago

wrong answer - should use trigger

upvoted 22 times

...

TheDUdeu

Most Recent 4 years, 5 months ago

Triggers would be easier and udf can't change table data.

upvoted 2 times

vrab

4 years, 5 months ago

you need to Record any changes not to change table data

upvoted 1 times

...

SoupDJ

4 years, 8 months ago

The question specifically mentions a UDF on the EMPLOYEE table - however change capture encompasses the change tables and the change queries created when Change Capture is created. If you were to implement a UDF, you would be using those generated queries or the change tables - not the EMPLOYEE table itself.

upvoted 1 times

...

Cococo

4 years, 9 months ago

Answer is correct, it is cdc function, have a look at the link it is all about UDF and their types, 3 types - Scalar, Table-Valued and System. https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/user-defined-functions?view=sql-server-ver15

upvoted 2 times

...

melvin9900

4 years, 11 months ago

Answer may be correct . https://docs.microsoft.com/en-us/sql/integration-services/change-data-capture/create-the-function-to-retrieve-the-change-data?view=sql-server-ver15

upvoted 1 times

...

JohnFan

5 years, 3 months ago

Table-Valued user-defined functions Table-Valued UDFs are used to present a set of data as a table, much like a view. In fact, they are generally thought of as views with parameters (or parameterized views.) There are two kinds of table-valued UDFs: Simple Consisting of a single Transact-SQL query, simple table-valued UDFs work very much like a VIEW. Multi-Statement Consists of as many statements as you need, allowing you to build a set of data using the same logic as you had in scalar UDFs, but returning a table instead of a scalar variable.

upvoted 1 times

...

JohnFan

5 years, 3 months ago

there are two types of objects we deal with: triggers and User-Defined Functions (UDFs). TRIGGER objects are used to react to certain types of actions, such as a DML operation like an INSERT, a DDL operation like ALTER INDEX, CREATE TABLE, etc., or a user logging into the server. The most common use of triggers is to use a DML trigger to extend data integrity where constraints are not sufficient. UDFs allow you to create modules of code that can be used as the building blocks of SQL statements. While Transact-SQL is not generally great at producing very modular code, UDFs allow you to build code that works much like SQL Server’s system functions where it makes sense.

upvoted 2 times

...

New_user

5 years, 5 months ago

Trigger can be used for this task, but this fact doesn't deny given answer. There is a function cdc.fn_cdc_get_all_changes_. It returns a table containing all changes to custom table. So, answer is right

upvoted 3 times

Bartek

5 years, 4 months ago

It depends, but in that case I think Your wrong. "Solution: You implement a user-defined function on the Employees table." "cdc.fn_cdc_get_all_changes_" is not a user defined function, but just function build by procedure "EXEC sys.sp_cdc_enable_table " ref. https://www.mssqltips.com/sqlservertip/5212/sql-server-temporal-tables-vs-change-data-capture-vs-change-tracking--part-2/

upvoted 10 times

JohnFan

5 years, 3 months ago

you use a table-valued wrapper function because this kind of function explicitly defines the columns of its output table in its RETURNS clause. This explicit definition of columns provides the metadata that an Integration Services source component needs. You have to create this function for each table for which you want to retrieve change data. https://docs.microsoft.com/en-us/sql/integration-services/change-data-capture/create-the-function-to-retrieve-the-change-data?view=sql-server-ver15

upvoted 1 times

...

raja1234567890

5 years ago

Firstly, it talks about recording change. This implies you need access to inserted and deleted tables. Secondly, you need to modify certain data when you insert data from deleted table to certain audit table. So trigger will be appropriate.

upvoted 1 times

...

maartynaa

5 years ago

I agree, answer should be No

upvoted 2 times

...

amar111

5 years, 1 month ago

The Answer should be NO . Reason : 1. in question its mentioned "User Defined Function" and cdc.fn_cdc_get_all_changes is not a UDF. 2. cdc_get_all_changes is part of process - you have enable change tracking at database level then enable change tracking at table level . after doing this cdc_get_all_changes gets automatically created .

upvoted 1 times

Cococo

4 years, 9 months ago

3 types of UDF - Scalar, Table-Valued and System https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/user-defined-functions?view=sql-server-ver15

upvoted 1 times

...

neko

5 years, 5 months ago

I agree with cyen. The best practice is using DML triggers to audit data changes although UDFs can UPDATE, INSERT, and DELETE statements modifying table variables that are local to it, it is not normal to use UDFs to modifying data. cyen, please add more explanation next time so that we can help studying for each other.

upvoted 1 times

...