Can a table variable have an index?
Creating an index on a table variable can be done implicitly within the declaration of the table variable by defining a primary key and creating unique constraints. You can also create the equivalent of a clustered index. To do so, just add the clustered reserved word.
Why are temp tables faster than table variables?
However, if there is a memory pressure the pages belonging to a table variable may be pushed to tempdb. ⇒ Table variables cannot be involved in transactions, logging or locking. This makes @table faster then #temp. So table variable is faster then temporary table.
How to test the index of a temp table?
Example below query the before and after comparison of index creation in temp table: You need to test if the index will help you or not. You need to balance how many index you can have because it can also impact your performance if you have too many index.
Why does SQL Server not use index scan?
If your query is returning too many rows, a high percentage of the total rows in the table, SQL Server will not use the index. This happens because the cost of the key lookup for each row can be bigger than the cost of an entire scan.
Which is more efficient index scan or index predicate?
Index Scan: Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.
How are temporary tables indexed in SQL Server?
The underlying tables are huge (some have billions of rows), so most stored procedures are designed such that first they extract only the relevant rows of these huge tables into temporary tables, and then the temp tables are joined with each other and with other smaller tables to create a final extract. Something similar to this: