1. Table variable syntax:

declare @my_table_variable table(  
  id int,
  name varchar(50)
);
select * from @my_table_variable;
go

1. Table variable is a local variable with a special data type table.

2. Table variable is not created in memory. It is created in temp_db.

3. Table variable declared will have a different SQL engine name inside the temp_db.

4. Table variable will become out of scope once the batch ends. If you try to access after the batch separator GO, you will get an error to declare the table variable.

5. Table variable can be declared inside a user defined function (UDF) and DML operation can be executed on table variable. But temporary table can not be created inside an UDF.

Limitations

1. Table variable can not be dropped explicitly.

2. Table variable can not be truncated.

3. Table variable has limited scope and are not part of the persistent database, transaction rollbacks don't affect them.

4. Table variable can be used in SELECT, INSERT, UPDATE, and DELETE statement. But it can not be used in SELECT * INTO.

5. You can not create explicit index on the table variable. When declaring the table variable, you can define the column as PRIMARY KEY or UNIQUE. This will create an implicit index.

6. CHECK constraint, DEFAULT value, and computed columns in table variable can not call a UDF.

7. You can not create a named constraint.

8. You can not use user defined data type in column definition.

9. If you are using nested stored procedure, the table variable is not visible to the calling stored procedure (outer sp) unlike temporary tables.

10. If you create a table variable column with identity. You can not insert explicit value in identity column. Because SET IDENTITY INSERT ON/OFF can not be used.

11. You can not create a table variable in dynamic SQL.

12. Table variable will have a cardinality of 1. So statistics are not tracked for them and this result in bad query plan.

13. Queries that modify the table variables don't generate the parallel query execution plans. Performance can be affected when large table variables or table variables in complex query is modified. In this scenario, temporary table is a better alternative.

14. Mostly , optimizer will build a query plan on the assumption that table variable has no rows. So, if the table variable is going to have more than 100 records, then temporary table is the best option.

15. If table variable is going to be used in JOIN, use the RECOMPILE hint. So that the optimizer will use the correct cardinality for the table variable.