--Examples — SQL Server Table Variables
--1. Declaration
DECLARE @t TABLE
(
    id INT,
    name VARCHAR(50)
);
--2. Insert and Select
DECLARE @t TABLE(id INT, name VARCHAR(50));

INSERT INTO @t VALUES (1,'ACME'), (2,'BETA');

SELECT *
FROM @t;
--3. Direct JOIN with a table
DECLARE @Customers TABLE(CustomerID INT);

INSERT INTO @Customers VALUES (1),(2);

SELECT o.OrderID, o.CustomerID
FROM Orders o
JOIN @Customers c
     ON o.CustomerID = c.CustomerID;
--4. With constraint
DECLARE @t TABLE
(
    id INT PRIMARY KEY,
    code VARCHAR(20) UNIQUE
);
--5. Using inside a function
CREATE FUNCTION get_numbers()
RETURNS @t TABLE(num INT)
AS
BEGIN
    INSERT INTO @t VALUES (1),(2),(3);
    RETURN;
END
--6. Debugging (survives rollback)
BEGIN TRAN;

DECLARE @debug TABLE(msg VARCHAR(100));

INSERT INTO @debug VALUES ('Step completed');

ROLLBACK;

SELECT *
FROM @debug;