4

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

For example:

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

Usage:

SELECT * FROM [dbo].[fnSplitIDs]('{ADD26A9A-ABDD-4755-9B3C-C4F12C46988F},{C9F92768-77F6-468B-A85C-4F42C2FDD6F6}')
TT.
  • 15,774
  • 6
  • 47
  • 88
zig
  • 4,524
  • 1
  • 24
  • 68
  • 3
    Not enough of a difference to matter compared to the technique's actual performance. Tthere *are* a lot of other ways to split strings - avoiding multi-value strings in the first place is best. SQLCLR is the fastest option. XML doesn't perform as well as other methods. Check [Aaron Bertrand's comparison of all the methods](http://sqlperformance.com/2012/07/t-sql-queries/split-strings) – Panagiotis Kanavos Feb 15 '16 at 10:24
  • Where does the multi-valued string come from? Could it be replaced with a table-typed variable, so you can avoid splitting entirely? Eg. ADO.NET allows table-valued parameters – Panagiotis Kanavos Feb 15 '16 at 10:25
  • @PanagiotisKanavos, I use it in a Delphi client application. no table-valued variables there. – zig Feb 15 '16 at 10:33
  • are you sure? What library/driver did you use? Which Delphi version? This type isn't specific to ADO.NET, nor is it a new addition. All you need is an updated driver. – Panagiotis Kanavos Feb 15 '16 at 14:00
  • @PanagiotisKanavos, I'm not 100% sure. I use Delphi 5 with TADO components. SQLOLEDB.1 provider. there are no complex parameters AFAIK. – zig Feb 15 '16 at 14:04
  • The one that came out in 1999? You won't even find support for SQL Server 2000 there, much less for functionality that was added in 2008. Even if you mean Delphi 2005, you won't find TVPs. *Maybe* you could use Devart's drivers, but I'm not sure they can be used with such old versions – Panagiotis Kanavos Feb 15 '16 at 14:09
  • @PanagiotisKanavos, yes the 1999 one :) "You won't even find support for SQL Server 2000" ADO basically does everything I need. well, except maybe TVPs. I'll dig into it. – zig Feb 15 '16 at 14:17
  • as you just found out, no it doesn't. It also doesn't handle failover connections that use database mirrorring. Doesn't support date and time types, timezone offsets. Imagine storing times for two different countries, for example, or trying to deal with DST – Panagiotis Kanavos Feb 15 '16 at 14:21

0 Answers0