Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Allow CLR UDT to return IEnumerable / Table / TVF from a method ( SqlMethod ) like XML.nodes() SQLCLR

Currently, it is only possible to return scalar values from User-Defined Types (UDTs), either as properties or methods.

Sometimes, however, one has a list / array / collection of items that needs to be returned. The only ways to accomplish this right now are either:

a) return XML / SqlXml and parse it with the T-SQL XML.nodes() function, or

b) return a serialized string to be parse by a T-SQL or SQLCLR TVF, or

c) pass the UDT itself into a SQLCLR TVF.

In all three cases, the mechanics of doing this are clunky, and extra steps are being taken to serialize / deserialize, resulting in increased CPU and elapsed times.

Here is a question on Stack Overflow where someone is asking how to accomplish this:
Creating a table method on a user defined type (like like 'nodes' on the XML data type) ( http://stackoverflow.com/q/30851523/577765 )

5 votes
Sign in
(thinking…)
Password icon
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Upvotes: 4

<=-=Mar 6 2017 3:54PM=-=>

Thanks for your feedback! We do not have any plans to change CLR user defined types at this time, but we will consider it for future releases.

<=-=Mar 6 2017 3:55PM=-=>

Thanks for your feedback! We do not have any plans to change CLR user defined types at this time, but we will consider it for future releases.

1 comment

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base