Download Microsoft.70-761.PracticeTest.2019-02-03.111q.vcex

You can create a database object inside an instance of SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the rich programming model provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types.

ISNULL Syntax: ISNULL ( check_expression , replacement_value ) author:"Luxemburg, Rosa"The ISNULL function replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression. References: https://msdn.microsoft.com/en-us/library/ms184325.aspx

The OUTPUT Clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view. Note: If the column modified by the .RITE clause is referenced in an OUTPUT clause, the complete value of the column, either the before image in deleted.column_name or the after image in inserted.column_name, is returned to the specified column in the tablevariable.Incorrect Answers:C: The deleted.Creditlimit should be inserted in the second column, the OldCreditLimit column, not the third column.References: https://msdn.microsoft.com/en-us/library/ms177564.aspx

Using Cross Joins A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. However, if a WHERE clause is added, the cross join behaves as an inner join. B: You can use the IIF in the ON-statement.IIF returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server. References:https://technet.microsoft.com/en-us/library/ms190690(v=sql.105).aspxhttps://msdn.microsoft.com/en-us/library/hh213574.aspx

The datetime datetype defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock. The DATEFROMPARTS function returns a date value for the specified year, month, and day. Incorrect Answers:A: ValidFrom should be less (<) than @sdate AND ValidTo should be greater (>) than @edate.B: We should add a day with DATEADD, not subtract one day.C: We cannot compare a date to an exact datetime.References: https://msdn.microsoft.com/en-us/library/ms187819.aspx

The ISNULL function replaces NULL with the specified replacement value. References: https://msdn.microsoft.com/en-us/library/ms184325.aspx

Using an Indexed View would allow you to keep your base data in properly normalized tables and maintain data-integrity while giving you the denormalized "view" of that data. References: http://stackoverflow.com/questions/4789091/updating-redundant-denormalized-data-automatically-in-sql-server

User-defined functions that return a table data type can be powerful alternatives to views. These functions are referred to as table-valued functions. A table-valued user-defined function can be used where table or view expressions are allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views. A table-valued user-defined function can also replace stored procedures that return a single result set. References: https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx

A TRY_CONVERT function returns a value cast to the specified data type if the cast succeeds; otherwise, returns null. References: https://msdn.microsoft.com/en-us/library/hh230993.aspx