Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on May 5th, 2012  |  syntax: None  |  size: 2.70 KB  |  hits: 11  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. When multiple calls to the same UDF are in a single statement, how many times will it be called?
  2. SELECT
  3.     column1,
  4.     column2,
  5.     dbo.FUNC(column3) AS column3
  6. FROM table1
  7. WHERE dbo.FUNC(column3) >= 5
  8. ORDER BY dbo.FUNC(column3) DESC
  9.        
  10. CREATE FUNCTION dbo.FUNC1(@p1 int)
  11. RETURNS int
  12. AS
  13. BEGIN
  14.     RETURN @p1 + 1
  15. END
  16.  
  17. GO
  18.  
  19. CREATE FUNCTION dbo.FUNC2(@p1 int)
  20. RETURNS int
  21. WITH SCHEMABINDING
  22. AS
  23. BEGIN
  24.     RETURN @p1 + 1
  25. END
  26.  
  27. GO
  28. SELECT
  29.        OBJECTPROPERTYEX(OBJECT_ID('dbo.FUNC1'), 'IsDeterministic'),
  30.        OBJECTPROPERTYEX(OBJECT_ID('dbo.FUNC2'), 'IsDeterministic')
  31. GO
  32.        
  33. SELECT
  34.     dbo.FUNC1(number) AS FUNC1,
  35.     dbo.FUNC2(number) AS FUNC2
  36. FROM master..spt_values
  37. WHERE dbo.FUNC1(number) >= 5 AND dbo.FUNC2(number) >= 5
  38. ORDER BY dbo.FUNC1(number), dbo.FUNC2(number)
  39.        
  40. |--Sort(ORDER BY:([Expr1003] ASC, [Expr1004] ASC))
  41.        |--Compute Scalar(DEFINE:([Expr1003]=[test].[dbo].[FUNC1]([master].[dbo].[spt_values].[number])))
  42.             |--Filter(WHERE:([test].[dbo].[FUNC1]([master].[dbo].[spt_values].[number])>=(5) AND [Expr1004]>=(5)))
  43.                  |--Compute Scalar(DEFINE:([Expr1004]=[test].[dbo].[FUNC2]([master].[dbo].[spt_values].[number])))
  44.                       |--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]))
  45.        
  46. SELECT
  47.     FUNC1,
  48.     FUNC2
  49. FROM master..spt_values
  50. CROSS APPLY (SELECT dbo.FUNC1(number), dbo.FUNC2(number)) C(FUNC1, FUNC2)
  51. WHERE FUNC1 >= 5 AND FUNC2 >= 5
  52. ORDER BY FUNC1, FUNC2
  53.        
  54. |--Sort(ORDER BY:([Expr1003] ASC, [Expr1004] ASC))
  55.        |--Filter(WHERE:([Expr1003]>=(5)))
  56.             |--Compute Scalar(DEFINE:([Expr1003]=[test].[dbo].[FUNC1]([master].[dbo].[spt_values].[number])))
  57.                  |--Filter(WHERE:([Expr1004]>=(5)))
  58.                       |--Compute Scalar(DEFINE:([Expr1004]=[test].[dbo].[FUNC2]([master].[dbo].[spt_values].[number])))
  59.                            |--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]))
  60.        
  61. SELECT
  62.     FUNC1 + 10,
  63.     FUNC2 + 10
  64. FROM master..spt_values
  65. CROSS APPLY (SELECT dbo.FUNC1(number), dbo.FUNC2(number)) C(FUNC1, FUNC2)
  66. WHERE FUNC1 >= 5 AND FUNC2 >= 5
  67. ORDER BY FUNC1, FUNC2
  68.        
  69. |--Compute Scalar(DEFINE:([Expr1005]=[Expr1003]+(10)))
  70.        |--Sort(ORDER BY:([Expr1003] ASC, [Expr1004] ASC))
  71.             |--Filter(WHERE:([Expr1003]>=(5)))
  72.                  |--Compute Scalar(DEFINE:([Expr1003]=[test].[dbo].[FUNC1]([master].[dbo].[spt_values].[number])))
  73.                       |--Filter(WHERE:([Expr1004]>=(5)))
  74.                            |--Compute Scalar(DEFINE:([Expr1004]=[test].[dbo].[FUNC2]([master].[dbo].[spt_values].[number]), [Expr1006]=[test].[dbo].[FUNC2]([master].[dbo].[spt_values].[number])+(10)))
  75.                                 |--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]))
create a new version of this paste RAW Paste Data