Revert table from user function which is best?

I was wondering what is the best practice to return a table from a function? Should I define table structure or use RETURNS TABLE? What are the scenarios for using both flavors?

For instance:

ALTER FUNCTION [dbo].[fnSplitIDs]
(
   @List VARCHAR(MAX)
)
RETURNS TABLE
AS
  RETURN ( 
    SELECT Item AS ID FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'uniqueidentifier')
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'
        + REPLACE(REPLACE(@List, ',', '</i><i>') , '''', '') + '</i>').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  );

Versus:

ALTER FUNCTION [dbo].[fnSplitIDs]
(
   @List VARCHAR(MAX)
)
RETURNS @t TABLE([ID] uniqueidentifier)
AS
BEGIN
  INSERT INTO @t([ID])
  SELECT Item AS ID FROM
      ( SELECT Item = x.i.value('(./text())[1]', 'uniqueidentifier')
        FROM ( SELECT [XML] = CONVERT(XML, '<i>'
        + REPLACE(@List, ',', '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
      WHERE Item IS NOT NULL
  RETURN
END

Using:

SELECT * FROM [dbo].[fnSplitIDs]('{ADD26A9A-ABDD-4755-9B3C-C4F12C46988F},{C9F92768-77F6-468B-A85C-4F42C2FDD6F6}')
+4
source share

Source: https://habr.com/ru/post/1628814/


All Articles