DECLARE Table Variable (SQL Server)
DECLARE Table Variable (SQL Server) is a SQL statement in the DDL Statements category. SQL Server: table variables hold result sets in memory. Similar to temp tables but with less logging. The syntax is DECLARE @table TABLE (id INT, name VARCHAR(100));. It returns table variable. A typical example: DECLARE @high_earners TABLE ( id INT PRIMARY KEY, name VARCHAR(100), salary DECIMAL(10,2) ); INSERT INTO @high_earners SELECT id, name, salary FROM employees WHERE salary > 100000; SELECT * FROM @high_earners ORDER BY salary DESC; -- vs #temp tables: -- Table variables: scope limited to batch, no stats, less logging -- Temp tables: broader scope, have stats, more logging -- Best for: small result sets (<100 rows) -- Avoid for: large… A close relative is CREATE TABLE, which creates a new table in the database with specified columns, data types, and constraints. A close relative is ALTER TABLE, which modifies an existing table: add/drop columns, modify data types, rename, add/drop constraints.