Nonclustered indexes on SQL Server 2014, table variables

As you can see from Paul White's Twitter, in SQL Server 2014, for table variables (tables Variables), it is supported for non-unique clustered indexes (Non-unique Clustered Indexes) and nonclustered indexes (non-clustered Indexes). Seeing this, I decided to try it on my own virtual machine, because it would be an excellent feature. Table variables are great because they can be used to avoid excessive recompilation (excessive recompilations). When you create them, they are not statistical information and you do not change the database schema. They are only variables, but they are still resident in tempdb.

One drawback of table variables is that you cannot create nonclustered indexes on them, which is not good when working with large numbers of datasets. However, SQL Server CTP1 has fixed this shortcoming. Take a look at the following code (click on the toolbar's display to include the actual execution plan):

1 DECLARE @tempTable TABLE2 (3IdINT IDENTITY(1,1)PRIMARY KEY,4FirstNameCHAR( -)INDEXIdx_firstname,5LastNameCHAR( -)6 )7     8 INSERT  into @TempTable(FirstName, LastName)9 SELECT TOP 100000Name, name fromMaster.dbo.syscolumnsTen      One SELECTFirstName from @TempTable A WHEREFirstName= 'CID' - GO

We look at the execution plan for the SELECT statement, and SQL Server performs the nonclustered index scan operator (non-clustered index Seek operator). That is, we can define extra nonclustered indexes on table variables. There is no statistics for each nonclustered index that is created. This feature is cool, and is supported in simple syntax (easy syntax) for regular database tables. Let's look at the following table definition:

1 CREATE TABLEFoo2 (3Col1INT PRIMARY KEY CLUSTERED,4Col2INT INDEXIdx_col2,5Col3INT INDEXidx_col36 )7 GO

This will prompt the following error on SQL Server 2008r2:

Successful execution on SQL Server 2014!

Further, we can also create a composite index (composite indexes) with the new syntax:

1 --Inline creation of Indexes2 CREATE TABLEFoo23 (4Col1INT PRIMARY KEY CLUSTERED,5Col2INT INDEXidx_col2 (Col2, Col3),6Col3INT7 )8 GO

The previous version (I am here for SQL Server 2008R2) can only be prompted with the following error:

can have fried days, everyone hurriedly to experience!

Nonclustered indexes on SQL Server 2014, table variables