Split Function - User Defined function in SQL SERVER with multiple split conditions

     In this article we are going to see a split function in SQL SERVER. How the split function works. The first parameter for the function takes the word and second parameter takes the group of condition based on words has to be split  “.,@#$ %^” etc.

For Example: Rajesh,…Is  a,C##Developer

Output:

Rajesh

Is

A

C#

Developer

Let we see the function Now,

CREATE FUNCTION [DBO].[SPLIT]

(

@DATA NVARCHAR(MAX),

@SPLITCONDITION NVARCHAR(30)

)

RETURNS @VALUE TABLE(TEXT NVARCHAR(MAX))

AS

BEGIN

DECLARE @CONLEN INT = LEN (@SPLITCONDITION)

      IF @CONLEN < 1

      BEGIN

            INSERTINTO @VALUE(TEXT) SELECT @DATA

            RETURN

      END

      ELSE

      BEGIN       

            DECLARE@LEN  INT= LEN(@DATA)      

            DECLARE@LOOP INT = 0

            DECLARE@TEMPDATA NVARCHAR(MAX)

            DECLARE@CHAR CHAR(1)

            SELECT@TEMPDATA = ''

                       

            WHILE(@LOOP <= @LEN)

            BEGIN

                  SELECT@CHAR = SUBSTRING(@DATA,@LOOP,1)

                 

                  IFCHARINDEX(@CHAR,@SPLITCONDITION) > 0

                  BEGIN

                  IF  @TEMPDATA <>''

                   BEGIN

                        INSERTINTO @VALUE(TEXT) SELECT @TEMPDATA

                        SELECT@TEMPDATA = ''

                    END

                  END

                  ELSE 

                  BEGIN 

                        SELECT@TEMPDATA = @TEMPDATA +@CHAR

                  END

                  SELECT@LOOP = @LOOP+1

            END

                  IF@TEMPDATA <>''

                  INSERTINTO @VALUE(TEXT) SELECT @TEMPDATA                   

            RETURN

      END

      RETURN

END

How to call the Split function ?

SELECT TEXT FROM DBO.SPLIT('C#@IS,A,.OBJECT ORIENTED$LANGUAGE.,','.,$@ &')

Output :

	TEXT
1	C#
2	IS
3	A
4	OBJECT
5	ORIENTED
6	LANGUAGE

From this article we can see how the user defined split function is created in SQL server and executed.

SQL SERVER - PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, COMPOSITE KEY, CANDIDATE KEY AND ALTERNATE KEY

Keys are used to find the Row with identity and sort the data .They are so many keys present in SQLSERVER

PRIMARY KEY 
     Keys which are used to find the row with unique identity is known as Primary Key, Primary key is also a Unique Key but doesn't allow the NULL Value, In the below table EmployeeId is consider as primary key

EmployeeId	EmployeeName
E1	Rajesh
E2	Rajesh

Create Table Employee
(
 EmployeeId varchar(3) primary key,
 EmployeeName varchar(40)
)

UNIQUE KEY
        Key which is used to find the uniquely identify the rows of a table is known as Unique Key,Unique Key can allow one NULL value. It doesn't allow duplicate values.

FOREIGN KEY
       Key which is used as Reference key for another table and also behaves primary key of the table is known as Foreign Key.In the Below table EmpId is a Primary key for the first table and also it is reference as foreign key in the second table.

EmpId	Name	Department
1	Rajesh	Computer
2	Suresh	Electronics
3	Praba	Civil

Id	EmpId	PhoneNumber
1	1	3452342
2	1	1234563
3	1	2345612
4	2	2398521
5	2	2323177
6	2	5302994
7	3	3958218

COMPOSITE KEY
    Composite Key is a combination of more than one columns of a table. It can be a Candidate key, Primary key

CANDIDATE KEY
     Any number of columns that are uniquely identify the row in a table is known as Candidate key, Any Candidate key be a Primary Key,But we can select one of them as Primary key.

EmpMailId	EmployeeId	EmployeeName
E1@gma.com	E1	Rajesh
E2@gma.com	E2	Suresh

      In the above table,EmpMailId and EmployeeId are Candidate Keys But EmployeeId is consider as Primary Key.

ALTERNATE KEY
              Key which can choose as Primary key,But it is a Candidate Key other than primary key for Ex Above Diagram have the EmpMailId is a Alternate Key which can be choose as Primary,but it is a Candidate key not a Primary Key

In this article i explained the concepts of Keys, I hope this will give some understanding about the concepts of various keys in Sql Server.

SQL SERVER - NORMALIZATION With Examples

What is Normalization ?
  Normalization is a process of eliminating Redundant data and storing the related information in a table.


1. Eliminating Redundant data.
2. Faster update
3. Improve performance
4. Performance in indexes

Let we see different Normalization forms

1. First Normal Form (1NF)
    If a Table is said to be 1NF then it should satisfy following rules.

• Each cell must have one value
• Eliminating Duplicate Columns
• Create a separate table for group of related data and each row must be identify by primary key.

That means each cell must have single value and each row should be uniquely identified by Primary key

For Example :

Name	Department	Phone Number
Rajesh	Computer	3452342,1234563,2345612
Suresh	Electronics	2398521,2323177,5302994
Praba	Civil	3958218

In the above we can see the duplicate columns phone numbers have more than one value , we have to eliminate that and create a group of related data with Unique row identification by specifying a primary key for the table

Rule 1. By applying above rule each cell must have one value above table changes like below

Name	Department	Phone Number	Phone Number	Phone Number
Rajesh	Computer	3452342	1234563	2345612
Suresh	Electronics	2398521	2323177	5302994
Praba	Civil	3958218

Rule 2 & 3 . By applying second rule and third rule no more duplicate columns and each row must be unique is applied     to above table.

Id	Name	Department	Phone Number
1	Rajesh	Computer	3452342
2	Rajesh	Computer	1234563
3	Rajesh	Computer	2345612
4	Suresh	Electronics	2398521
5	Suresh	Electronics	2323177
6	Suresh	Electronics	5302994
7	Praba	Civil	3958218

2. Second Normal Form (2NF)

    The Table must be in second normal form , Then it should satisfy the following rules.

•  It should satisfy first normal form
•  Separate the particular columns ,values are duplicated in each row  should be place in separate table
•  Create the relationship between the tables

From the above table we can see the column name and department are repeated in each row ,This two columns can be maintained in another table and make a relationship between these two tables 

EmpId	Name	Department
1	Rajesh	Computer
2	Suresh	Electronics
3	Praba	Civil

Id	EmpId	PhoneNumber
1	1	3452342
2	1	1234563
3	1	2345612
4	2	2398521
5	2	2323177
6	2	5302994
7	3	3958218

In the above table Empid is played as Primary key for the first table and foreign key for the second table.

3. Third Normal Form (3NF)

     The table must be in 3NF,if it is satisfying the following rules

•  Must be in 2NF
•  Separate the columns that are not dependent upon the primary key of the table.

Product	Price	Tax
LED	23000	20%
AC	15000	10%
Fridge	12000	15%

From the above table you can see that Tax Column is not dependent on Product Primary key column, It is dependent on Price so we separate that in to two different table.

Product	Price
LED	23000
AC	15000
Fridge	12000

Price	Tax
23000	20%
15000	10%
12000	15%

4. Fourth Normal Form (4NF)

• It should be in 3NF
• The non key columns should be dependent on full primary key instead of partial key , If then separate it.

From the following table "EmployeeName" Non-Key column not dependent on full primary key "ManagerId,EmployeeId,TaskID" it depends upon the EmployeeId  Partial Key so it can be separated.

ManagerId	EmployeeId	TaskID	EmployeeName
M1	E1	T1	Rajesh
M2	E1	T1	Rajesh

ManagerId	EmployeeId	TaskID
M1	E1	T1
M2	E1	T1

EmployeeId	EmployeeName
E1	Rajesh

That's it from this article we can see the normalization and there concepts Fully.