User Defined Functions

Overview:

Functions are the database objects, it contains the set of T-SQL transactions and has set of the rule that differs from stored procedure, Will discuss the difference between functions and stored procedures in next post. There are three types of functions namely Scalar Functions, Inline Table-Valued Functions and Multi-Statement Table-Valued Functions.

Scalar Functions: This function type returns a single value of data type specified in the RETURNS clause. The returned values are of any type except text, ntext, image, cursor, or timestamp.

Example:

CREATE FUNCTION fn_ScalarExample(@Val INT)

RETURNS INT

AS

BEGIN

      SET @Val = @Val*2;

      RETURN @Val;

END

GO

Usage   :

                SELECT dbo.fn_ScalarExample(12)

Result   :

                24

Inline Table-Valued Functions:  In this type there is no transactions & returns a variable of data type table whose value is derived from a single SELECT statement. SELECT scripts are specified immediate to RETURN

Example:

CREATE FUNCTION fn_InlineExample(@Val INT)

RETURNS TABLE

AS

      RETURN (SELECT (@Val*2) AS Val);

GO

Usage   :

                SELECT * FROM dbo.fn_InlineExample(12)

Result   :

                24

Multi-Statement Table-Valued Functions: In this we have to define the structure of table in RETURNS section and through the set of transaction data has to be loaded into the table.

Example:

CREATE FUNCTION fn_MultiStatementExample(@Val INT)

RETURNS @Test TABLE( Val INT)

AS

BEGIN

      INSERT @Test

      SELECT  @Val*2

      RETURN

END

GO

Usage   :

                SELECT * FROM dbo.fn_MultiStatementExample(12)

Result   :

                24

Dynamic File Up loader

Overview:

                Easy tool to upload flat or .CSV files to the database table.  Very useful for small scale projects and does data dump quickly.

Query:

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id=OBJECT_ID(N'usp_Uploader'))

DROP PROCEDURE usp_Uploader

GO

SET   NOCOUNT     OFF;

GO

CREATE PROCEDURE usp_Uploader(

                                                @tableName nVARCHAR(20),

                                                @filePath nVARCHAR(50),

                                                @rowDelimiter VARCHAR(5),

                                                @fieldDelimiter VARCHAR(5),

                                                @startFrom VARCHAR(4),

                                                @errorMessage nVARCHAR(200) OUTPUT

                                          )

AS

BEGIN

      BEGIN TRANSACTION

      BEGIN TRY

            DECLARE     @query VARCHAR(max);

            SET         @query            =     'BULK INSERT '+@tableName+'

                                                FROM '+@filePath+'

                                                WITH

                                                (

                                                FIRSTROW ='+@startFrom+',

                                                FIELDTERMINATOR ='+@fieldDelimiter+',

                                                ROWTERMINATOR = '+@rowDelimiter+'

                                                )'                                 

            EXECUTE(@Query);

            COMMIT TRANSACTION

      END TRY

      BEGIN CATCH

            ROLLBACK TRANSACTION

            SET   @errorMessage = ERROR_MESSAGE()

      END CATCH

END;

GO

Usage:

DECLARE     @Error nVARCHAR(200);

EXEC usp_Uploader 'test','''d:\Source.txt''','''\n''','''\t''',100,@Error OUTPUT;

PRINT @Error;     

Dynamic PIOVTing

Overview:

                Pivot gives you the options to make rows data in to columns and this example gives you the way to create dynamic pivoting. You can see in the query that locations are assigned to a variable and the same has been used in the PIVOT query. So, rows data that i.e AUS, UAE, UK and USA are taken into the variable.

 

Base table:

Query:

            DECLARE     @Query      VARCHAR(200);

DECLARE     @Columns    VARCHAR(200);

SELECT      @Columns=COALESCE(@Columns+',','')+'['+a.Location+']'

FROM        (     SELECT DISTINCT Location FROM DynamicPivot)a

SET         @Query =    'SELECT     Name,'+@Columns+'

                        FROM  (     SELECT      Name, Location, Quantity

                                    FROM  DynamicPivot

                              ) a

                        PIVOT(SUM(Quantity) FOR Location IN ('+@Columns+')) pvt'

EXEC(@Query);

Result:

 

Normal Forms (1 to 3)

Overview:

                The normal forms defined in relational database theory represent a set of guidelines for record design.

1^st Normal Form:

                This is the basic and simplest normal form, the aim of this is to eliminate duplicate columns from the table and create separate tables for each group of related data and identify each row with a unique column (the primary key).

Employee (Not in 1NF)
EMP_ID	EMP_Name	Dept_ID	Dept_Name	Skills
1	Ramesh	201	IT	C,C#,SQL
2	Suresh	225	Systems	32Bit, 63 Bit
3	Ganesh	225	Systems	32Bit

Employee (In 1NF)
EMP_ID	EMP_Name	Dept_ID	Dept_Name	Skills
1	Ramesh	201	IT	C
1	Ramesh	201	IT	C#
1	Ramesh	201	IT	SQL
2	Suresh	225	Systems	32Bit
2	Suresh	225	Systems	64bit
3	Ganesh	225	Systems	32Bit

2^nd Normal Form:

Each attribute must be functionally dependent on the primary key. Functional dependence - the property of one or more attributes that uniquely determines the value of other attributes. Any non-dependent attributes are moved into a smaller (subset) table. Prevents update, insert, and delete anomalies

Note: Skills is not functionally dependent on EMP_ID since it is not unique to each EMP_ID.

Employee (In 1NF)
EMP_ID	EMP_Name	Dept_ID	Dept_Name	Skills
1	Ramesh	201	IT	C
1	Ramesh	201	IT	C#
1	Ramesh	201	IT	SQL
2	Suresh	225	Systems	32Bit
2	Suresh	225	Systems	64bit
3	Ganesh	225	Systems	32Bit

Employee (In 2NF)					Skills (In 2NF)
EMP_ID	EMP_Name	Dept_ID	Dept_Name		EMP_ID	Skills
1	Ramesh	201	IT		1	C
2	Suresh	225	Systems		1	C#
3	Ganesh	225	Systems		1	SQL
					2	32Bit
					2	64bit
					3	32Bit

3^rd Normal Form:

Remove transitive dependencies. Transitive dependence - two separate entities exist within one table. Any transitive dependencies are moved into a smaller (subset) table. Prevents update, insert, and delete anomalies.

Employee (In 2NF)
EMP_ID	EMP_Name	Dept_ID	Dept_Name
1	Ramesh	201	IT
2	Suresh	225	Systems
3	Ganesh	225	Systems

Employee (In 3NF)				Department (in 3NF)
EMP_ID	EMP_Name	Dept_ID		Dept_ID	Dept_Name
1	Ramesh	201		201	IT
2	Suresh	225		225	Systems
3	Ganesh	225