• Haining (6/23/2012)


    Should it be dependent on the version of SQL Server targeted on?

    (...)

    This is contradicted to the later version SQL Server on Limitations and Restrictions for table variable. Reference: http://msdn.microsoft.com/en-us/library/ms175010%28v=sql.105%29.aspx

    No, this has been the case since table variables were introduced (in SQL 2000), and never changed since. The information at the link you post (which applies to SQL Server 2008R2) explicitly mentions PRIMARY KEY and UNIQUE as types of constraints that can be declared for a table variable. It does not explicitly say that an index will be implicitly created for those constraints, but that is explained at other sources. (I don't have time to hunt down a good reference now; sorry).

    jeff.mason (6/23/2012)


    You cannot define indexes at object creation. And while a typical table will create an index for a primary key, I suspect that this does not happen for table variables.

    Your suspicion is wrong. A supporting index will always be created for primary key and unique constraints, even in table variables.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/