Is there any difference between a variable table and a topic if this variable table would be a custom type?

For example, in performance, and so on.

I know that a custom data type is powerful to return and pass to functions.

  • rephrase, please - Kryshtop

1 answer 1

Yes, of course there are differences - UDTT is still not a complete table. Other than the restrictions listed in User-Defined Table Types , like

  • the inability to change the structure of a custom type after creating
  • inability to create a nonclustered index on a user type (with reservations)

There is another important difference that directly affects performance: SQL Server does not support statistics on columns when passing a custom type value as a parameter . Those. You can assume that there is always about one line in your passed parameter. It was repaired a bit in SQL Server 2014 , but the problem sometimes still manifests itself.

In practice, this means that when using a UDT and using a temporary table for the same purpose, you may receive different execution plans for equivalent queries. And, accordingly, different performance, and UDT does not necessarily show the worst result.