MS SQL Server Functions

Explain Microsoft SQL Server functions?

What are the benefits of User-Defined Functions?

Explain the difference between Functions and Stored procedures in SQL Server?

a. Can be used in a number of places without restrictions as compared to stored procedures.

b. Code can be made less complex and easier to write.

c. Parameters can be passed to the function.

d. They can be used to create joins and also be sued in a select, where or case statement.

e. Simpler to invoke.

User defined functions are created and defined by the user. They are created as per users needs. They may or may not accept parameters. They can be used to create joins and simple to invoke as compared to stored procedures

Types:

Scalar user defined: returns values as one of the scalar data types. Text, timestamp, image data types are not supported. It may or may not accept parameters.

Inline table value user defined : it returns a table data type. These functions can pass parameters to the sql’s SELECT command. They are similar to views except, they can accept parameters.

A built in function of sql is used for performing calculations. These are standard functions provided by sql.

Aggregate functions: these functions perform calculation on a column and return a single value. Example: AVG(), SUM(), MIN(), MAX()

Scalar functions : these functions perform calculation on an input value and return a single value. Example: ROUND(), MID(), LCASE(), UCASE()

First Normal form

Entities of the table must have unique identifier or entity key.

Second Normal Form

All the attributes of the table must depend on the entity key for that entity.

Third Normal Form

All attributes that are not part of the key must not depend on any other non-key attributes.

It is the process of organizing data into related table.

To normalize database, we divide database into tables and establish relationships between the tables.

It reduces redundancy. It is done to improve performance of query.

Normalization is the process of reducing data redundancy and maintains data integrity. This is performed by creating relationships among tables through primary and foreign keys. Normalization procedure includes 1NF, 2NF, 3NF, BCNF, and then the data is normalized.

Denomalization on the contrary is the process of adding redundant data to speed up complex queries involving multiple table JOINS. One might just go to a lower form of Normalization to achieve Denormalization and better performance. Data is included in one table from another in order to eliminate the second table which reduces the number of JOINS in a query and thus achieves performance.

The process of adding redundant data to get rid of complex join, in order to optimize database performance. This is done to speed up database access by moving from higher to lower form of normalization.