Reaping the benefits of the Window functions in T-SQL

Question

Reaping the benefits of the Window functions in T-SQL

Viewing 13 posts - 46 through 57 (of 57 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Eirikur Eiriksson wrote:

Thom A wrote:
Just noticed this when I was putting this on a new instance, but in the .sql file the WITH starts with a leading ;, which actually breaks the DDL statement. Suppose that's another reason to educate people that ; isn't a "beginningator". 🙂
Here we begin(ninator) again 😉
😎

Well, it is one of my pet peeves. ??

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

BWAAAA-HAAAA!!!! There's enough bad code out there for me to be really peeved about that I just don't care where the semi-colon is so long as it's there. In fact, my pet peeve is that semi-colons are required anywhere! 😀 They didn't use to be required at all and I use to intentionally avoid all languages that did require them (and a bazillion braces) and mostly still do. 😀

--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)

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 3

Thom A wrote:

Just noticed this when I was putting this on a new instance, but in the .sql file the WITH starts with a leading ;, which actually breaks the DDL statement. Suppose that's another reason to educate people that ; isn't a "beginningator". 🙂

That's because it's part of the RETURN statement. If you'd have put the semicolon on the end of the RETURN line instead, it it still would have failed.

corey lawson Hall of Fame Points: 3773 More actions · Answer 4

Speaking of adds, it'd really be nice if they just figure out a decent built-in to expose the .Net regular expression functions.

That'd save convincing the DBAs to turn on SQLCLR, and then convince them to add the DLL you built with the .Net regex functions you might want to use...

I did work somewhere in the past where the DBA had done that, and life was Good.

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

I'm one of those DBAs that will allow the use of CLR. I'm just not one to allow the use of CLR that doesn't make sense. I had one guy take me all the way up to the CIO because, after reviewing the code, said I wouldn't deploy it and was never given the chance to explain why until we got to the CIO.

The code the developer insisted he needed did a simple "modulo". You know... like the one readily available in SQL with the "%" operator. 😀

If you consider the huge mistake the supposed professionals at MS made in using FORMAT (wicked performance problem there) and what they did with things like STRING_SPLIT(), you have to forgive DBAs that don't allow CLR.

--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)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

I agree that CLRs have they´re usage, somewhat more like a stuffed toy a pram.

😎

CLRs are not without limitations and although some can be blazing fast, limitations like data streaming interruptions are sometimes a real PITA to deal with. Tried to use several different zip-unzip CLRs for a project, all of those turned out to be unreliable, returning partial results, which does not go well with any set based approach, just like MARS.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

Jeff Moden wrote:

I'm one of those DBAs that will allow the use of CLR. I'm just not one to allow the use of CLR that doesn't make sense. I had one guy take me all the way up to the CIO because, after reviewing the code, said I wouldn't deploy it and was never given the chance to explain why until we got to the CIO.
The code the developer insisted he needed did a simple "modulo". You know... like the one readily available in SQL with the "%" operator. 😀
If you consider the huge mistake the supposed professionals at MS made in using FORMAT (wicked performance problem there) and what they did with things like STRING_SPLIT(), you have to forgive DBAs that don't allow CLR.

Almost all MODULO calculations are based on the principle of integer division that is IIRC slower in a CLR than natively in SQL.

😎

BTW, integer division in SQL is generally slower than modulo 😉

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 8

A little bit of history here. The ANSI/ISO Standards committee decided that the grammar for SQL would be LALR(1). One of the committee members had access to a really good parser generator and would check the syntax after every meeting. We would then change BNF to assure this. What it means is that the semi-colon is a Terminator and not an announcer. Sybase changed the first grammars that we had for SQL so that they did not need a Terminator. A new line was sufficient and the semi-colon was optional. (I have always used the Terminator, because I wanted portable code and because of the standards freak) However, as Microsoft has come more and more into compliance with ANSI/ISO standards, this is not always possible.

Please post DDL and follow ANSI/ISO standards when asking for help.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 9

jcelko212 32090 wrote:

A little bit of history here. The ANSI/ISO Standards committee decided that the grammar for SQL would be LALR(1). One of the committee members had access to a really good parser generator and would check the syntax after every meeting. We would then change BNF to assure this. What it means is that the semi-colon is a Terminator and not an announcer. Sybase changed the first grammars that we had for SQL so that they did not need a Terminator. A new line was sufficient and the semi-colon was optional. (I have always used the Terminator, because I wanted portable code and because of the standards freak) However, as Microsoft has come more and more into compliance with ANSI/ISO standards, this is not always possible.

Some of us might remember ALGOL 58/60, IIRC the first high level language to implement semicolon as a statement terminator. Many languages were based on ALGOL but the first SQL dialect that was influenced by the syntax was PL.

😎

Similar to the comma first vs. comma last argument, announcer or terminator should not be the points of focus, those are rather how one constructs the code. I prefer terminator (for the previous statement) as the announcer of a statement just as each columnar definition in a query starting with a comma, safes me lot of time when using my code repositories to construct the code.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Eirikur Eiriksson wrote:

jcelko212 32090 wrote:
A little bit of history here. The ANSI/ISO Standards committee decided that the grammar for SQL would be LALR(1). One of the committee members had access to a really good parser generator and would check the syntax after every meeting. We would then change BNF to assure this. What it means is that the semi-colon is a Terminator and not an announcer. Sybase changed the first grammars that we had for SQL so that they did not need a Terminator. A new line was sufficient and the semi-colon was optional. (I have always used the Terminator, because I wanted portable code and because of the standards freak) However, as Microsoft has come more and more into compliance with ANSI/ISO standards, this is not always possible.
Some of us might remember ALGOL 58/60, IIRC the first high level language to implement semicolon as a statement terminator. Many languages were based on ALGOL but the first SQL dialect that was influenced by the syntax was PL.
😎
Similar to the comma first vs. comma last argument, announcer or terminator should not be the points of focus, those are rather how one constructs the code. I prefer terminator (for the previous statement) as the announcer of a statement just as each columnar definition in a query starting with a comma, safes me lot of time when using my code repositories to construct the code.

I remember learning Algol during a teacher strike while I was in High School back when dinosaurs still roamed the land. I actually liked the language.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 11

>> I remember learning Algol during a teacher strike while I was in High School back when dinosaurs still roamed the land. I actually liked the language. <<

I learned Fortran in high school, as part of an IBM project to educate kids over the summer. But when I got to Georgia Tech, they were using Burroughs 5000 computers. This machine was written in Algol. I mean, there was no assembly language, just a dialect of Algol 60. It was also a stack architecture that does very well for block-structured languages. At some point, the senior project for everybody in the fledgling computer science department (which was under electrical engineering at the time), was to add features to GTL (Georgia Tech Language). The result was a monster with incredibly bad performance. Someone finally optimized it as his senior project. And I have to agree with Dykstra about Algol; it was a better language than almost all of what followed it.

Please post DDL and follow ANSI/ISO standards when asking for help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

jcelko212 32090 wrote:

>> I remember learning Algol during a teacher strike while I was in High School back when dinosaurs still roamed the land. I actually liked the language. <<
I learned Fortran in high school, as part of an IBM project to educate kids over the summer. But when I got to Georgia Tech, they were using Burroughs 5000 computers. This machine was written in Algol. I mean, there was no assembly language, just a dialect of Algol 60. It was also a stack architecture that does very well for block-structured languages. At some point, the senior project for everybody in the fledgling computer science department (which was under electrical engineering at the time), was to add features to GTL (Georgia Tech Language). The result was a monster with incredibly bad performance. Someone finally optimized it as his senior project. And I have to agree with Dykstra about Algol; it was a better language than almost all of what followed it.

I learned BASIC, Fortran, Algol, COBOL, and Focal while in High School. COBOL was actually a college level course through the UCCS Extension Service (or what ever it was called back then).

Worked on Burroughs systems in the Air Force as a computer operator. It was a good system that an operator actually had control over instead of being run by the computer like the Honeywell 6080's I worked with at NORAD.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 13

jcelko212 32090 wrote:

>> I remember learning Algol during a teacher strike while I was in High School back when dinosaurs still roamed the land. I actually liked the language. <<
I learned Fortran in high school, as part of an IBM project to educate kids over the summer. But when I got to Georgia Tech, they were using Burroughs 5000 computers. This machine was written in Algol. I mean, there was no assembly language, just a dialect of Algol 60. It was also a stack architecture that does very well for block-structured languages. At some point, the senior project for everybody in the fledgling computer science department (which was under electrical engineering at the time), was to add features to GTL (Georgia Tech Language). The result was a monster with incredibly bad performance. Someone finally optimized it as his senior project. And I have to agree with Dykstra about Algol; it was a better language than almost all of what followed it.

There is a long list of programming languages like the GTL that eventually failed, most of those being too academic and less commercially adoptable.

😎

What I really like about ALGOL is that it is really good when writing pseudo code, very "human-readable", in fact the pseudo code in this article is based on ALGOL 😉