This is truly an amazing feature to add to SQL Server 2014. I use table variables all the time and this was the only thing I didn’t like about them. Up to this point, SQL Server did not support having non-clustered indexes on table variables (the one with the name that starts with the @). If you wanted to do this, you had to create/use a temp table (the one with the name that starts with #).
With SQL Server 2014, they changed this and now allow the table variables to have non-clustered indexes as well. To do this, we simply add a little bit of extra code after the column declaration.
DECLARE @AnimalTableVar TABLE ( AnimalID INT, AnimalName VARCHAR(50) INDEX IX_AnimalTempTable_AnimalName )
In the sample above, we create a table variable called @AnimalTableVar. Then when we declare the column AnimalName, we add an index called IX_AnimalTempTable_AnimalName.
Here is how selecting from this table shows up in the execution plan!
Reference: http://msdn.microsoft.com/en-us/library/ms188927(v=sql.120).aspx