Can you use a table-valued function in a SELECT column list?
0
I understand that a table-valued function is designed to appear in the FROM clause, but is there a way to include it in the column list, viz:
SELECT
x.a,
x.b,
FN(x.c),
x.d
FROM x;
... where the expected output might be something like the following?
|x.a|x.b|fn_col_1|fn_col_2|fn_col_3|x.d|
sql-server user-defined-functions asked Nov 26 '18 at 0:09
youcantryreachingmeyoucantryreachingme
1909
add a comment |
0
I understand that a table-valued function is designed to appear in the FROM clause, but is there a way to include it in the column list, viz:
SELECT
x.a,
x.b,
FN(x.c),
x.d
FROM x;
... where the expected output might be something like the following?
|x.a|x.b|fn_col_1|fn_col_2|fn_col_3|x.d|
asked Nov 26 '18 at 0:09
youcantryreachingmeyoucantryreachingme
1909
Table valued functions return tables, as the name implies. You should probably try to find another way to do this.
– Tim Biegeleisen
Nov 26 '18 at 0:20
I guess I'm after a row-valued function. I have a function which, depending on a supplied parameter, can return about 8 different values. The issue is that to calculate any 1 of the values requires calculating all of them, and that's a lot of work - so when I want a query like above, and I want to return each of those 8 values, the same (or very similar) work gets executed 8 times per row of results. Would be nice to call the function once and return the 8 values together for 1/8th of the processing. I'll look into joining TVF with the other table.
– youcantryreachingme
Nov 26 '18 at 0:24
No you can't. Try to find a different solution.
– Alvaro Parra
Nov 26 '18 at 0:27
1
Just join the TVF.
– Dale Burrell
Nov 26 '18 at 0:27
1
As mentioned; create a TVF that only returns one row and multiple fields.CROSS APPLYto it (orINNER JOIN) and include the columns in the select list. If this is a slow TVF you're going to get performance issues.
– Nick.McDermaid
Nov 26 '18 at 0:33
add a comment |
0
0
0
I understand that a table-valued function is designed to appear in the FROM clause, but is there a way to include it in the column list, viz:
SELECT
x.a,
x.b,
FN(x.c),
x.d
FROM x;
... where the expected output might be something like the following?
|x.a|x.b|fn_col_1|fn_col_2|fn_col_3|x.d|
asked Nov 26 '18 at 0:09
youcantryreachingmeyoucantryreachingme
1909
I understand that a table-valued function is designed to appear in the FROM clause, but is there a way to include it in the column list, viz:
SELECT
x.a,
x.b,
FN(x.c),
x.d
FROM x;
... where the expected output might be something like the following?
|x.a|x.b|fn_col_1|fn_col_2|fn_col_3|x.d|
asked Nov 26 '18 at 0:09
youcantryreachingmeyoucantryreachingme
1909
asked Nov 26 '18 at 0:09
youcantryreachingmeyoucantryreachingme
1909
asked Nov 26 '18 at 0:09
youcantryreachingmeyoucantryreachingme
1909
asked Nov 26 '18 at 0:09
youcantryreachingmeyoucantryreachingme
1909
asked Nov 26 '18 at 0:09
youcantryreachingmeyoucantryreachingme
1909
1909
Table valued functions return tables, as the name implies. You should probably try to find another way to do this.
– Tim Biegeleisen
Nov 26 '18 at 0:20
I guess I'm after a row-valued function. I have a function which, depending on a supplied parameter, can return about 8 different values. The issue is that to calculate any 1 of the values requires calculating all of them, and that's a lot of work - so when I want a query like above, and I want to return each of those 8 values, the same (or very similar) work gets executed 8 times per row of results. Would be nice to call the function once and return the 8 values together for 1/8th of the processing. I'll look into joining TVF with the other table.
– youcantryreachingme
Nov 26 '18 at 0:24
No you can't. Try to find a different solution.
– Alvaro Parra
Nov 26 '18 at 0:27
1
Just join the TVF.
– Dale Burrell
Nov 26 '18 at 0:27
1
As mentioned; create a TVF that only returns one row and multiple fields.CROSS APPLYto it (orINNER JOIN) and include the columns in the select list. If this is a slow TVF you're going to get performance issues.
– Nick.McDermaid
Nov 26 '18 at 0:33
add a comment |
Table valued functions return tables, as the name implies. You should probably try to find another way to do this.
– Tim Biegeleisen
Nov 26 '18 at 0:20
I guess I'm after a row-valued function. I have a function which, depending on a supplied parameter, can return about 8 different values. The issue is that to calculate any 1 of the values requires calculating all of them, and that's a lot of work - so when I want a query like above, and I want to return each of those 8 values, the same (or very similar) work gets executed 8 times per row of results. Would be nice to call the function once and return the 8 values together for 1/8th of the processing. I'll look into joining TVF with the other table.
– youcantryreachingme
Nov 26 '18 at 0:24
No you can't. Try to find a different solution.
– Alvaro Parra
Nov 26 '18 at 0:27
1
Just join the TVF.
– Dale Burrell
Nov 26 '18 at 0:27
1
As mentioned; create a TVF that only returns one row and multiple fields.CROSS APPLYto it (orINNER JOIN) and include the columns in the select list. If this is a slow TVF you're going to get performance issues.
– Nick.McDermaid
Nov 26 '18 at 0:33
Table valued functions return tables, as the name implies. You should probably try to find another way to do this.
– Tim Biegeleisen
Nov 26 '18 at 0:20
Table valued functions return tables, as the name implies. You should probably try to find another way to do this.
– Tim Biegeleisen
Nov 26 '18 at 0:20
I guess I'm after a row-valued function. I have a function which, depending on a supplied parameter, can return about 8 different values. The issue is that to calculate any 1 of the values requires calculating all of them, and that's a lot of work - so when I want a query like above, and I want to return each of those 8 values, the same (or very similar) work gets executed 8 times per row of results. Would be nice to call the function once and return the 8 values together for 1/8th of the processing. I'll look into joining TVF with the other table.
– youcantryreachingme
Nov 26 '18 at 0:24
I guess I'm after a row-valued function. I have a function which, depending on a supplied parameter, can return about 8 different values. The issue is that to calculate any 1 of the values requires calculating all of them, and that's a lot of work - so when I want a query like above, and I want to return each of those 8 values, the same (or very similar) work gets executed 8 times per row of results. Would be nice to call the function once and return the 8 values together for 1/8th of the processing. I'll look into joining TVF with the other table.
– youcantryreachingme
Nov 26 '18 at 0:24
No you can't. Try to find a different solution.
– Alvaro Parra
Nov 26 '18 at 0:27
No you can't. Try to find a different solution.
– Alvaro Parra
Nov 26 '18 at 0:27
1
1
Just join the TVF.
– Dale Burrell
Nov 26 '18 at 0:27
Just join the TVF.
– Dale Burrell
Nov 26 '18 at 0:27
1
1
As mentioned; create a TVF that only returns one row and multiple fields.
CROSS APPLY to it (or INNER JOIN) and include the columns in the select list. If this is a slow TVF you're going to get performance issues.– Nick.McDermaid
Nov 26 '18 at 0:33
As mentioned; create a TVF that only returns one row and multiple fields.
CROSS APPLY to it (or INNER JOIN) and include the columns in the select list. If this is a slow TVF you're going to get performance issues.– Nick.McDermaid
Nov 26 '18 at 0:33
add a comment |
1 Answer
1
active
oldest
votes
2
You can cross apply a TVF to a regular table e.g.
SELECT
x.a
, x.b
, x.d
, y.*
FROM x
cross apply FN(x.c) y;
answered Nov 26 '18 at 0:35
Dale BurrellDale Burrell
3,39052655
1
Thanks Dale - this has worked. A query on a large dataset which was taking 20 seconds before, takes 22 seconds now. Not sure that's a rigorous test, but for my purposes that's fine and your syntax makes for easier reading of the query.
– youcantryreachingme
Nov 26 '18 at 1:22
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53473284%2fcan-you-use-a-table-valued-function-in-a-select-column-list%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
You can cross apply a TVF to a regular table e.g.
SELECT
x.a
, x.b
, x.d
, y.*
FROM x
cross apply FN(x.c) y;
answered Nov 26 '18 at 0:35
Dale BurrellDale Burrell
3,39052655
1
Thanks Dale - this has worked. A query on a large dataset which was taking 20 seconds before, takes 22 seconds now. Not sure that's a rigorous test, but for my purposes that's fine and your syntax makes for easier reading of the query.
– youcantryreachingme
Nov 26 '18 at 1:22
add a comment |
2
You can cross apply a TVF to a regular table e.g.
SELECT
x.a
, x.b
, x.d
, y.*
FROM x
cross apply FN(x.c) y;
answered Nov 26 '18 at 0:35
Dale BurrellDale Burrell
3,39052655
1
Thanks Dale - this has worked. A query on a large dataset which was taking 20 seconds before, takes 22 seconds now. Not sure that's a rigorous test, but for my purposes that's fine and your syntax makes for easier reading of the query.
– youcantryreachingme
Nov 26 '18 at 1:22
add a comment |
2
2
2
You can cross apply a TVF to a regular table e.g.
SELECT
x.a
, x.b
, x.d
, y.*
FROM x
cross apply FN(x.c) y;
answered Nov 26 '18 at 0:35
Dale BurrellDale Burrell
3,39052655
You can cross apply a TVF to a regular table e.g.
SELECT
x.a
, x.b
, x.d
, y.*
FROM x
cross apply FN(x.c) y;
answered Nov 26 '18 at 0:35
Dale BurrellDale Burrell
3,39052655
answered Nov 26 '18 at 0:35
Dale BurrellDale Burrell
3,39052655
answered Nov 26 '18 at 0:35
Dale BurrellDale Burrell
3,39052655
answered Nov 26 '18 at 0:35
Dale BurrellDale Burrell
3,39052655
3,39052655
1
Thanks Dale - this has worked. A query on a large dataset which was taking 20 seconds before, takes 22 seconds now. Not sure that's a rigorous test, but for my purposes that's fine and your syntax makes for easier reading of the query.
– youcantryreachingme
Nov 26 '18 at 1:22
add a comment |
1
Thanks Dale - this has worked. A query on a large dataset which was taking 20 seconds before, takes 22 seconds now. Not sure that's a rigorous test, but for my purposes that's fine and your syntax makes for easier reading of the query.
– youcantryreachingme
Nov 26 '18 at 1:22
1
1
Thanks Dale - this has worked. A query on a large dataset which was taking 20 seconds before, takes 22 seconds now. Not sure that's a rigorous test, but for my purposes that's fine and your syntax makes for easier reading of the query.
– youcantryreachingme
Nov 26 '18 at 1:22
Thanks Dale - this has worked. A query on a large dataset which was taking 20 seconds before, takes 22 seconds now. Not sure that's a rigorous test, but for my purposes that's fine and your syntax makes for easier reading of the query.
– youcantryreachingme
Nov 26 '18 at 1:22
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53473284%2fcan-you-use-a-table-valued-function-in-a-select-column-list%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Table valued functions return tables, as the name implies. You should probably try to find another way to do this.
– Tim Biegeleisen
Nov 26 '18 at 0:20
I guess I'm after a row-valued function. I have a function which, depending on a supplied parameter, can return about 8 different values. The issue is that to calculate any 1 of the values requires calculating all of them, and that's a lot of work - so when I want a query like above, and I want to return each of those 8 values, the same (or very similar) work gets executed 8 times per row of results. Would be nice to call the function once and return the 8 values together for 1/8th of the processing. I'll look into joining TVF with the other table.
– youcantryreachingme
Nov 26 '18 at 0:24
No you can't. Try to find a different solution.
– Alvaro Parra
Nov 26 '18 at 0:27
1
Just join the TVF.
– Dale Burrell
Nov 26 '18 at 0:27
1
As mentioned; create a TVF that only returns one row and multiple fields.
CROSS APPLYto it (orINNER JOIN) and include the columns in the select list. If this is a slow TVF you're going to get performance issues.– Nick.McDermaid
Nov 26 '18 at 0:33