SP, UDF and VIEW
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
Post a Comment