Funciton Vs Inline Query

• santoooo

  SSCommitted

  Points: 1999

  More actions

  July 9, 2009 at 1:01 am

  #73235

  Dear All,

  I am very curious to know whichone will give the better performance either Funtion or Inline Query.

  My requirement is

  i am having a query(which will return millions of records) in which i have called funciton that will return the comma seperated value for the given id

  Here is the funciton code.

  CREATE FUNCTION [dbo].[fn_SplitByComma]

  (

  @Value Bigint

  ) RETURNS varchar(8000)

  AS BEGIN

  DECLARE @Result varchar(8000)

  SET @Result = ''

  SELECT @Result = @Result + CASE WHEN LEN(@Result)>0 THEN ', ' ELSE ''

  END + <>

  FROM <>

  WHERE id= @Value

  RETURN @Result

  END

  .

  I am using this in a select query which will return millions of records

  as

  SELECT id, dbo.fn_SplitByComma(id) as commaseperatedvalue

  FROM <>

  Could you please tell me whichone is the best one

  what if i use inline query instead of function..

  SELECT id,

  (

  SELECT CASE WHEN LEN(<>)>0 THEN ', ' ELSE '' END + <>

  FROM <>

  WHERE id= @Value

  )

  commaseperatedvalue

  FROM <>

  Thanks,

  Santosh

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  July 19, 2009 at 10:50 pm

  #1026729

  See the following for an answer...

  http://qa.sqlservercentral.com/articles/Test+Data/61572/

  And please stop double and triple posting.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions