Post Reopened by Paul White♦ sql-server

occurred Apr 28, 2022 at 9:52

added 35 characters in body; edited tags; edited title

Source Link

edited Apr 28, 2022 at 9:51

90.3k
30
424
663

UDF and Builtbuilt-in Functionsfunction behavior when used in a Sql Query

Take the following queries:

DECLARE @X VARCHAR(200) = '1,2,3,4'

SELECT
    *,
    dbo.aUserDefinedScalarFunction(4) AS ScalarValue
FROM
   MyTable T
   INNER JOIN dbo.aUserDefineTableFunction(@X) A ON T.SomeID=A.SomeID
WHERE 
  (T.ID1 IS NULL OR T.ID1 IN (SELECT [value] FROM STRING_SPLIT(@X,',')))
  AND
  (T.ID2 IS NULL OR T.ID2 IN (SELECT Value FROM dbo.MySplitterFunction(@X))

I normally create Indexedindexed #tempTables for the whereWHERE conditions above, I have found that to be more performantperform better on large datasets. However, I still can't find definitive answers to the following questions:

Will the query analyzer optimize aUserDefinedScalarFunction(4) As ScalarValue or does it get evaluated for each record?
Will the INNER JOIN dbo.aUserDefineTableFunction(@X) get materialized into a temp table once or will it be executed for each record? The function returns table (not a table variable).
Does the result of SELECT [value] FROM STRING_SPLIT(@X,',') get optimized or is it evaluated for each comparison?
Does the result of SELECT Value FROM dbo.MySplitterFunction(@X) get optimized or is it evaluated during each comparison?

Thanks.

UDF and Built-in Functions behavior when used in a Sql Query

Take the following queries

DECLARE @X VARCHAR(200) = '1,2,3,4'

SELECT
    *,
    dbo.aUserDefinedScalarFunction(4) AS ScalarValue
FROM
   MyTable T
   INNER JOIN dbo.aUserDefineTableFunction(@X) A ON T.SomeID=A.SomeID
WHERE 
  (T.ID1 IS NULL OR T.ID1 IN (SELECT [value] FROM STRING_SPLIT(@X,',')))
  AND
  (T.ID2 IS NULL OR T.ID2 IN (SELECT Value FROM dbo.MySplitterFunction(@X))

I normally create Indexed #tempTables for the where conditions above, I have found that to be more performant on large datasets. However, I still can't find definitive answers to the following questions:

Will the query analyzer optimize aUserDefinedScalarFunction(4) As ScalarValue or does it get evaluated for each record?
Will the INNER JOIN dbo.aUserDefineTableFunction(@X) get materialized into a temp table once or will it be executed for each record?
Does the result of SELECT [value] FROM STRING_SPLIT(@X,',') get optimized or is it evaluated for each comparison?
Does the result of SELECT Value FROM dbo.MySplitterFunction(@X) get optimized or is it evaluated during each comparison?

Thanks.

UDF and built-in function behavior

Take the following queries:

DECLARE @X VARCHAR(200) = '1,2,3,4'

SELECT
    *,
    dbo.aUserDefinedScalarFunction(4) AS ScalarValue
FROM
   MyTable T
   INNER JOIN dbo.aUserDefineTableFunction(@X) A ON T.SomeID=A.SomeID
WHERE 
  (T.ID1 IS NULL OR T.ID1 IN (SELECT [value] FROM STRING_SPLIT(@X,',')))
  AND
  (T.ID2 IS NULL OR T.ID2 IN (SELECT Value FROM dbo.MySplitterFunction(@X))

I normally create indexed #tempTables for the WHERE conditions above, I have found that to perform better on large datasets. However, I still can't find definitive answers to the following questions:

Will the query analyzer optimize aUserDefinedScalarFunction(4) As ScalarValue or does it get evaluated for each record?
Will the INNER JOIN dbo.aUserDefineTableFunction(@X) get materialized into a temp table once or will it be executed for each record? The function returns table (not a table variable).
Does the result of SELECT [value] FROM STRING_SPLIT(@X,',') get optimized or is it evaluated for each comparison?
Does the result of SELECT Value FROM dbo.MySplitterFunction(@X) get optimized or is it evaluated during each comparison?

3

duplicates list edited from Does SQL Server cache the result of a multi-statement table-valued function? to Changing metadata changes query output issue [duplicate], Is there any benefit to SCHEMABINDING a function beyond Halloween Protection?, Does SQL Server cache the result of a multi-statement table-valued function?

Link

edited Apr 28, 2022 at 9:39

Paul White ♦

90.3k
30
424
663

Post Closed as "Duplicate" by mustaccio, Paul White♦ sql-server

Does SQL Server cache the result of a multi-statement table-valued function?

occurred Apr 28, 2022 at 9:04

added 2 characters in body

Source Link

edited Apr 27, 2022 at 18:33

Ross Bush

629
1
5
18

Take the following queries

DECLARE @X VARCHAR(200) = '1,2,3,4'

SELECT
    *,
    dbo.aUserDefinedScalarFunction(4) AS ScalarValue
FROM
   MyTable T
   INNER JOIN dbo.aUserDefineTableFunction(@X) A ON T.SomeID=A.SomeID
WHERE 
  (T.ID1 IS NULL OR T.ID1 IN (SELECT [value] FROM STRING_SPLIT(@X,',')))
  AND
  (T.ID2 IS NULL OR T.ID2 IN (SELECT Value FROM dbo.MySplitterFunction(@X))

I normally create Indexed #tempTables for the where conditions above, I have found that to be more performant on large datasets. However, I still can't find definitive answers to the following questions:

Will the query analyzer optimize aUserDefinedScalarFunction(4) As ScalarValue or does it get evaluated for each record?
Will the INNER JOIN dbo.aUserDefineTableFunction(@X) get optimized tomaterialized into a table'esque resulttemp table once or will it be executed for each record?
Does the result of SELECT [value] FROM STRING_SPLIT(@X,',') get optimized or is it evaluated for each comparison?
Does the result of SELECT Value FROM dbo.MySplitterFunction(@X) get optimized or is it evaluated during each comparison?

Thanks.

Take the following queries

DECLARE @X VARCHAR(200) = '1,2,3,4'

SELECT
    *,
    dbo.aUserDefinedScalarFunction(4) AS ScalarValue
FROM
   MyTable T
   INNER JOIN dbo.aUserDefineTableFunction(@X) A ON T.SomeID=A.SomeID
WHERE 
  (T.ID1 IS NULL OR T.ID1 IN (SELECT [value] FROM STRING_SPLIT(@X,',')))
  AND
  (T.ID2 IS NULL OR T.ID2 IN (SELECT Value FROM dbo.MySplitterFunction(@X))

I normally create Indexed #tempTables for the where conditions above, I have found that to be more performant on large datasets. However, I still can't find definitive answers to the following questions:

Will the query analyzer optimize aUserDefinedScalarFunction(4) As ScalarValue or does it get evaluated for each record?
Will the INNER JOIN dbo.aUserDefineTableFunction(@X) get optimized to a table'esque result or will it be executed for each record?
Does the result of SELECT [value] FROM STRING_SPLIT(@X,',') get optimized or is it evaluated for each comparison?
Does the result of SELECT Value FROM dbo.MySplitterFunction(@X) get optimized or is it evaluated during each comparison?

Thanks.

Take the following queries

DECLARE @X VARCHAR(200) = '1,2,3,4'

SELECT
    *,
    dbo.aUserDefinedScalarFunction(4) AS ScalarValue
FROM
   MyTable T
   INNER JOIN dbo.aUserDefineTableFunction(@X) A ON T.SomeID=A.SomeID
WHERE 
  (T.ID1 IS NULL OR T.ID1 IN (SELECT [value] FROM STRING_SPLIT(@X,',')))
  AND
  (T.ID2 IS NULL OR T.ID2 IN (SELECT Value FROM dbo.MySplitterFunction(@X))

I normally create Indexed #tempTables for the where conditions above, I have found that to be more performant on large datasets. However, I still can't find definitive answers to the following questions:

Will the query analyzer optimize aUserDefinedScalarFunction(4) As ScalarValue or does it get evaluated for each record?
Will the INNER JOIN dbo.aUserDefineTableFunction(@X) get materialized into a temp table once or will it be executed for each record?
Does the result of SELECT [value] FROM STRING_SPLIT(@X,',') get optimized or is it evaluated for each comparison?
Does the result of SELECT Value FROM dbo.MySplitterFunction(@X) get optimized or is it evaluated during each comparison?

Thanks.

Source Link

asked Apr 27, 2022 at 18:29

Ross Bush

629
1
5
18

UDF and Built-in Functions behavior when used in a Sql Query

Take the following queries

DECLARE @X VARCHAR(200) = '1,2,3,4'

SELECT
    *,
    dbo.aUserDefinedScalarFunction(4) AS ScalarValue
FROM
   MyTable T
   INNER JOIN dbo.aUserDefineTableFunction(@X) A ON T.SomeID=A.SomeID
WHERE 
  (T.ID1 IS NULL OR T.ID1 IN (SELECT [value] FROM STRING_SPLIT(@X,',')))
  AND
  (T.ID2 IS NULL OR T.ID2 IN (SELECT Value FROM dbo.MySplitterFunction(@X))

I normally create Indexed #tempTables for the where conditions above, I have found that to be more performant on large datasets. However, I still can't find definitive answers to the following questions:

Will the query analyzer optimize aUserDefinedScalarFunction(4) As ScalarValue or does it get evaluated for each record?
Will the INNER JOIN dbo.aUserDefineTableFunction(@X) get optimized to a table'esque result or will it be executed for each record?
Does the result of SELECT [value] FROM STRING_SPLIT(@X,',') get optimized or is it evaluated for each comparison?
Does the result of SELECT Value FROM dbo.MySplitterFunction(@X) get optimized or is it evaluated during each comparison?

Thanks.

sql-server

Stack Exchange Network

Return to Question

UDF and Builtbuilt-in Functionsfunction behavior when used in a Sql Query

UDF and Built-in Functions behavior when used in a Sql Query

UDF and built-in function behavior

UDF and Built-in Functions behavior when used in a Sql Query