Stairway to T-SQL: Beyond The Basics Level 8: Coding Shortcuts using += and -= Operators

Question

Stairway to T-SQL: Beyond The Basics Level 8: Coding Shortcuts using += and -= Operators

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

Thanks for the response, Joe.

Except for the part where you say "do not have to write with the full ANSI/ISO current SQL standard", I have to continue to respectfully disagree. As a bit of a sidebar, the term "thunk" reminds me of the noise that performance frequently makes when an ORM incorrectly creates "interface code". 😀

Consider the following post by Alexander Kuznetsov

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/02/24/writing-ansi-standard-sql-is-not-practical.aspx

Ignoring the fact that writing a loop in ORACLE is still worse than writing something set-based, that simple stored procedure epitomizes exactly what I'm talking about. To pass a parameter to a stored procedure, you have to have variables as arguments. Various engines make variable assignments using different methods and how the variables are declared and named are frequently quite different, as well. As Alex says in his short article, I'm pretty sure that even this simple stored procedure cannot be written to be portable even if you were to remove the need for any output.

Without getting into all the things that I (and I'm not alone here) necessarily need to do on a daily basis that wouldn't stand a chance of being ANSI/ISO compliant, which also means no chance of being portable, I don't even consider whether code is portable or not when I'm writing SQL. Rather, I exploit even proprietary functionality of whatever database engine I'm using.

If you think even simple code can be written to be truly portable without it being generated by the likes of an ORM, then correctly answer the simple questions posed in the following link. 😛

https://groups.google.com/forum/#!topic/fido7.rdbms.oracle/NRNrTf6p6rE

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joe Celko SSCertifiable Points: 5749 More actions · Answer 2

. As a bit of a sidebar, the term "thunk" reminds me of the noise that performance frequently makes when an ORM incorrectly creates "interface code". BigGrin

I am not really sure where the term "thunk" came from. I know that it first showed up in Algol 60 because it was the first language to interview various kinds of parameter passing. There was "call by name" and "call by value" and then we added, "call by reference" in PL/1 and finally call by need"

>> Consider the following post by Alexander Kuznetsov

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/02/24/writing-ansi-standard-sql-is-not-practical.aspx <<

I noticed that one of his commenters talked about having a dozen different databases and supporting with portable code. But I still think the best example is the TPC (transaction processing Council) test sets. They have to be ported from ANSI standard to quite a few target databases and the vendors are able to translate it in such a way that they get the best performance out of their products. It also been doing this for quite a few years, so we have a lot of evidence that it is quite that possible to get both portability and performance.

To pass a parameter to a stored procedure, you have to have variables as arguments. Various engines make variable assignments using different methods and how the variables are declared and named are frequently quite different, as well. As Alex says in his short article, I'm pretty sure that even this simple stored procedure cannot be written to be portable even if you were to remove the need for any output.

Yet somehow DB2 version 11 is able to pass them around without any problems. Have you noticed vendors have been trying to get over to ANSI standards for years? This is why SQL Server now has DATE and DATETIME2(n) as a synonym for the ANSI standard TIMESTAMP(n).

And why Oracle VARCHAR2(n) was added to that mess they sell as a database.

I don't even consider whether code is portable or not when I'm writing SQL. Rather, I exploit even proprietary functionality of whatever database engine I'm using. [/QUOTE

And this is where I make my living 🙂 a lot of my customers have gotten trapped in proprietary code, often on smaller SQL products like SQL Server, get successful, and have to move it to a mainframe or other platform; very often these days, several other platforms.

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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

CELKO (7/25/2016)
And this is where I make my living 🙂 a lot of my customers have gotten trapped in proprietary code, often on smaller SQL products like SQL Server, get successful, and have to move it to a mainframe or other platform; very often these days, several other platforms.

Interesting! Did you use any stored procedures?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joe Celko SSCertifiable Points: 5749 More actions · Answer 4

Yes, lots of them. I am scared of SQL injection (https://xkcd.com/327/), but more than that, procedures make portability much easier. If I have done a good job, and follow the old software engineering principles (one and only one entry point, one and only one exit point, one and only one task that can be defined with "<verb ><object>" sentences, etc. all that coupling cohesion stuff learned in 1970 software engineering revolution really do work!

I tend to go one step further, and try to make everything look like SQL/PSM. I prefix arguments with "@in_<data element name>" and "@out_<data element name>", then I add the deterministic, "modifies SQL" clauses as a comment at the front of the procedure body.

Then there is the usual required boilerplate; the author, the last modification date, line by line and whatever else the shop (read: DOD regulations) requires. Back in my old Fortran days one of our tricks was to use a Southern Bell phone number which always produced a busy signal as our home phone. Sorry, but back then computers were so expensive and programmers were so cheap that we were expected to be on call 24/7.

Another trick I use which will thing is really weird, is to write two versions of a procedure. One is in the dialect of the current version I am compiling. The other version is a comment in ANSI/ISO standard SQL can be uncommented when the product gets up to snuff. For example, I wrote a lot of in fixed join comments when the Sybase SQL server world was still on the extended equality outer joins. I also put comments in the DDL to get rid of the old bit datatypes (remember they were originally bits and not a numeric data type like they are today in T SQL dialect). One of the nicest, compliments I got from the client was at all they had to do to migrate package from the in fixed joints came in was to delete the original code and uncommented my work.

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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 5

Thanks, Joe. I'm right there with you on the use of stored procedures.

CELKO (7/31/2016)
I tend to go one step further, and try to make everything look like SQL/PSM. I prefix arguments with "@in_<data element name>" and "@out_<data element name>", then I add the deterministic, "modifies SQL" clauses as a comment at the front of the procedure body.

Are you saying that your argument names that are prefixed with "@" are portable to Oracle and MySQL?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)