Differences between Stored Procedures and Functions

1. Procedure can return zero or n values whereas function can return one value which is mandatory.
2. Procedures can have input/output parameters for it whereas functions can have only input parameters.
3. Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
4. Functions can be called from procedure whereas procedures cannot be called from function.
5. Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6. We can go for transaction management in procedure whereas we can't go in function.
7. Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
9. UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
10. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
11. Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

How to Retrieve Last Inserted Identity of Record

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

Note:  Always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Stored Procedure in SQL

Stored Procedures

Stored procedure  is the block of SQL statements written together to performs some specific task.  A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.
We can pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters
A procedure may or may not return any value.

Advantage of Stored Procedure

There are multiple advantages to using stored procedures:

1. Stored procedures will generally perform better than the same group of statements executed individually. That is because when stored procedure code is compiled the execution algorithm is stored in the server procedure cache. Therefore, the SQL Server engine does not have to make a decision on how to run a stored procedure each time it is executed.
    
2. Stored procedures can provide a high level of security to SQL Server data. If you write a stored procedure for every Data Modification Language statement (SELECT, INSERT, UPDATE, DELETE), then you can grant the database users access to these stored procedures, without giving permissions to the underlying tables. This way you can tightly control what users can do with your data.
    
3. Stored procedures can accept and return parameters. Each stored procedure can have up to 2100 parameters.
    
4. Stored procedures let you encapsulate business rules within manageable components of code. When the business rule changes, you only have to modify your code in one place.
    
5. Stored procedures can be executed automatically when SQL Server starts up. Alternatively you can invoke stored procedures explicitly. 

Example of Stored Procedure

Create Proc [dbo].[Dinner_SelectEmployee]

as

Select Firstname, Lastname, DateofBirth,Department  from Employee

Use OUTPUT Parameter to Get data from Store Procedure

Create Stored Procedure

USE Northwind

CREATE PROC usp_InserShipper

@CompanyName      VARCHAR(100),

@Phone                  VARCHAR(12),

@ShipperID        INT         OUTPUT

AS

BEGIN

      INSERT INTO Shippers(CompanyName,Phone)

      VALUES(@CompanyName,@Phone)

     

      SET @ShipperID=SCOPE_IDENTITY();

END

Get Output Paramenter by executing stored procedure

DECLARE @ShipperID INT

DECLARE @CompanyName VARCHAR(100)

DECLARE @Phone          VARCHAR(12)

SET @CompanyName= 'My Company'

SET   @Phone=     '(503) 555-9831'

EXEC usp_InserShipper @CompanyName,@Phone,@ShipperID OUTPUT

SELECT @ShipperID  --Get ShipperID using Output parameter

String Functions in SQL

In SQL Server there are several built-in string functions to perform string manipulations a mainly used to change the case of strings, concatenate strings, reverse strings, extract various part of strings. All below functions takes string input value and return a string or numeric value.
ASCII : Returns the ASCII code value for a character
Syntax: ASCII(character)
SELECT ASCII('a') returns 97
SELECT ASCII('A') returns 65


For Upper character 'A' to 'Z' ASCII value 65 to 90
For Lower character 'A' to 'Z' ASCII value 97 to 122
For digit '0' to '9' ASCII value 48 to 57
UNICODE : UNICODE function works just like ASCII function, except returns Unicode standard integer value. UNICODE could be useful if you are working with international character sets.
Syntax: UNICODE(character)
SELECT UNICODE('F') returns 70
SELECT UNICODE('STRING FUNCTION') returns 83 (leftmost character of string)
LOWER : Convert character strings data into lowercase.
Syntax: LOWER(string)
SELECT LOWER('STRING FUNCTION') returns string function
UPPER : Convert character strings data into Uppercase.
Syntax: UPPER(string)
SELECT UPPER('string function') returns STRING FUNCTION
LEN : Returns the length of the character string.
Syntax: LEN(string)
SELECT LEN('STRING FUNCTION')returns15
REPLACE : Replaces all occurrences of the second string(string2) in the first string(string1) with a third string(string3).
Syntax: REPLACE('string1','string2','string3')
SELECT REPLACE('STRING FUNCTION','STRING','SQL') returns SQL Function
Returns NULL if any one of the arguments is NULL.
LEFT : Returns left part of a string with the specified number of characters counting from left.LEFT function is used to retrieve portions of the string.
Syntax: LEFT(string,integer)
SELECT LEFT('STRING FUNCTION', 6) returns STRING
RIGHT : Returns right part of a string with the specified number of characters counting from right.RIGHT function is used to retrieve portions of the string.
Syntax: RIGHT(string,integer)
SELECT RIGHT('STRING FUNCTION', 8) returns FUNCTION
LTRIM : Returns a string after removing leading blanks on Left side.(Remove left side space or blanks)
Syntax: LTRIM(string)
SELECT LTRIM('   STRING FUNCTION') returns STRING FUNCTION
RTRIM : Returns a string after removing leading blanks on Right side.(Remove right side space or blanks)
Syntax: RTRIM( string )
SELECT RTRIM('STRING FUNCTION   ') returns STRING FUNCTION
REVERSE : Returns reverse of a input string.
Syntax: REVERSE(string)
SELECT REVERSE('STRING FUNCTION') returns NOITCNUF GNIRTS
REPLICATE : Repeats a input string for a specified number of times.
Syntax: REPLICATE (string, integer)
SELECT REPLICATE(Repeat, 3) returns Repeat Repeat Repeat
SPACE : Returns a string of repeated spaces.The SPACE function is an equivalent of using REPLICATE function to repeat spaces.
Syntax: SPACE ( integer) (If integer is negative,a null string is returned.)
SELECT ('STRING') + SPACE(1) + ('FUNCTION') returns STRING FUNCTION
SUBSTRING : Returns part of a given string.
SUBSTRING function retrieves a portion of the given string starting at the specified character(startindex) to the number of characters specified(length).
Syntax: SUBSTRING (string,startindex,length)
SELECT SUBSTRING('STRING FUNCTION', 1, 6) returns STRING
SELECT SUBSTRING('STRING FUNCTION', 8, 8) returns FUNCTION
STUFF : Deletes a specified length of characters and inserts another set of characters at a specified starting point.
STUFF function is useful to inserts a set of characters(string2) into a given string(string1) at a given position.
Syntax: STUFF (string1,startindex,length,string2)
SELECT STUFF('STRING FUNCTION', 1, 6, 'SQL') returns SQL FUNCTION
SELECT STUFF('SQL FUNCTION', 5, 8, 'Tutorial') returns SQL Tutorial
CHARINDEX : Returns the starting position of the specified string(string1) in a character string(string2).
Syntax: CHARINDEX (string1,string2 [,start_location ])
SELECT CHARINDEX('SQL','Useful SQL String Function') returns 8
SELECT CHARINDEX('SQL','Useful SQL String Function') returns 19
If string1 is not found within string2,CHARINDEX returns 0.
PATINDEX : PATINDEX function works very similar to CHARINDEX function.PATINDEX function returns the starting position of the first occurrence of a pattern in a specified string, or zeros if the pttern is not found.
Using PATINDEX function you can search pattern in given string using Wildcard characters(%).The % character must come before and after pattern.
Syntax: PATINDEX('%pattern%',string)
SELECT PATINDEX('%SQL%','Useful SQL String Function') returns 8
SELECT PATINDEX('Useful%','Useful SQL String Function') returns 1
SELECT PATINDEX('%Function','Useful SQL String Function') returns 19
If pattern is not found within given string,PATINDEX returns 0.

Function	Parameters		Example			Description
ASCII	(character_experssion)		SELECT ASCII(‘A’)			Return 65, the ASCII code of the leftmost character ‘A’
CHAR	(integer_expression)		SELECT CHAR(65)			Return ‘A’ the character equivalent of the ASCII code value
CHARINDEX	(‘pattern’,expression)		SELECT CHARINDEX(‘E’,HELLO’)			Returns 2, the starting postion of the
DIFFERENCE	(character_expression1, character_expression2)		SELECT DIFFRENCE(‘HELLO’,’hell’)			Return 4. The DIFFRENCE function compares two strings and evaluates the similarity between them, return a value from 0 through 4. The value 4 is the best match.
LEFT	(character_expression, interger_expression)		SELECT LEFT(‘RICHARD’,4)			Return ‘RICH’ which is part of the character string equal in size to the integer_expression characters from the left.
LEN	(character_expression)		SELECT LEN(‘RICHARD’)			Returns ‘7, the number of characters in the character_expression.
LOWER	(character_expression)		SELECT LOWER(‘RICHARD’)			Returns ‘richard’, after converting Character_expression to lower case.
LTRIM	(character_expression)		SELECT LTRIM(‘   RICHARD)			Return ‘RICHARD’ without leading spaces. It  removes leading blanks from the character_expression.
PATINDEX		(‘%pattern%’,expression)		SELECT PATINDEX(‘%BOX%’,’ACTION BOX’)	Return 7, the starting position of the first occurrence of the pattern in the specified expression, or zeros if the pattern is not found.
REVERSE		(character_expression)		SELECT RIGHT(RICHARD’)	Returns ‘DRAHCIR’), the reverse of the character_expression.
RIGHT		(character_expression, integer_expression)		SELECT RIGHT(‘RICHARD’,4)	Returns ‘HARD’ , the part of the character string, extracting from the right the number of character specified in the integer_expression.
RTRIM		(character_expression)		SELECT RTRIM(‘RICHARD  ‘)	Returns ‘RICHARD’, after removing any trailing blanks from the character Expression.
SPACE		(integer_expression,		SELECT ‘RICHARD’+ SPACE(2)+’HILL’	Returns ‘RICHARD HILL’. Two spaces are inserted between the first and second word.
STR		(float_expression, [length,[decimal])		SELECT STR(123.45,6,2)	Returns ‘123.45’. It converts numeric data to character data where the length is the total length, incude the decimal point, the sign, the digits, and the spaces and the decimal is the number of places to the right of the decimal point.
STUFF		(character_expression1, Start,length, character_expression2		SELECT STUFF(‘Weather’,2,2,’i’)	Returns ‘Wither’. It deletes length character from character_expression1 from the start and then inserts char_expression2 into character_expression1 at the start position.
SUBSTRING		(expression,start,length)		SELECT SUBSTRING(‘Wether’,2,2)	Returns ‘ea’ which is part of a character string. It returns length characters from the start position of the expression.
UPPER		(character_expression)		SELECT UPPER(‘Richard’)	Retiurns ‘RICHARD’. It converts lower case character data to upper case.

Retrive records from database

Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL

Syntax of the SELECT statement

SELECT select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

·  Table listing (FROM clause)

·  Filter logic (WHERE clause)

·  Summarize values (GROUP BY)

·  Filters the GROUP BY values (HAVING)

·  Sorting logic (ORDER BY)

A single column can be accessed from a SELECT statement

USE Employee;

GO

SELECT EmpID FROM Employee

GO

Multiple columns can be accessed from a single SELECT statement

USE Employee;

GO

SELECT EmpID, Firstname, Lastname, Address FROM Employee

GO

Add a WHERE clause to limit the records values from the  table only when the WHERE criteria is met

USE Employee;

GO

SELECT EmpID, Firstname, Lastname, Address FROM Employee where EmpID=1005

GO

Add ORDER BY clauses to show how data can be sorted in both ascending and descending. Default is ascending.

USE Employee;

GO

SELECT EmpID, Firstname, Lastname, Address FROM Employee orderby Firstname Desc

GO

Add Group BY clauses to show data in a Group which is used with aggregate function.

USE Employee;

GO

SELECT sum(Salary) FROM Employee Group By Department

GO

Data Modals

Database Management

A database is a collection of logically related information and store information in a proper way.

Database Management is the process of maintaining data in the database so that information is available easily when required. The System used to for Database Management is called Database Management System. Database Management System involves:

1 –Design Database structure to store and maintain a huge amount of data.

2-Provide facilities to retrieving, inserting, updating and deleting records from database.

3- Taking and restoring data.

4- Secure data from unauthorized users.

Organizing the data in the database is described in a Data Model. Mainly there are two types of data models

1-      Object-based logical model, which focuses on describing the data, the relation among the data.

2-      Record-based logical model, which focuses on describing the data structure and the access techniques in a database management system.

An Entity is any object, place, person, or activity about which data is recorded. For example Teacher and Student etc.

Relationship is an association among entities. For example there is the relation between Teacher and Student as Teacher Teaches Student.

There are three type of relationships

1-One to One Relationship

Example: For a particular department (like Chemistry, Physics) there is only one Department Head.

3-      One to Many Relationship

Example: One department can contain many teachers to teach same subject.

Example: One student can select many Courses and One Course can be selected by many students.

Normalization of database

Normalization is a method through which we break down complex table structures into simple table structure. Using this method we can reduce redundancy in a table and eliminate the inconsistency and reduce  the disk space without loss of information.

Normalization has several advantages like it makes sorting faster enables index creation, more cluster indexes, few indexes per tables, few NULLs and make the database compact.

Normalization helps to simplify the table structure of tables. The performance of an application is directly linked to the database design. If database design is not proper then it slow down the process of application.

Some rules that should be followed to achieve a good database design are:

1 -Each table should have an identifier.

2-Each table should store data for a single type of entity.

3-Columns that accept NULLs should be avoid.

4-The repetition of values of columns should be avoided.

Normalization results in the formation of tables that satisfy certain specified rules and represent certain normal forms. The normal forms are used to ensure the various types of anomalies and inconsistencies are not introduced in the database. A table structure is always in a certain normal form. Several normal forms have been identified. The most important and widely used normal forms are:

1-First Normal Form (1NF)

2-Second Normal Form (2NF)

3 –Third Normal Form (3NF)

4-                4-Boyce-Codd Normal Form (BCNF)

The first, second and third normal forms were originally defined by Dr. E.F. Codd. Later, Boyce and Codd introduced another normal form called the Boyce-Codd Normal Form.

Functional Dependency

The normalization theory is based on the fundamental notion of functional dependency. First, let’s examine the concept of functional dependency.

In a given relation R, attribute A is functionally dependent on attribute B if each value of A in R is associated with precisely on value of B.

In other words, attribute A is functionally dependent on B if and only if, for each value of B, there is exactly one value of A.

Attribute B is called the determinant. Consider the following table Employee:

Code	Name	City
E1	John	Texas
E2	Sally	California
E3	Mick	Florida

Here, for Code E1 there is exactly one value of Name, John. Hence Name is functionally dependent on Code. Similarly, there is one value of City for each value of Code. Hence, the attribute City is functionally dependent on the attribute Code.  The attribute Code is the determinant. It means that Code determines City and Name.

First Normal Form (1 NF)

A table is  said to be in the 1 NF when each cell of the table contains precisely one value.

Consider the following table Project.

Ecode	Dept	DeptHead	ProjCode	Hours
E101	Systems	E901	P27 P51 P20	90 101 60
E305	Sales	E906	P27 P22	109 98
E508	Admin	E908	P51 P27	Null 72

The data in the table is not normalized because the cells in ProjCode and Hours have more than one value.

By applying the 1NF definition to the Project table, you arrive at the following table:

Ecode	Dept	DeptHead	ProjCode	Hours
E101	Systems	E901	P27	90
E101	Systems	E901	P51	101
E101	Systems	E901	P20	60
E305	Sales	E906	P27	109
E305	Sales	E906	P22	98
E308	Admin	E908	P51	NULL

Second Normal Form (2 NF)

A table is said to be in 2 NF when it is in 1 NF and every attribute in the row in functionally dependent upon the whole key, and not just part of the key. Consider the table Project

Ecode	ProjCode	Dept	DeptHead	Hours
E101	P27	Systems	E901	90
E305	P27	Finance	E909	10
E508	P51	Admin	E908	NULL
E101	P51	Systems	E901	101
E101	P20	Systems	E901	60
E508	P27	Admin	E908	72

This situation could lead to the following problems:

A-     Insertion

The department of a particular employee cannot be recorded until the employee is assigned a project.

B-     Updation

For a given employee, the employee code, department name, and department head are repeated several times. Hence, if an employee is transferred to another department, this change will have to be recorded in every row of the table pertaining to that employee. Any omission will lead to inconsistencies.

C-Deletion

When an employee completes work on a project, the employee’s record is deleted. The information regarding the department to which the employee belongs will also be lost.

The primary key here is composite (Ecode + ProjCode).

Convert a table to 2NF

Find and remove attributes that are functionally dependent on only a part of the key and not on the whole key. Place them in a different table.

Combine the remaining attributes.

To convert the table Project  into 2NF, you must remove the attributes that are not functionally dependent on the whole key and place them in a different table along with the attribute that it is functionally dependent on. In above example, since Dept is not functionally dependent on the whole key ECode+ProjCode, you place Dept along with ECode in a separate table called EmployeeDept. We also move the DeptHead to the EmployeeDept table

Following are the tables

EmployeeDept

ECode	Dept	DeptHead
E101	Systems	E901
E305	Finance	E909
E508	Admin	E908

Project

ECode	ProjCode	Hours
E101	P27	90
E101	P51	101
E101	P20	60
E305	P27	10
E508	P51	NULL
E508	P27	72

Third Normal Form (3 NF)

A table is said to be in 3NF when it is in 2 NF and every non-key attribute is functionally dependent only on the primary key.

Consider the table EmployeeDept

ECode	Dept	DeptHead
E101	Systems	E901
E305	Finance	E909
E402	Sales	E906
E508	Admin	E908
E607	Finance	E909
E608	Finance	E909

Problems in data manipulation above tables are:

A-     Insertion

The department head of a new department that does not have any employees at present cannot be entered in the DeptHead column. This is because the primary key is unknown.

B-     Updation

For a given department, the code for a particular department head (DeptHead) is repeated several times. Hence, if a department head moves to another department, the change will have to be made consistently across the table.

C-Deletion

If the record of an employee is deleted, the information regarding the head of the department will also be deleted. Hence,  there will be a loss of information.

Convert a table to 3NF

Find and remove non-key attributes that are functionally dependent on attributes that are not the primary key. Place them in a different table.

Group the remaining attributes.

To convert the table EmployeeDept into 3NF, you must remove the column DeptHead since it is not functionally dependent on only the primary key ECode, and place it in another table called Department along with the attribute Dept that it is functionally dependent on.

EmployeeDept                                               Department

Ecode	Dept
E101	Systems
E305	Finance
E402	Sales
E508	Admin
E607	Finance
E608	Finance

Dept	DeptHead
Systems	E901
Sales	E906
Admin	E908
Finance	E909