I can put a primary key on a table variable to speed up processing, but does anybody know if it is or is not possible to add additional non-clustered indexes on a table variable? I can't find any way to do it, yet I can't find anything in Books Online that says specifically that it can't be done.
I'm trying to make a frankenquery more efficient by reducing the size of the recordset. The problem is that many of the criteria and joins utilize secondary indexes on the main table, and so the query actually runs more slowly when I use a reduced recordset without these additional indexes.
Unfortunately, I can't use temporary tables because these procedures will be called by multiple users through Crystal Enterprise which uses the same connection for all users. The only way I can see to keep these user's scopes separate is to use table variables.
i tested asynchronous connection with multiple rs's executing various action queries and sp's, - couldn't get an error to pop up, until i tried to update/delete object that was used in another statement. using this concept i even managed to get a deadlock, from the same connection.