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:

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)