December 15, 2016 at 9:06 am
I missed the title so I assumed it meant Scalar functions 🙁
You're one of those people that puts all of the details in the title of an email aren't you! 😛
December 15, 2016 at 9:13 am
george.p (12/15/2016)
I missed the title so I assumed it meant Scalar functions 🙁You're one of those people that puts all of the details in the title of an email aren't you! 😛
Well the question did say "Inline" and none of the answers were even in the realm of possibilities for a scalar function so it was pretty obvious it was not a scalar. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 15, 2016 at 9:19 am
Sean Lange (12/15/2016)
george.p (12/15/2016)
I missed the title so I assumed it meant Scalar functions 🙁You're one of those people that puts all of the details in the title of an email aren't you! 😛
Well the question did say "Inline" and none of the answers were even in the realm of possibilities for a scalar function so it was pretty obvious it was not a scalar. 🙂
Yeah I know haha I'm just playing - I'm at work and completely misread it.
I have to admit I was confused by the answers, so I decided I can't know the answer and chose one at random. I then saw the title :')
Probably a lesson in itself there haha.
December 15, 2016 at 9:24 am
george.p (12/15/2016)
Sean Lange (12/15/2016)
george.p (12/15/2016)
I missed the title so I assumed it meant Scalar functions 🙁You're one of those people that puts all of the details in the title of an email aren't you! 😛
Well the question did say "Inline" and none of the answers were even in the realm of possibilities for a scalar function so it was pretty obvious it was not a scalar. 🙂
Yeah I know haha I'm just playing - I'm at work and completely misread it.
I have to admit I was confused by the answers, so I decided I can't know the answer and chose one at random. I then saw the title :')
Probably a lesson in itself there haha.
I hear ya there!!! Once you answer a lot of these QOTD you will start looking all over the place for clues when the answers are less than obvious. The title can sometimes give some insight, other times it causes even more confusion. Oh well...QOTD, where everything is made up and the points mean nothing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 15, 2016 at 9:38 am
Sean Lange (12/15/2016)
george.p (12/15/2016)
Sean Lange (12/15/2016)
george.p (12/15/2016)
I missed the title so I assumed it meant Scalar functions 🙁You're one of those people that puts all of the details in the title of an email aren't you! 😛
Well the question did say "Inline" and none of the answers were even in the realm of possibilities for a scalar function so it was pretty obvious it was not a scalar. 🙂
Yeah I know haha I'm just playing - I'm at work and completely misread it.
I have to admit I was confused by the answers, so I decided I can't know the answer and chose one at random. I then saw the title :')
Probably a lesson in itself there haha.
I hear ya there!!! Once you answer a lot of these QOTD you will start looking all over the place for clues when the answers are less than obvious. The title can sometimes give some insight, other times it causes even more confusion. Oh well...QOTD, where everything is made up and the points mean nothing.
Whose code is it anyway? 😀
December 15, 2016 at 9:59 am
Luis Cazares (12/15/2016)
Sean Lange (12/15/2016)
george.p (12/15/2016)
Sean Lange (12/15/2016)
george.p (12/15/2016)
I missed the title so I assumed it meant Scalar functions 🙁You're one of those people that puts all of the details in the title of an email aren't you! 😛
Well the question did say "Inline" and none of the answers were even in the realm of possibilities for a scalar function so it was pretty obvious it was not a scalar. 🙂
Yeah I know haha I'm just playing - I'm at work and completely misread it.
I have to admit I was confused by the answers, so I decided I can't know the answer and chose one at random. I then saw the title :')
Probably a lesson in itself there haha.
I hear ya there!!! Once you answer a lot of these QOTD you will start looking all over the place for clues when the answers are less than obvious. The title can sometimes give some insight, other times it causes even more confusion. Oh well...QOTD, where everything is made up and the points mean nothing.
Whose code is it anyway? 😀
I figured somebody would get that reference. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 15, 2016 at 10:21 am
Any select could be surrounded by parenthesis:
(select * from sys.objects WHERE name like 'sys%')
(select * from sys.indexes WHERE name like 'c%')
December 15, 2016 at 10:21 am
Luis Cazares (12/15/2016)
I've never written an iTVF using parenthesis around the query. However, the other answers were completely wrong and the correct answer states that it can not that it must.
Actually the wording is a bit sloppy - it appears to say that the whole return statement containing the query should be in parentheses (using the usual proximity rule for parsing, "surrounded by parenthesis" must refer to the return statement not to the query, and I imagine that in American spelling "parenthesis" is the plural of parenthesis)! But I dismissed that as just sloppy phrasing, and wondered why on earth it wanted parentheses around the query (unlike you, I can't see any way of reading the question and that answer option as saying the parentheses are optional) but the other options were so thoroughly wrong that I just assumed that not saying they were optional was just another bit of sloppiness and this option was intended to be the correct one.
The quasi-BNF for inline TVFs at the CREATE FUNCTION (Transact-SQL) page is quite amusing: it gives you four options for the parentheses: have both, have neither, have just the opening one, have just the closing one. I'm fairly sure that two of those options are invalid :hehe: but I haven't bothered to try them so can't guarantee it.
Tom
December 15, 2016 at 10:36 am
TomThomson (12/15/2016)
The quasi-BNF for inline TVFs at the CREATE FUNCTION (Transact-SQL) page is quite amusing: it gives you four options for the parentheses: have both, have neither, have just the opening one, have just the closing one. I'm fairly sure that two of those options are invalid :hehe: but I haven't bothered to try them so can't guarantee it.
Would this be a better option?
-- Transact-SQL Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN { ( select_stmt ) | select_stmt }
[ ; ]
December 15, 2016 at 10:46 am
Luis Cazares (12/15/2016)
TomThomson (12/15/2016)
The quasi-BNF for inline TVFs at the CREATE FUNCTION (Transact-SQL) page is quite amusing: it gives you four options for the parentheses: have both, have neither, have just the opening one, have just the closing one. I'm fairly sure that two of those options are invalid :hehe: but I haven't bothered to try them so can't guarantee it.Would this be a better option?
-- Transact-SQL Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN { ( select_stmt ) | select_stmt }
[ ; ]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 15, 2016 at 11:26 am
To me, the wording is questionable because the answers start "This can return a table …” which means it can return something else.
The TechNet article I referenced said "Inline user-defined functions are a subset of user-defined functions that return a table data type..." meaning that only a table is returned. Only one option said “This returns a table…”
December 17, 2016 at 10:56 am
ChrisM@Work (12/15/2016)
Luis Cazares (12/15/2016)
TomThomson (12/15/2016)
The quasi-BNF for inline TVFs at the CREATE FUNCTION (Transact-SQL) page is quite amusing: it gives you four options for the parentheses: have both, have neither, have just the opening one, have just the closing one. I'm fairly sure that two of those options are invalid :hehe: but I haven't bothered to try them so can't guarantee it.Would this be a better option?
-- Transact-SQL Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN { ( select_stmt ) | select_stmt }
[ ; ]
Yes, that would be much better - very much better.
Tom
December 17, 2016 at 10:59 am
tnpich (12/15/2016)
To me, the wording is questionable because the answers start "This can return a table …” which means it can return something else.The TechNet article I referenced said "Inline user-defined functions are a subset of user-defined functions that return a table data type..." meaning that only a table is returned. Only one option said “This returns a table…”
I guess I sort of agree - after all, myw comment was about sloppy wording. but at the same time, I disagree - although "this can..." can hardly be said to reflect what happens (since it doesn't exclude anything else happening) the other three options are just plain worng, the statements are false, while this statement is true (although it desn't actually exclude anything that's false, so it's not at all useful).
Perhaps more people should be required t study mathematical logic and the semanitcs or logical algebras and even the semantics of natural languages like English before they are allowed anywhere near complicated things like function definitions :exclamationmark: (as someone who researched the semantics of mathematical logic calculi many decades ago I would like to believe that. But sadly "I like" and "I do" don't always match in the realworld :crying: - some people I've met who had done that sort of research were about as useful as a chocolate grill-pan when they tried to do computer stuff.)
Tom
December 19, 2016 at 9:07 am
Luis Cazares (12/15/2016)
TomThomson (12/15/2016)
The quasi-BNF for inline TVFs at the CREATE FUNCTION (Transact-SQL) page is quite amusing: it gives you four options for the parentheses: have both, have neither, have just the opening one, have just the closing one. I'm fairly sure that two of those options are invalid :hehe: but I haven't bothered to try them so can't guarantee it.Would this be a better option?
-- Transact-SQL Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN { ( select_stmt ) | select_stmt }
[ ; ]
Actually, that's redundant unfortunately correct (see the update at the end for why). As Carlos says:
Carlo Romagnano (12/15/2016)
Any select could be surrounded by parenthesis:
(select * from sys.objects WHERE name like 'sys%')(select * from sys.indexes WHERE name like 'c%')
In fact, even this works:
with t as ( select * from sys.tables )
((((( select * from t )))))
So the parentheses* are an aspect of the SELECT statement, not the function declaration. Furthermore, more than one set of parentheses is acceptable, as long as they are paired.
As such, they belong in the syntax for SELECT, and in fact that is the case (from https://msdn.microsoft.com/en-us/library/ms189499.aspx -- look carefully at the definition for <query_expression> -- parentheses are defined recursively there ):
-- Syntax for SQL Server and Azure SQL Database
<SELECT statement> ::=
[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ]
<query_expression>
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
[ ,...n ] ]
[ <FOR Clause>]
[ OPTION ( <query_hint> [ ,...n ] ) ]
<query_expression> ::=
{ <query_specification> | ( <query_expression> ) }
[ { UNION [ ALL ] | EXCEPT | INTERSECT }
<query_specification> | ( <query_expression> ) [...n ] ]
<query_specification> ::=
SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
< select_list >
[ INTO new_table ]
[ FROM { <table_source> } [ ,...n ] ]
[ WHERE <search_condition> ]
[ <GROUP BY> ]
[ HAVING < search_condition > ]
So the function declaration should just be:
-- Transact-SQL Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN select_stmt
[ ; ]
as the parentheses are defined in the <query expression> section of the SELECT syntax.
UPDATE: Except optional parentheses are also defined for the function declaration. This would not work if that were not the case, as the parentheses around <query expression> come after the WITH clause:
create or alter function dbo.test()
returns table
as
return (
with t as ( select * from sys.tables )
select * from t
)
So Luis's syntax is necessary to allow for these completely unnecessary optional parentheses.
* Yes, even here in the US, the plural of parenthesis is parentheses.
December 22, 2016 at 2:53 am
Easy one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply