1. Introduction — Kya Hota Hai, Kyun Zaroori Hai

Socho tum ek payroll system mein kaam kar rahe ho.
Har jagah ek hi logic repeat ho raha hai — "Employee ka net salary calculate karo", "Date ko fiscal quarter mein convert karo", "String ko split karo comma se".

Agar ye logic har SP mein, har query mein copy-paste karo — toh ek din requirement change hui aur tumhe 200 jagah jaake fix karna padega. 😩

User-Defined Function (UDF) ka yahi kaam hai — ek baar likho, hazaar jagah use karo.

UDF Kya Hai?

UDF ek T-SQL routine hai jo:
Input parameters leta hai
Processing karta hai
Ek value ya table return karta hai
SELECT, WHERE, JOIN — kahi bhi use ho sakta hai

2. Basic Concepts — Foundations Samjho

SQL Server mein 3 types ke User-Defined Functions hote hain:

User-Defined Functions
│
├── 1. Scalar Function          → ek single value return karta hai (int, varchar, date...)
├── 2. Inline TVF (iTVF)        → ek TABLE return karta hai (single SELECT)
└── 3. Multi-Statement TVF      → ek TABLE return karta hai (multiple statements)
    (MSTVF)

General Syntax Structure

CREATE [OR ALTER] FUNCTION [schema_name].[function_name]
(
    @param1 DataType,
    @param2 DataType = DefaultValue   -- optional default
)
RETURNS ReturnType                    -- scalar ke liye datatype, TVF ke liye TABLE
[WITH SCHEMABINDING]                  -- optional: objects se bind karta hai
[WITH RETURNS NULL ON NULL INPUT]     -- optimization hint
AS
BEGIN
    -- logic yahan
    RETURN value_or_table
END

Key Rules — Jo Poora SQL Community Bhool Jaata Hai

✅ UDF SELECT mein use ho sakta hai
✅ UDF WHERE, JOIN, HAVING mein use ho sakta hai
❌ UDF ke andar INSERT/UPDATE/DELETE permanent table pe nahi hota (exceptions hain)
❌ UDF ke andar RAISERROR / THROW kaam nahi karta
❌ UDF ke andar stored procedure call nahi hoti
❌ UDF ke andar non-deterministic functions (GETDATE, NEWID) ka use schemabinding ke saath nahi

📌 3. Type 1: Scalar Function — Ek Value Return Karta Hai

Syntax

CREATE OR ALTER FUNCTION [dbo].[function_name]
(
    @param DataType
)
RETURNS DataType        -- single value: INT, VARCHAR, DATE, DECIMAL...
[WITH SCHEMABINDING]
AS
BEGIN
    DECLARE @result DataType

    -- logic
    SET @result = ...

    RETURN @result
END

Example 1: Employee ka Age Calculate Karo

CREATE OR ALTER FUNCTION [dbo].[fnHR_GetAge]
(
    @DateOfBirth DATE
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    /*
        Ye function DOB se current age calculate karta hai
        DATEDIFF sirf year count karta hai, isliye birthday
        check se 1 minus karte hain agar birthday abhi nahi aaya
    */
    DECLARE @Age INT

    SET @Age = DATEDIFF(YEAR, @DateOfBirth, GETDATE())
             - CASE
                   WHEN MONTH(@DateOfBirth) > MONTH(GETDATE()) THEN 1
                   WHEN MONTH(@DateOfBirth) = MONTH(GETDATE())
                    AND DAY(@DateOfBirth)   > DAY(GETDATE())   THEN 1
                   ELSE 0
               END

    RETURN @Age
END
GO

-- USE KARO:
SELECT
    EmployeeID,
    EmployeeName,
    DateOfBirth,
    dbo.fnHR_GetAge(DateOfBirth) AS CurrentAge
FROM Employees;

Example 2: Tax Slab Calculator (Payroll)

CREATE OR ALTER FUNCTION [dbo].[fnPAY_IncomeTaxSlab]
(
    @AnnualIncome DECIMAL(18, 2)
)
RETURNS DECIMAL(18, 2)
WITH SCHEMABINDING
AS
BEGIN
    /*
        Old tax regime slabs (simplified example):
        0 - 2.5L    → 0%
        2.5L - 5L   → 5%
        5L - 10L    → 20%
        10L+        → 30%
    */
    DECLARE @Tax DECIMAL(18, 2) = 0

    IF @AnnualIncome <= 250000
        SET @Tax = 0
    ELSE IF @AnnualIncome <= 500000
        SET @Tax = (@AnnualIncome - 250000) * 0.05
    ELSE IF @AnnualIncome <= 1000000
        SET @Tax = (250000 * 0.05) + ((@AnnualIncome - 500000) * 0.20)
    ELSE
        SET @Tax = (250000 * 0.05) + (500000 * 0.20)
              + ((@AnnualIncome - 1000000) * 0.30)

    RETURN @Tax
END
GO

-- USE KARO:
SELECT
    EmployeeID,
    AnnualCTC,
    dbo.fnPAY_IncomeTaxSlab(AnnualCTC)       AS AnnualTax,
    dbo.fnPAY_IncomeTaxSlab(AnnualCTC) / 12  AS MonthlyTDA
FROM EmployeeSalary;

Example 3: Fiscal Quarter Convert Karo (Indian FY: Apr-Mar)

CREATE OR ALTER FUNCTION [dbo].[fnGEN_GetFiscalQuarter]
(
    @Date DATE
)
RETURNS CHAR(6)    -- e.g. 'Q1', 'Q2', 'Q3', 'Q4'
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Month INT = MONTH(@Date)
    DECLARE @Quarter CHAR(2)

    SET @Quarter = CASE
                       WHEN @Month IN (4,  5,  6)  THEN 'Q1'   -- Apr-Jun
                       WHEN @Month IN (7,  8,  9)  THEN 'Q2'   -- Jul-Sep
                       WHEN @Month IN (10, 11, 12) THEN 'Q3'   -- Oct-Dec
                       ELSE                              'Q4'   -- Jan-Mar
                   END

    RETURN @Quarter
END
GO

-- USE KARO:
SELECT
    SaleDate,
    Amount,
    dbo.fnGEN_GetFiscalQuarter(SaleDate) AS FiscalQuarter
FROM SalesData
WHERE dbo.fnGEN_GetFiscalQuarter(SaleDate) = 'Q3';

Example 4: Special Characters Remove Karo (Data Cleaning)

CREATE OR ALTER FUNCTION [dbo].[fnGEN_RemoveSpecialChars]
(
    @InputString NVARCHAR(500)
)
RETURNS NVARCHAR(500)
WITH SCHEMABINDING
AS
BEGIN
    /*
        Sirf A-Z, a-z, 0-9 aur space rakhna hai
        Baaki sab characters remove karo
        PATINDEX se pehla special char dhundho, STUFF se remove karo
    */
    DECLARE @CleanString NVARCHAR(500) = @InputString
    DECLARE @Pos         INT

    -- Jab tak koi special character mile, hatate raho
    SET @Pos = PATINDEX('%[^A-Za-z0-9 ]%', @CleanString)

    WHILE @Pos > 0
    BEGIN
        SET @CleanString = STUFF(@CleanString, @Pos, 1, '')
        SET @Pos         = PATINDEX('%[^A-Za-z0-9 ]%', @CleanString)
    END

    RETURN LTRIM(RTRIM(@CleanString))
END
GO

-- USE KARO:
SELECT
    RawName,
    dbo.fnGEN_RemoveSpecialChars(RawName) AS CleanName
FROM CustomerImport;

4. Type 2: Inline Table-Valued Function (iTVF) — Best Performer

Kya Hota Hai?

Single SELECT statement return karta hai
SQL Server isko view ki tarah treat karta hai — query mein inline merge ho jaata hai
Optimizer andar dekh sakta hai → Parallelism, Predicate Pushdown — sab kaam karta hai
BEGIN...END nahi hota

Syntax

CREATE OR ALTER FUNCTION [dbo].[function_name]
(
    @param DataType
)
RETURNS TABLE
AS
RETURN
(
    -- Sirf ek SELECT statement
    SELECT col1, col2
    FROM   SomeTable
    WHERE  SomeColumn = @param
);
GO

Example 1: Employee ka Salary Detail Return Karo

CREATE OR ALTER FUNCTION [dbo].[fnPAY_GetEmployeeSalaryDetail]
(
    @EmployeeID  INT,
    @PayMonth    INT,
    @PayYear     INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        e.EmployeeID,
        e.EmployeeName,
        e.Department,
        s.BasicSalary,
        s.HRA,
        s.Conveyance,
        s.SpecialAllowance,
        s.BasicSalary + s.HRA + s.Conveyance + s.SpecialAllowance AS GrossSalary,
        s.PF + s.ESI + s.TDS                                       AS TotalDeductions,
        (s.BasicSalary + s.HRA + s.Conveyance + s.SpecialAllowance)
            - (s.PF + s.ESI + s.TDS)                              AS NetSalary
    FROM Employees       e
    JOIN EmployeeSalary  s ON s.EmployeeID = e.EmployeeID
    WHERE e.EmployeeID = @EmployeeID
      AND s.PayMonth   = @PayMonth
      AND s.PayYear    = @PayYear
);
GO

-- USE KARO — CROSS APPLY se multiple employees ke liye:
SELECT
    e.EmployeeID,
    sd.*
FROM Employees e
CROSS APPLY dbo.fnPAY_GetEmployeeSalaryDetail(e.EmployeeID, 4, 2025) sd
WHERE e.DepartmentID = 10;

Example 2: Date Range mein Working Days (Parametric Filter)

CREATE OR ALTER FUNCTION [dbo].[fnATT_GetAttendanceInRange]
(
    @EmployeeID INT,
    @FromDate   DATE,
    @ToDate     DATE
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        a.AttDate,
        a.InTime,
        a.OutTime,
        a.Status,
        DATEDIFF(MINUTE, a.InTime, a.OutTime) / 60.0 AS WorkingHours
    FROM AttendanceMaster a
    WHERE a.EmployeeID = @EmployeeID
      AND a.AttDate   BETWEEN @FromDate AND @ToDate
      AND a.Status    IN ('P', 'HD')    -- Present ya Half Day
);
GO

-- USE KARO:
SELECT *
FROM dbo.fnATT_GetAttendanceInRange(101, '2025-04-01', '2025-04-30')
WHERE WorkingHours < 8;    -- 8 ghante se kam kaam kiya

Example 3: String Splitter — Production Grade (Ye Wala Famous Hai!)

CREATE OR ALTER FUNCTION [dbo].[fnGEN_SplitBlank]
(
    @text      NVARCHAR(MAX),
    @delimiter NVARCHAR(20) = ','
)
RETURNS TABLE
AS
RETURN
(
    WITH
    -- Virtual numbers table banao — koi physical table nahi chahiye
    E1(N) AS
    (
        SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),
                             (1),(1),(1),(1),(1)) t(N)
    ),
    E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b),   --       100 rows
    E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b),   --    10,000 rows
    E6(N) AS (SELECT 1 FROM E2 a CROSS JOIN E4 b),   -- 1,000,000 rows

    -- TOP se sirf utne hi rows lenge jitne string ki length hai
    Tally(N) AS
    (
        SELECT TOP (ISNULL(LEN(@text), 0) + 1)
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM E6
    ),

    -- Har token ki start position dhundho
    TokenStarts(start_pos) AS
    (
        SELECT 1
        WHERE  @text IS NOT NULL AND LEN(@text) > 0

        UNION ALL

        SELECT N + LEN(@delimiter)
        FROM   Tally
        WHERE  @text IS NOT NULL
          AND  LEN(@delimiter) > 0
          AND  N <= LEN(@text) - LEN(@delimiter) + 1
          AND  SUBSTRING(@text, N, LEN(@delimiter)) = @delimiter
    )

    -- Start position se token extract karo
    SELECT
        SUBSTRING(
            @text,
            start_pos,
            ISNULL(NULLIF(CHARINDEX(@delimiter, @text, start_pos), 0),
                   LEN(@text) + 1) - start_pos
        ) AS value
    FROM TokenStarts
);
GO

-- USE KARO — CSV string ko rows mein convert karo:
SELECT s.value AS SkillName
FROM dbo.fnGEN_SplitBlank('SQL Server,Python,Power BI,Azure', ',') s;
/*
Result:
SQL Server
Python
Power BI
Azure
*/

5. Type 3: Multi-Statement TVF (MSTVF) — Jab Logic Complex Ho

Kya Hota Hai?

Multiple statements allowed hain (DECLARE, IF, WHILE, INSERT)
Ek @table variable define karte hain, usme data insert karte hain, phir return karte hain
Performance mein sabse slow — optimizer isko black box treat karta hai
Use tab karo jab iTVF mein logic fit na ho

Syntax

CREATE OR ALTER FUNCTION [dbo].[function_name]
(
    @param DataType
)
RETURNS @ResultTable TABLE
(
    Col1 DataType,
    Col2 DataType
)
AS
BEGIN
    -- Multiple statements allowed
    INSERT INTO @ResultTable
    SELECT ...

    IF (condition)
    BEGIN
        INSERT INTO @ResultTable ...
    END

    RETURN   -- @ResultTable automatically return ho jaata hai
END
GO

Example 1: Employee Hierarchy (Manager Chain)

CREATE OR ALTER FUNCTION [dbo].[fnHR_GetEmployeeHierarchy]
(
    @EmployeeID INT
)
RETURNS @Hierarchy TABLE
(
    Level        INT,
    EmployeeID   INT,
    EmployeeName NVARCHAR(200),
    ManagerID    INT,
    Designation  NVARCHAR(100)
)
AS
BEGIN
    /*
        Recursive CTE se employee ka poora reporting chain nikalo
        Level 0 = Employee khud
        Level 1 = Direct Manager
        Level 2 = Manager ka Manager
        ...aur aage
    */
    WITH EmpCTE AS
    (
        -- Base: employee khud
        SELECT
            0               AS Level,
            e.EmployeeID,
            e.EmployeeName,
            e.ManagerID,
            e.Designation
        FROM Employees e
        WHERE e.EmployeeID = @EmployeeID

        UNION ALL

        -- Recursive: manager chain upar jaao
        SELECT
            c.Level + 1,
            e.EmployeeID,
            e.EmployeeName,
            e.ManagerID,
            e.Designation
        FROM Employees e
        JOIN EmpCTE    c ON c.ManagerID = e.EmployeeID
        WHERE c.Level < 10   -- infinite loop se bachao
    )
    INSERT INTO @Hierarchy
    SELECT Level, EmployeeID, EmployeeName, ManagerID, Designation
    FROM   EmpCTE;

    RETURN;
END
GO

-- USE KARO:
SELECT * FROM dbo.fnHR_GetEmployeeHierarchy(1042)
ORDER BY Level;

Example 2: Variable Salary Components (Dynamic Rows)

CREATE OR ALTER FUNCTION [dbo].[fnPAY_GetVariableComponents]
(
    @EmployeeID INT,
    @Month      INT,
    @Year       INT
)
RETURNS @Components TABLE
(
    ComponentName  NVARCHAR(100),
    ComponentType  CHAR(1),       -- 'E' = Earning, 'D' = Deduction
    Amount         DECIMAL(18,2)
)
AS
BEGIN
    -- Earnings insert karo
    INSERT INTO @Components
    SELECT ComponentName, 'E', Amount
    FROM   PayrollEarnings
    WHERE  EmployeeID = @EmployeeID
      AND  PayMonth   = @Month
      AND  PayYear    = @Year;

    -- Deductions insert karo
    INSERT INTO @Components
    SELECT ComponentName, 'D', Amount
    FROM   PayrollDeductions
    WHERE  EmployeeID = @EmployeeID
      AND  PayMonth   = @Month
      AND  PayYear    = @Year;

    -- Agar koi row nahi aaya toh default row daalo
    IF NOT EXISTS (SELECT 1 FROM @Components)
    BEGIN
        INSERT INTO @Components VALUES ('No Data', 'E', 0);
    END

    RETURN;
END
GO

6. WITH SCHEMABINDING — Kya Hai, Kyun Use Karo?

CREATE OR ALTER FUNCTION [dbo].[fnHR_GetAge]
(
    @DOB DATE
)
RETURNS INT
WITH SCHEMABINDING    -- ← Ye wala
AS
BEGIN
    ...
END

SCHEMABINDING Kyun Important Hai?

Bina SCHEMABINDING	SCHEMABINDING ke Saath
Koi bhi table column drop/rename kar sakta hai — function silently break	Table column drop/rename blocked — error aata hai
Optimizer ko determinism pata nahi	Deterministic function ban sakta hai
Index on Computed Column nahi ban sakta	Indexed computed column ban sakta hai
Performance hints limited	Better execution plans

Rule: Har scalar aur iTVF mein WITH SCHEMABINDING lagao — unless GETDATE(), NEWID() use karna ho.

7. Intermediate Usage — Real Workflow Patterns

Pattern 1: CROSS APPLY — iTVF ko Superpower Do

-- Har employee ke liye last 3 months ki attendance summary
SELECT
    e.EmployeeID,
    e.EmployeeName,
    att.AttDate,
    att.WorkingHours
FROM Employees e
CROSS APPLY
(
    SELECT TOP 3
        a.AttDate,
        DATEDIFF(MINUTE, a.InTime, a.OutTime) / 60.0 AS WorkingHours
    FROM dbo.fnATT_GetAttendanceInRange(
             e.EmployeeID,
             DATEADD(MONTH, -3, GETDATE()),
             GETDATE()
         ) a
    ORDER BY a.AttDate DESC
) att;

Pattern 2: Function ko WHERE Clause Mein

-- Sirf vo employees jinka age 30+ ho
SELECT EmployeeID, EmployeeName
FROM   Employees
WHERE  dbo.fnHR_GetAge(DateOfBirth) >= 30;

-- ⚠️ WARNING: Scalar function in WHERE → Index use nahi hota!
-- Better approach: Computed column ya calling query mein calculate karo

Pattern 3: Computed Column mein UDF

-- Table mein Age automatically calculate karo
ALTER TABLE Employees
ADD Age AS dbo.fnHR_GetAge(DateOfBirth);   -- virtual, stored nahi

-- Ya persisted (stored):
ALTER TABLE Employees
ADD Age AS dbo.fnHR_GetAge(DateOfBirth) PERSISTED;
-- Persisted ke liye function DETERMINISTIC hona chahiye (SCHEMABINDING required)

Pattern 4: Check Constraint mein UDF

-- Custom validation: joining date future mein nahi ho sakti
CREATE OR ALTER FUNCTION [dbo].[fnHR_ValidateJoiningDate]
(
    @JoiningDate DATE
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE WHEN @JoiningDate <= GETDATE() THEN 1 ELSE 0 END
END
GO

ALTER TABLE Employees
ADD CONSTRAINT CK_ValidJoiningDate
    CHECK (dbo.fnHR_ValidateJoiningDate(JoiningDate) = 1);

8. Advanced Concepts — Performance aur Internals

🔴 Scalar UDF ka Sabse Bada Problem: RBAR

RBAR = Row By Agonizing Row

Jab scalar UDF ko SELECT mein use karo — SQL Server har row ke liye alag execute karta hai:

-- 10 lakh rows? → Function 10 lakh baar chalega!
SELECT EmployeeID, dbo.fnPAY_IncomeTaxSlab(AnnualCTC) AS Tax
FROM   EmployeeSalary;   -- 1,000,000 rows = 1,000,000 function calls

✅ Solution 1: Scalar UDF → iTVF mein Convert Karo

-- Scalar (slow):
SELECT dbo.fnPAY_IncomeTaxSlab(AnnualCTC) FROM EmployeeSalary;

-- iTVF equivalent (fast):
CREATE OR ALTER FUNCTION [dbo].[fnPAY_TaxSlabITVF]
(
    @AnnualIncome DECIMAL(18,2)
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        CASE
            WHEN @AnnualIncome <= 250000  THEN 0
            WHEN @AnnualIncome <= 500000  THEN (@AnnualIncome - 250000)  * 0.05
            WHEN @AnnualIncome <= 1000000 THEN 12500 + (@AnnualIncome - 500000)  * 0.20
            ELSE                               112500 + (@AnnualIncome - 1000000) * 0.30
        END AS Tax
);
GO

-- Use with CROSS APPLY:
SELECT e.EmployeeID, t.Tax
FROM   EmployeeSalary e
CROSS APPLY dbo.fnPAY_TaxSlabITVF(e.AnnualCTC) t;

✅ Solution 2: Scalar UDF Inlining (SQL Server 2019+)

SQL Server 2019 mein Scalar UDF Inlining feature aaya — simple scalar UDFs automatically inline ho jaate hain:

-- Check karo kya tera function inlineable hai:
SELECT
    name,
    is_inlineable,        -- 1 = Yes, 0 = No
    inline_type
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.fnPAY_IncomeTaxSlab');

Function inlineable hone ke liye:

No TRY...CATCH
No WHILE loops
No multiple RETURN statements in branches
No user-defined types
No @@ROWCOUNT etc.

✅ Solution 3: Determinism aur Indexed View

-- Function deterministic hai ya nahi check karo:
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.fnHR_GetAge'), 'IsDeterministic');
-- 1 = Deterministic (same input → always same output)
-- 0 = Non-deterministic

-- Deterministic functions indexed computed columns mein use ho sakte hain!

MSTVF Performance Problem — Row Estimate Always 1

-- MSTVF ke saath execution plan dekho:
-- Estimated rows = 1 (hamesha!)
-- Actual rows = 5000
-- → Bad join strategy, bad memory grant → SLOW!

-- Fix: Use OPTION (RECOMPILE) ya iTVF mein convert karo
SELECT *
FROM   SomeTable st
JOIN   dbo.fnHR_GetEmployeeHierarchy(101) h ON h.EmployeeID = st.EmployeeID
OPTION (RECOMPILE);    -- har baar fresh plan banao

9. Real-World Use Cases

Use Case 1: Payroll Report mein Multiple UDFs

-- Ek payroll slip query jisme 3 UDFs use ho rahe hain
SELECT
    e.EmployeeID,
    e.EmployeeName,
    e.Department,
    dbo.fnGEN_GetFiscalQuarter(s.PayDate)       AS FiscalQuarter,
    s.GrossSalary,
    dbo.fnPAY_IncomeTaxSlab(s.AnnualCTC)  / 12  AS MonthlyTax,
    dbo.fnHR_GetAge(e.DateOfBirth)               AS EmployeeAge,
    s.GrossSalary
        - (dbo.fnPAY_IncomeTaxSlab(s.AnnualCTC) / 12)
        - s.PF - s.ESI                           AS NetTakeHome
FROM Employees      e
JOIN EmployeeSalary s ON s.EmployeeID = e.EmployeeID
WHERE s.PayMonth = MONTH(GETDATE())
  AND s.PayYear  = YEAR(GETDATE());

Use Case 2: Dynamic CSV Split in Stored Procedure

-- SP ke andar iTVF string splitter use karo
CREATE OR ALTER PROCEDURE [dbo].[uspRPT_GetMultiEmployeeReport]
    @EmployeeIDList NVARCHAR(MAX)    -- '101,102,103,104'
AS
BEGIN
    SET NOCOUNT ON;

    SELECT e.*
    FROM   Employees e
    WHERE  e.EmployeeID IN
    (
        SELECT CAST(value AS INT)
        FROM   dbo.fnGEN_SplitBlank(@EmployeeIDList, ',')
        WHERE  value <> ''
    );
END
GO

-- Call karo:
EXEC dbo.uspRPT_GetMultiEmployeeReport '101,102,103,104,105';

Use Case 3: Data Validation Layer

-- Bulk import se pehle data validate karo
CREATE OR ALTER FUNCTION [dbo].[fnGEN_IsValidEmail]
(
    @Email NVARCHAR(200)
)
RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE
               WHEN @Email LIKE '%_@_%.__%'
                AND @Email NOT LIKE '%[<>()[\]\\,;: ]%'
               THEN CAST(1 AS BIT)
               ELSE CAST(0 AS BIT)
           END
END
GO

-- Import table mein invalid emails dhundho:
SELECT *
FROM CustomerImport
WHERE dbo.fnGEN_IsValidEmail(EmailAddress) = 0;

10. Edge Cases aur Common Errors

Error 1: ALTER nahi chala type change pe

-- ERROR: Cannot perform alter on incompatible object type
-- (MSTVF → iTVF convert karte time)

-- FIX: DROP → CREATE
DROP FUNCTION IF EXISTS dbo.fnGEN_SplitBlank;  -- SQL 2016+
GO
CREATE FUNCTION dbo.fnGEN_SplitBlank...
GO

Error 2: NULL input se unexpected results

-- Scalar function NULL return kar sakta hai agar NULL input aaye
SELECT dbo.fnHR_GetAge(NULL);   -- NULL return hoga, error nahi

-- FIX: NULL handle karo function ke andar:
IF @DateOfBirth IS NULL RETURN NULL;
-- Ya function option:
CREATE FUNCTION ... RETURNS INT
WITH RETURNS NULL ON NULL INPUT   -- Auto NULL return karo

Error 3: Recursive function maximum depth

-- SQL Server mein recursive function ki max depth = 32
-- Zyada deep recursion → Error 217

-- FIX: Level counter se limit lagao:
WHERE c.Level < 10   -- ya apni zaroorat ke hisaab se

Error 4: Scalar UDF mein GETDATE() aur SCHEMABINDING

-- YE KAAM NAHI KARTA:
CREATE FUNCTION dbo.fnTest(@d DATE)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN DATEDIFF(DAY, @d, GETDATE())   -- ERROR! GETDATE() non-deterministic
END

-- FIX: SCHEMABINDING hatao ya GETDATE() ko parameter se pass karo:
CREATE FUNCTION dbo.fnTest(@d DATE, @Today DATE)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN DATEDIFF(DAY, @d, @Today)
END

Error 5: Side Effects — DML nahi chal sakta permanent tables pe

-- YE KAAM NAHI KARTA SCALAR/iTVF MEIN:
CREATE FUNCTION dbo.fnTest(@x INT)
RETURNS INT AS
BEGIN
    INSERT INTO AuditLog VALUES (@x, GETDATE())   -- ERROR!
    RETURN @x
END

-- FIX: Agar DML chahiye toh Stored Procedure use karo, Function nahi

11. Pro Developer Insights — Best Practices

Naming Convention

fn{Module}_{Action}{Object}

fn = Function prefix (always)
Module = PAY, HR, ATT, GEN, RPT
Action = Get, Calc, Validate, Convert, Split
Object = Employee, Date, Salary, String

Examples:
✅ fnPAY_GetNetSalary
✅ fnHR_CalcAge
✅ fnGEN_SplitBlank
✅ fnATT_GetWorkingDays
❌ GetSalary (no fn prefix)
❌ fn1 (meaningless)

Performance Checklist

□ Scalar UDF hai aur large table pe use hoga → iTVF mein convert karo
□ SCHEMABINDING lagaya hai?
□ WITH RETURNS NULL ON NULL INPUT hai NULL-passthrough logic ke liye?
□ MSTVF use kar rahe ho → kya iTVF mein convert ho sakta hai?
□ SQL Server 2019+ hai → is_inlineable check kiya?
□ WHERE clause mein scalar UDF → Index use nahi hoga, computed column consider karo
□ Recursive function mein level limit lagayi hai?

Security Best Practice

-- Sirf execute permission do, table access mat do directly:
GRANT EXECUTE ON dbo.fnPAY_IncomeTaxSlab TO [ReportingRole];

-- Schema-level security:
GRANT SELECT ON SCHEMA::dbo TO [ReadOnlyUser];

12. Comparison Table — Teeno Types

Feature	Scalar UDF	Inline TVF	MSTVF
Returns	Single value	Table (1 SELECT)	Table (multiple stmts)
Performance	🔴 Slow (RBAR)	🟢 Fast (inlined)	🟡 Medium
Optimizer visibility	❌ Black box	✅ Full visibility	❌ Black box
Row estimate	N/A	✅ Accurate	❌ Always 1
Parallelism	❌ No	✅ Yes	❌ No
DML inside	❌ No	❌ No	❌ No (temp only)
WHILE/IF logic	✅ Yes	❌ No	✅ Yes
Use in WHERE	✅ Yes	✅ (APPLY)	✅ (APPLY)
SCHEMABINDING	✅ Recommended	✅ Recommended	✅ Optional
SQL 2019 Inlining	✅ (simple ones)	Already inlined	❌ No
Best for	Simple calculation	Set-based filtering	Complex row logic

Interview Questions

Basic Level

Q1. SQL Server mein kitne types ke UDF hote hain?

3 types: Scalar Function, Inline TVF, Multi-Statement TVF

Q2. Scalar aur Table-Valued Function mein kya fark hai?

Scalar ek single value return karta hai. TVF ek table return karta hai jise CROSS APPLY ya FROM clause mein use kar sakte hain.

Q3. UDF ko Stored Procedure se kaise alag samjhein?

UDF SELECT mein use ho sakta hai, SP nahi. UDF DML nahi kar sakta permanent tables pe. SP output parameters ya result sets return karta hai, UDF directly value/table return karta hai.

Q4. SCHEMABINDING kya karta hai?

Function ko underlying objects se bind karta hai — agar koi referenced column drop karo toh SQL Server error deta hai.

Intermediate Level

Q5. iTVF aur MSTVF mein performance fark kyun hota hai?

iTVF SQL Server mein view ki tarah inline ho jaata hai — optimizer andar dekh sakta hai, parallel plans bana sakta hai, predicate pushdown hoti hai. MSTVF black box hai — optimizer maan leta hai 1 row return hoga (hamesha wrong), parallelism blocked hai.

Q6. Scalar UDF mein RBAR kya hai?

Row By Agonizing Row — jab scalar UDF SELECT mein use hota hai, SQL Server har row ke liye usse alag call karta hai. 10 lakh rows = 10 lakh function calls.

Q7. CROSS APPLY aur OUTER APPLY mein kya fark hai TVF ke saath?

CROSS APPLY = INNER JOIN ki tarah — jo rows TVF se empty return karti hain wo exclude. OUTER APPLY = LEFT JOIN ki tarah — NULL values ke saath bhi rows aati hain.

Advanced Level

Q8. Scalar UDF Inlining kya hai? Kab aaya?

SQL Server 2019 mein aaya. Simple scalar UDFs automatically inline ho jaate hain — RBAR nahi hota, optimizer visibility milti hai. sys.sql_modules mein is_inlineable se check karo.

Q9. Function deterministic kab hota hai?

Jab same inputs pe hamesha same output aaye. GETDATE(), NEWID() use karne se non-deterministic ho jaata hai. Deterministic functions indexed computed columns mein use ho sakte hain.

Q10. MSTVF ka row estimate always 1 kyun hota hai?

SQL Server imperative code (WHILE, IF, multiple INSERTs) ka output predict nahi kar sakta, isliye default estimate 1 use karta hai. Is wajah se bad execution plans bante hain — especially joins mein.

Scenario-Based

Q11. Tumhare paas ek scalar function hai jo 500 SP mein use hota hai aur performance issues aa rahe hain. Kya karoge?

Step 1: sys.dm_exec_function_stats se execution count aur CPU time dekho. Step 2: Check karo kya SQL 2019 inlining se help hogi (is_inlineable). Step 3: Logic simple hai toh iTVF mein convert karo aur CROSS APPLY use karo. Step 4: Agar WHERE clause mein use hota hai toh computed persisted column consider karo.

Q12. MSTVF ko iTVF mein convert karte waqt ALTER fail ho gaya — kya aur kyun?

Error 2010: ALTER se object type change nahi hota (TF → IF). Solution: DROP → CREATE karo. Pehle sys.sql_expression_dependencies se dependencies check karo, phir DROP karo aur naya iTVF CREATE karo.

Conclusion — Key Learnings

Yaad Rakho — Quick Reference

Kya chahiye?                        → Kaunsa UDF?
─────────────────────────────────────────────────
Single value (age, tax, quarter)    → Scalar UDF
                                      (small tables ke liye theek)

Set-based filtering, joins,         → Inline TVF ⭐
large tables, CROSS APPLY           → ALWAYS prefer this

Complex row-by-row logic,           → MSTVF
hierarchy, conditional inserts      (last resort)

Final Advice

Pehle iTVF try karo — agar logic fit hota hai single SELECT mein
Scalar UDFs mein SCHEMABINDING lagao hamesha
WHERE clause mein scalar UDF mat daalo large tables ke liye
SQL 2019+ pe is_inlineable check karo apne scalar functions ka
MSTVF mein hamesha OPTION (RECOMPILE) ya row hints consider karo
Naming convention follow karo — fn{Module}_{Action} pattern
Dependencies check karo DROP se pehle — production mein blindly mat karo

Golden Rule: UDF ek "reusable calculation unit" hai — jab bhi ek hi logic 3+ jagah repeat ho, UDF banao. Lekin performance ka dhyan rakho — sahi type ka UDF chunna utna hi important hai jitna UDF banana.

Results for "MSTVF"

SQL Server User-Defined Functions (UDF)