SP, UDF and VIEW

SPs are Databse subroutine used to perform tasks within the database, whether it be to INSERT, UPDATE, DELETE, SELECT, send return values, send output parameters, send e-mail, call command line arguments, encapsulate business logic, enforce data integrity, or any combination thereof.
They are compiled when first run, and the query plans are stored and cached by SQL Server's optimizer, and those cached plans are swapped out depending on frequency of usage.

View:
• To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
• To control access to rows and columns of data.
• To aggregate data for performance.

UDFS:
Set of statements which must return an object considered as function.
In general, UDFs can be a serious source of performance issues. Also, UDFs cannot be used for DML operations (INSERT/UPDATE/DELETE), cannot use non-deterministic functions, cannot use dynamic SQL, and cannot have error-handling (e.g. RAISERROR).
These are of 3 types;
1. Scalar: returns a single value, meant for doing calculations
2. Inline table valued: Returns a table, can be used in joins or in place of result set
3. Multi statement table valued: Returns a defined table.

A simple glance on all of the above:

SP

UDF

VIEW

Query plan is saved in server optimizer

Query plan is saved only for inline table functions

No plan is saved but definition of the view is saved with an exception of indexed view

DML operations can be performed

DML operations are not possible

DML operations are possible with considering certain constraints

We can use REUTURN keyword, and can return a numeric value, a string which must be a numeric.

Eg: return ‘123'

We can return a single object of any kind

We can't return a value

We can use Non-deterministic functions like, getdate()

 

We can't use Non-Deterministic functions.

We can use

Error handling can be implemented

Error handling is not implemented

N/A

Supports Dynamic code

Doesn't supports dynamic Code

N/A

Comments

Popular posts from this blog

SharePoint 2019 New Features

Move the list items from one list to another list using PowerShell - SharePoint 2010

Email Validation for SharePoint list column