Ye ek User-Defined Table Type (UDTT) hota hai.

Aap SQL Server me ek custom table ka design bana lete ho, jise aap baad me Stored Procedures, Functions, aur TVPs (Table-Valued Parameters) me input/output ke liye use kar sakte ho.

Iska main kaam hota hai — Stored Procedure ko ek table as a parameter pass karna.
Normal parameters me hum ek value pass karte hain, par table type me hum pure rows ka ek set pass kar sakte hain.

⭐ Syntax: How to Create a Table Type

CREATE TYPE EmployeeType AS TABLE
(
    EmpID INT,
    EmpName VARCHAR(100),
    Salary DECIMAL(10,2)
);

Ye ek new data type ban gaya jiska naam EmployeeType hai.

⭐ How to Use Table Type in Stored Procedure

Declare a variable of type EmployeeType

DECLARE @Emp EmployeeType;

INSERT INTO @Emp VALUES
(1, 'Amit', 50000),
(2, 'Sumit', 60000);

SELECT * FROM @Emp

Create a Stored Procedure that accepts Table Type

ALTER PROCEDURE InsertEmployees
(
    @EmpData EmployeeType READONLY
)
AS
BEGIN
    SELECT * FROM @EmpData
   -- DELETE FROM @EmpData 
   --The table-valued parameter "@EmpData" is READONLY and cannot be modified.
END;


GO

DECLARE @Emp EmployeeType 

INSERT INTO @Emp VALUES
(1, 'Amit', 50000),
(2, 'Sumit', 60000);



EXEC InsertEmployees @Emp
GO

Q1. What is CREATE TABLE TYPE in SQL Server?

Answer:
CREATE TABLE TYPE ek User-Defined Table Type (UDTT) banata hai.
Iska use tab hota hai jab hume table ko parameter ki tarah Stored Procedure ya Function me pass karna ho.

Ye ek custom table structure hota hai jo multiple rows ko ek shot me pass karne ki permission deta hai.

Q2. Why do we write READONLY with Table Type?

Answer:
READONLY mandatory hota hai.
Agar READONLY nahi likhoge to stored procedure compile hi nahi hoga.

Reason:

• Table-Valued Parameters (TVP) ko modify nahi kiya ja sakta
• Ye memory-optimized hote hai
• SQL Server safety ke liye modification allow nahi karta
• Performance issues avoid karne ke liye READONLY compulsory hai

Q3. Can we modify data inside a Table Type parameter?

Answer:
❌ No. Impossible.
Aap insert/update/delete nahi kar sakte kyunki SQL Server TVP ko internally READONLY design karta hai.

Q4. Can Table Type be altered?

Answer:
❌ No.
Aap ALTER TYPE nahi kar sakte.
Agar structure change karna ho to:

1. DROP TYPE EmployeeType
2. CREATE TYPE EmployeeType (new structure)

Q5. What are best use cases of Table Type?

Answer:

• Bulk insert into a table
• Sending multiple rows to stored procedures
• Passing list of IDs for filtering
• Log processing
• Master-detail inserts
• High-performance data operations