OVER() function(clause) in T-SQL

In T-SQL as everyone knows about OVER() function that is used with ranking functions. It has two options Partition By and Order By. Partition By is optional while Order By Mandatory for ranking functions.

If we think outside of ranking function than we can also use OVER() function with Aggregate functions, Next Value for (used with sequence) and Analytic functions.

OVER() function generates output according to conditions. We can reuse its values using CTEs or we can also insert this values inside the tables.

Some examples:

SELECT SalesOrderID, ProductID, OrderQty
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS [Average]    
FROM Sales.SalesOrderDetail 

SELECT SalesOrderID, ProductID, OrderQty
    ,ROW_NUMER() OVER(PARTITION BY SalesOrderID Order By SalesOrderID) AS [Average]    
FROM Sales.SalesOrderDetail  

User defined functions in T-SQL

User defined functions are set of codes which returns some value may be in terms of scalar value or table value after performing operations. These codes are reusable and made to perform any operation. It can have multiple input parameters but only one return.

Types of UDF in T-SQL

1. Scalar function:
1. deterministic
2. non-deterministic
2. Inline function:
3. Table valued function:

Scalar Functions: In this type of function it returns only one value of any data type except table. For integer data type we have two types:

1. deterministic: It means output is totally dependent on input
2. non-deterministic: That means output is no dependent on input

           CREATE FUNCTION <function-name>(<input-parameters>)
               RETURNS <data-type>
               AS
               BEGIN
                      <statements>
                      Return <variable>
               END

Inline Function: This type of function only returns table type with only single statement. This function does not have BEGIN...END.

       CREATE FUNCTION <function-name>(<input-parameters>)
               RETURNS TABLE
               AS
               RETURN (
                      <statements>
               )

Table Valued Function:  This type of function also returns table type but by creating table variable in definition with data type for each column.
              
       CREATE FUNCTION <function-name>(<input-parameters>)
               RETURNS <table-variable> TABLE (<columns with data-type>)
               AS
               BEGIN
                      <statements>
                      RETURN
              END

Pros: Precompiled which makes execution faster and reusable. A function can call another function and stored procedure also can call function. Can return table type.

Cons: We can not execute stored procedure inside function. Can not have output parameter, So can return only one value of any data type.