Question 251 – What are the Advantages of User Defined Functions?

• UDF can be used in a Select, Where, or Case statement.
• UDF can be used in join T-SQL Statements
• It can act like a table, so the ability to break out complex logic into shorter and shorter code blocks
  Additional benefit of making the code less complex and easier to write and maintain.
• We can pass parameters to customize and gets the return based on our requirement
• Simpler to invoke in sql statements than a stored procedure.

Question 252 – What are the DisAdvantages of User Defined Functions?

• UDF that return non-deterministic values are not allowed to be called from inside UDF
• GETDATE is an example of a non-deterministic function. Every time the function is called, a different value is returned.
• Not every SQL statement or operation is valid within a function.
• The following lists enumerate the valid and invalid function operations:
• Valid:
  Assignment statements
• Control-flow statements
• Variable declarations
• SELECT statements that modify local variables
• Cursor operations that fetch into local variables
• INSERT, UPDATE, DELETE statement that act upon local table variables
• Invalid:
  Built-in, nondeterministic functions such as GetDate()
• Statements that update, insert, or delete tables or views
• Cursor fetch operations that return data to the client

Question 253 – What is the difference between a Stored Procedure and Functions?

Stored Procedure

Functions

Question 254 – What is a Trigger?

• A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE,UPDATE) occurs.
• Triggers can restrict access to specific data, perform logging, or audit data modifications.
• A trigger cannot be called or executed.

Question 255 – What is a DML Trigger?

• Fired when a Data Manipulation Language (DML) event takes place
• AFTER – Executed after the action of the INSERT, UPDATE, or DELETE statement is performed.
• INSTEAD OF – Executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

Question 256 – What is a DDL Trigger?

• DDL triggers are new to SQL Server 2005.
• This type of triggers, like regular triggers, fire stored procedures in response to an event.
• They fire in response to a variety of Data Definition Language (DDL) events.
• These events are specified by the T-SQL statements that are start with the keywords CREATE, ALTER, and DROP.
• Certain stored procedures that perform DDL-like operations can also fire this.
• These are used for administrative tasks like auditing and regulating database operations.

Question 257 – What is a CLR Trigger?

• CLR triggers can be a DDL or DML one or can also be an AFTER or INSTEAD OF trigger.
• Here we need to execute one or more methods written in managed codes that are members of an assembly created in the .Net framework.
• Again, that assembly must be deployed in SQL Server 2005 using CREATE assembly statement.
• The Microsoft.SqlServer.Server Namespace contains the required classes and enumerations for this objective.
• Question 258 – What are the Advantages of Triggers?
• Audit a table for security
• Automatic updating of one or more tables whenever a DML/DDL statement is executed on that table.
• Triggers can be used to enforce constraints. For e.g. : Any insert/update/ Delete statements should not be allowed on a table after office hours. For enforcing this constraint Triggers should be used.
• Used to publish information about database events to subscribers. Events like Database startup or shutdown or a user even like User login in or user logoff.

Question 259 – What are the DisAdvantages of Triggers?

• It is easy to view constraints, indexes, Sp’s in database but triggers are difficult to view.
• Execute invisible to application. They are not visible or can be traced in debugging code.
• Hard to follow their logic as it they can be fired before or after the database insert/update happens.
• Easy to forget about triggers and if there is no documentation it will be difficult to figure out.
• Run every time when the db fields are updated; it is overhead on system, it makes system run slower.

Question 260 – What are Views in SQL Server?

• A view is an “Virtual Table”.
• It does not contain any data directly, it is a set of query that are applied to one or more tables as object.
• It can be thought of as a subset of a table.
• It can be used for retrieving data, as well as updating or deleting rows.
• The results of using a view are not permanently stored in the database.
• A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them.
• A view also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be extracted using simple queries.

Disclaimer – F5debug Interview Questions & Answers Series:

You may recopy extracts from these pages (“the material”) to individual third party websites or to any intranet websites, but only if:

You acknowledge www.f5debug.net as the source of the material. Such acknowledgment should include reference to www.f5debug.net in the copy of the material and should also include “© Karthikeyan Anbarasan, www.f5debug.net “. You inform the third party that these conditions apply to him/her and that he/she must comply with them.