Lynn Pettis (6/24/2014)

---

Andrew Kernodle (6/24/2014)

---

I don't believe that table variables are set to be deprecated, though they do have different use cases than, say, a temp table.

Table variables don't have optimization statistics on them, and they can't have indexes, so if you're holding large volumes of data and sorting through them based on specific values, they can be a performance hog, from what I understand.

On the other hand, as Lynn points out, they aren't affected by ROLLBACK statements, so they can be useful for testing, and on smaller data sets, they may actually have performance benefits compared to temp tables, as discussed in this[/url] article.

As usual, in SQL Server, "it depends"! Testing is the best way to determine whether a table variable will sink a given bit of code or not. It sounds like you've done the testing in this case to disprove their usefulness, but disregarding them outright should be reserved until testing shows it to be necessary 🙂

Careful about saying you can't have indexes on table variables, you can. You have create them as a primary key or unique key constraint while declaring the table variable. What you can't do is create indexes on table variables after they are declared.

They still won't have statistics on them, but they can have indexes.