Home > DeveloperSection > Blogs > User Define function in Sql Server

User Define function in Sql Server


Database SQL Server  SQL 
Ratings:
0 Comment(s)
 176  View(s)
Rate this:

User Define function in Sql Server 


User Defined Functions play a major role in SQL. User Defined functions can be used to perform a complicated logic, can accept parameters and return data. Many a times we have to write complex logic which cannot be written using a single query. In such case, UDFs role is important. For example, we can call user defined function in a where clause or use a user defined function in a JOIN.

Thease function are three types:


1.   Scalar function :

This function return a single value as a result of action perform by function and we return a any data type.

 

For example :

 

First we create table

 

create table Product (ProductId int primary key identity(1,1),ProductName varchar(200),Price nvarchar(25))

and after that create a function to get username

    

       create function fnGetProductName

      (

 

          @ProductName nvarchar(50)

      )

          returns varchar(50)

          as

          begin

          return

         (select @ProductName);

          end

 

Calling the above created function

 

select dbo. fnGetProductName (ProductName)as ProductName from product

 

2.   Inline table-valued function :

 

This function return a table value as a result of action perfrom by function

 

For example :


create function fnGetProduct()

returns table

as

return select * from Product

 

    Calling the above created function

 

select * from dbo.fnGetProduct()

 

3.   Multi Statement Table-Valued Function :

 

This user define function return a table value as a result of action perform by function and in this table variable must be delcare explicitly and define whose value can be derived from multiple sql statements

 

For example :

 

-- create the function for get Id,ProductCode and Price

create function fnMulProduct()

returns @Product2 table

(

Id int,

ProductCode nvarchar(50),

Price nvarchar(50)

)

as

begin

-- now update price of id=3

update @Product2 set Price=25 where Id=3

--it will update only in @produce table not in orginal table

return

end

 

 -- calling the above function function

Select * from fnMulProduct()

 

Index: Index is a database object. It can be created in a table to find data more quickly and efficiently that is call as Index.


Cluster Index: A cluster index determines physical order of the database, a table can have only one cluster index.

Ex- create Clustered index in_product_productname on product(productname desc)


Non Cluster Index: It is useful for columns that have repeated values.

Ex- CREATE NONCLUSTERED INDEX [IX_NONCLUSTERED_productname1]

ON product(productname desc)

 


Don't want to miss updates? Please click the below button!

Follow MindStick