September 1, 2016 at 7:45 pm
No, functions are used so that its code can be re-used. Seriously, your obsession with labeling everyone a Luddite gets insufferable
I did not call anybody a Luddite; I simply pointed out that this is not how we intend SQL to be written. Please quit looking for micro aggressions.
The purpose of a stored procedure is to encapsulate SQL for reuse. The UDF in T-SQL dialect simply hides things rather than making them easy to maintain. Remember that SQL is a declarative language, so we prefer to write expressions and statements instead.
If you work for the early Fortran, BASIC or COBOL, you would see the same structure in those languages (in the case of COBOL, there would be a perform verb). It is designed for procedural mindset. An SQL programmer with use a CTE today. Unfortunately, T-SQL simply uses them as in-line macros; other products make a decision as to whether to materialize them or not. In theory, a good optimizer would do this under the covers, it is a lot handier if the programmer tells the engine what to do.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
September 1, 2016 at 7:55 pm
It might be true that a CLR, like a cursor could be the best solution for some particular problem. But I have to go along with Jeff; I found that CLR functions simply add to complexity, make maintaining code very difficult (you know all 42 the CLR languages? Me neither!), And since even datatypes do not agree among languages where the overhead of just trying to pass data in a query.
At one point I wanted to get the Student t-distribution in SQL query. My first impulse was to do a CLR in some language (I think I can still read Fortran, but these days R might be the language of choice). It has an integral in its definition, so there is no SQL function for that (neither should there be; SQL is for data not complicated math).
The solution was to remember that SQL is a database language, so I created a lookup table that was good enough for what I wanted and could be shared. I have come to the conclusion that a lot of complicated looking problems can be solved lookup tables. I do not have to worry about data type conversions among languages, I do not have to worry about precision and rounding the computation, and a lot of times if it lookup table exists somewhere on the Internet. I can cut-and-paste it and turn it into a table constructor insertion statement.
How many times on this forum have we seen posters, thinking they need a cursor and found out they could do it several orders of magnitude faster in pure SQL? Can you give me example of something that has to be done with the CLR instead appear SQL?
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
September 2, 2016 at 6:04 pm
CELKO (9/1/2016)
No, functions are used so that its code can be re-used. Seriously, your obsession with labeling everyone a Luddite gets insufferable
I did not call anybody a Luddite; I simply pointed out that this is not how we intend SQL to be written. Please quit looking for micro aggressions.
The purpose of a stored procedure is to encapsulate SQL for reuse. The UDF in T-SQL dialect simply hides things rather than making them easy to maintain. Remember that SQL is a declarative language, so we prefer to write expressions and statements instead.
If you work for the early Fortran, BASIC or COBOL, you would see the same structure in those languages (in the case of COBOL, there would be a perform verb). It is designed for procedural mindset. An SQL programmer with use a CTE today. Unfortunately, T-SQL simply uses them as in-line macros; other products make a decision as to whether to materialize them or not. In theory, a good optimizer would do this under the covers, it is a lot handier if the programmer tells the engine what to do.
Joe, you should avoid making bizarre (and unfounded) claims like this claim that functions are non-declarative and appropriate only for procedural languages. By making such claims you will get yourself laughed at in any academic or industrial context here people are seriously involved in the production of systems using declarative languages.
There are far more declarative languages which are fundamentally functional than declarative languages that are not functional. How much of Miranda or Hope+ or any of the ML languages or Haskell would be left if functions were not allowed in them? Precisely nothing, those languages could not exist without functions because functions are are the fundamental concept on which those languages are based. The ML variants are the most widely used declarative languages, although Haskell is catching up, and Miranda (and to a lesser extent Hope+) were, in their day, well known declarative functional languages.
People who (unlike you) actually work with declarative languages tend to insist that referential transparency is enforced except in some very rare constructs, and that tends to rule out languages like SQL since they provide almost no referential transparency at all; they also tend to insist that operations have determined semantics, and that too rules out SQL (which gives implementatons far to much freedom, so that the semantics is often indeterminate).
Tom
September 5, 2016 at 12:55 pm
A declarative language needs orthogonality. Unless something changed in 2016, you cannot pass functions as parameters to other functions in UDFs. In T-SQL dialect, we basically have a copy of the old Fortran/BASIC "FN-" in-line macros which only goes down one level. A declarative language also needs to guarantee there are no side effects. Again, T-SQL does not enforce this. Basically, we are after. What a mathematician are called primitive recursive functions.
SQL is pretty good about orthogonality; an expression that returns a particular data type scalar value can be used anywhere that particular data type scalar value is used. The ANSI/ISO standard PSM is much better about this than most languages.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
September 15, 2016 at 1:48 pm
Joe asked: "Can you give me example of something that has to be done with the CLR instead appear SQL?"
A while back I did a C# CLR function in SQL 2012 for large JSON string parsing that is significantly faster, more reliable and more maintainable than anything that could be done with pure SQL.
Hyperbolic statements that functions (and especially CLR functions) are inherently bad make me laugh. I have been away for a while as I transitioned from full-time SQL development to full stack but this is something I missed.
September 15, 2016 at 2:16 pm
Ben Teraberry (9/15/2016)
Joe asked: "Can you give me example of something that has to be done with the CLR instead appear SQL?"A while back I did a C# CLR function in SQL 2012 for large JSON string parsing that is significantly faster, more reliable and more maintainable than anything that could be done with pure SQL.
Hyperbolic statements that functions (and especially CLR functions) are inherently bad make me laugh. I have been away for a while as I transitioned from full-time SQL development to full stack but this is something I missed.
I'm not hating on CLRs but I am pretty convinced that you can write faster code using T-SQL for any kind of string analysis and/or manipulation.
Do you have a sample of the JSON strings and what needs to be done with them?
-- Itzik Ben-Gan 2001
September 15, 2016 at 3:43 pm
Alan,
In my experience, properly written C# is always going to be faster splitting large strings and parsing text than t-sql is. I know at one point I tested a string split CLR function Paul White put together against Jeff's optimized t-sql function and consistently found they were virtually the same on millions of splits while the input string was fairly narrow ... say 1 to 30 characters. As the inputs got larger the CLR function pulled ahead. For us, it wasn't enough difference to hassle with the CLR, especially since the vast majority of time we are splitting smaller inputs. However, if I had a need to do sting splits on large strings and needed the highest possible performance I would use the CLR.
Take a complex JSON object with nested objects and arrays and handle that with t-sql and I do not think you are going to see very good things. If you have a high performance t-sql JSON parser I would love to see it and I do not mean that snidely. When we did comparison testing with .Net, the best t-sql sample I could find online and another t-sql solution built by another one of our devs the performance difference was astoundingly in favor of the CLR. If you have awesome code you can share I would love to test that against the CLR.
I love well written t-sql and I think many developers reach for other solutions instead of spending the time to refine and optimize, but t-sql is not the most performant solution for all tasks. I have been told that the SQL team at Microsoft was frustrated and disappointed in the community's reluctance to embrace CLR and cut resources from their planned improvements for it.
Our data has a bunch of person info that I don't want to clean to post but you can get an idea of the challenge by use a nice little JSON generator at json-generator.com[/url]. The object of the required function is to return the property of a specific object requested ... so for instance say you want the name of the first friend from the third object in the array. And at any point, there could be a query for any other property value in a similar manner. Obviously our objects are different but you should get the idea.
July 6, 2017 at 11:12 am
CELKO - Thursday, September 1, 2016 7:55 PMIt might be true that a CLR, like a cursor could be the best solution for some particular problem. But I have to go along with Jeff; I found that CLR functions simply add to complexity, make maintaining code very difficult (you know all 42 the CLR languages? Me neither!), And since even datatypes do not agree among languages where the overhead of just trying to pass data in a query.At one point I wanted to get the Student t-distribution in SQL query. My first impulse was to do a CLR in some language (I think I can still read Fortran, but these days R might be the language of choice). It has an integral in its definition, so there is no SQL function for that (neither should there be; SQL is for data not complicated math). The solution was to remember that SQL is a database language, so I created a lookup table that was good enough for what I wanted and could be shared. I have come to the conclusion that a lot of complicated looking problems can be solved lookup tables. I do not have to worry about data type conversions among languages, I do not have to worry about precision and rounding the computation, and a lot of times if it lookup table exists somewhere on the Internet. I can cut-and-paste it and turn it into a table constructor insertion statement. How many times on this forum have we seen posters, thinking they need a cursor and found out they could do it several orders of magnitude faster in pure SQL? Can you give me example of something that has to be done with the CLR instead appear SQL?
Yes. Real regular expressions. (not SQL 'LIKE', 'PATINDEX', etc.). This is quite good for a specific use case - address standardizations and parsing.
With T-SQL built-in string and lookup functions, there's just no reasonable equivalent to the regex "^" and "$" anchor characters, the "word boundary" anchors, etc. And we're not even talking about look-ahead and look-behind, grouping, etc.
I'm sure it's theoretically possible to write a program to decompose a real regular expression into a bunch of T-SQL functions, I do not want to try and do so, nor to try and modify the results after it's been generated.
It'd be so nice for MS to just include an assembly with the .Net regex methods out-of-the-box in the CLR space.
Sure, there's Melissa Data for this, too. But that doesn't help so much with non-US addresses...
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply