Modern SQL in PostgreSQL

Video in TIB AV-Portal: Modern SQL in PostgreSQL

Formal Metadata

Modern SQL in PostgreSQL
Still using Windows 3.1? So why stick to SQL-92?
Title of Series
Number of Parts
CC Attribution - ShareAlike 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this license.
Release Date
Production Place
Ottawa, Canada

Content Metadata

Subject Area
A lot has changed since SQL:92 SQL has gone out of fashion lately --- partly due to the NoSQL movement, but mostly because SQL is often still used like 20 years ago. As a matter of fact, the SQL standard continued to evolve during the past decades resulting in the current release of 2011. In this session, we will go through the most important additions since the widely known SQL-92, explain how they work and how PostgreSQL extends them. We will cover common table expressions and window functions in detail and have a very short look at the temporal features of SQL:2011 and the related features of PostgreSQL.

Related Material

Context awareness Standard deviation Computer animation Multiplication sign Revision control Software testing Function (mathematics) Distance Asynchronous Transfer Mode Window function Subset
Normal-form game Addition Word Theory of relativity Computer animation Query language Statement (computer science) Endliche Modelltheorie Port scanner Table (information) Mereology Algebra
Revision control Slide rule Computer animation Sine Code Personal digital assistant Hecke operator Multilateration
Table (information) Limit (category theory) Function (mathematics) Parameter (computer programming) Limit (category theory) Semantics (computer science) Product (business) Product (business) Category of being Order (biology) Escape character Computer animation Personal digital assistant Logic Order (biology) Statement (computer science) Ranking Data Encryption Standard Table (information) Row (database)
Axiom of choice Pay television Source code 1 (number) Planning Bit Limit (category theory) Port scanner Theory Twitter Order (biology) Causality Software Query language Personal digital assistant Hash function Order (biology) Data Encryption Standard Quicksort Multiplication Row (database)
Pay television Multiplication sign Mathematical singularity 1 (number) Planning Price index Limit (category theory) Line (geometry) Port scanner Limit (category theory) Food energy Number Order (biology) Personal digital assistant Query language IEC-Bus Data Encryption Standard Software testing Multiplication Loop (music) Resultant Row (database)
Addition Slide rule Regulärer Ausdruck <Textverarbeitung> Table (information) Multiplication sign Expression Interior (topology) Database Lattice (order) Function (mathematics) Port scanner Computer programming Number Window function Inclusion map Goodness of fit Personal digital assistant Natural number Quicksort Table (information) Imperative programming Loop (music) Mathematical optimization Oracle
NP-hard Sine Weight View (database) Expression Database Line (geometry) Mereology Rule of inference Cartesian coordinate system Revision control Inclusion map Computer animation Query language Personal digital assistant Statement (computer science) Energy level Statement (computer science) Game theory Table (information) Row (database) Form (programming) Physical system
Digital filter Link (knot theory) State of matter Multiplication sign View (database) Parameter (computer programming) Disk read-and-write head Perspective (visual) Revision control Musical ensemble Selectivity (electronic) Mathematical optimization Rotation Programming language Expression Feedback Stress (mechanics) Database Line (geometry) Port scanner Subject indexing Personal digital assistant Query language Statement (computer science) Table (information)
Implementation State of matter Source code Price index Normal (geometry) Port scanner Table (information) Extension (kinesiology) Rule of inference Theory Row (database)
Point (geometry) Slide rule Game controller Dependent and independent variables Mathematical analysis Cartesian coordinate system Theory Element (mathematics) Mathematics Message passing Computer animation Personal digital assistant Interpreter (computing) Video game Right angle Selectivity (electronic) Extension (kinesiology) Oracle God
Regulärer Ausdruck <Textverarbeitung> Table (information) Observational study Control flow Hyperbolic function Mereology Core dump Recursion Sine Web page Expression Electronic mailing list Staff (military) Type theory Arithmetic mean Loop (music) Computer animation Personal digital assistant Query language Vertex (graph theory) Quicksort Game theory Table (information) Resultant Oracle Row (database)
Mountain pass Knot Infinity Rule of inference Revision control Programmschleife Iteration Series (mathematics) Extension (kinesiology) Recursion Condition number Standard deviation Graph (mathematics) Infinity Ext functor Database SQL Server Demoscene Process (computing) Personal digital assistant Self-reference Iteration Quicksort Programmschleife Electric generator Freezing Oracle
Partition (number theory) Computer animation Expression Set (mathematics) Selectivity (electronic) Statement (computer science) Recursion Table (information) Oracle Window function
Area Group action Identifiability Poisson-Klammer Source code Expression Keyboard shortcut Sheaf (mathematics) Total S.A. Function (mathematics) Total S.A. Window function Type theory Query language Personal digital assistant Order (biology) Self-organization Summierbarkeit Endliche Modelltheorie Table (information)
Partition (number theory) Group action Building Computer animation Sine Query language Rule of inference Row (database)
Group action Context awareness Set (mathematics) Control flow Limit (category theory) Function (mathematics) Icosahedron Mereology Rule of inference Order (biology) Different (Kate Ryan album) Selectivity (electronic) Partition (number theory) Keyboard shortcut Density of states System call Window function Partition (number theory) Computer animation Personal digital assistant Function (mathematics) Order (biology) Summierbarkeit Resultant Row (database)
Axiom of choice Source code Electronic mailing list Database transaction Total S.A. Instance (computer science) Software maintenance Cartesian coordinate system Total S.A. Inclusion map Order (biology) Computer animation Bit rate Query language Personal digital assistant Mixed reality Order (biology) Row (database) Physical system
Ocean current Sine Database transaction Function (mathematics) Port scanner Mereology Frame problem Window function Number Type theory Order (biology) Different (Kate Ryan album) Order (biology) Ranking Summierbarkeit Ranking Reading (process) Row (database) Data type
Slide rule Zoom lens Standard deviation Data acquisition Oracle Social class
Computer animation Different (Kate Ryan album) State of matter Chemical equation Order (biology) Expression Total S.A. Table (information) Rule of inference Row (database) Number Window function
Metropolitan area network Sine Chemical equation Gradient Chemical equation Direction (geometry) Division (mathematics) Variance Function (mathematics) Port scanner SQL Server Food energy Window function Order (biology) Order (biology) Quantum Arithmetic progression Initial value problem Oracle Window
Slide rule Standard deviation Proper map 1 (number) Limit (category theory) Density of states Limit (category theory) Conformal map Number Window function Row (database)
Slide rule Standard deviation Regulärer Ausdruck <Textverarbeitung> Sine Cross-platform Server (computing) Instance (computer science) Order (biology) Uniformer Raum Computer animation Musical ensemble Selectivity (electronic) Quicksort Head-mounted display Oracle Row (database)
Slide rule Order (biology) Standard deviation Computer animation Sine 1 (number) Database
Order (biology) Computer animation Sine Lecture/Conference Source code Ext functor Limit (category theory)
Point (geometry) Web page Trail Slide rule Presentation of a group Server (computing) Ferry Corsten Multiplication sign Source code Function (mathematics) Mereology Theory Number Formal language Revision control Frequency Goodness of fit Mathematics Fluid Entropie <Informationstheorie> Selectivity (electronic) Mathematical optimization Metropolitan area network Metropolitan area network Pairwise comparison Link (knot theory) Web page Sound effect Database Multilateration Cartesian coordinate system Word Computer animation Personal digital assistant Order (biology) Normal (geometry) Table (information) Family Oracle Row (database) Distortion (mathematics)
Trail Standard deviation Table (information) Sine Multiplication sign Range (statistics) Content (media) Database Extreme programming Port scanner Timestamp Revision control Frequency Mathematics Frequency Computer animation Hypermedia Statement (computer science) Data Encryption Standard Table (information) Data type Physical system Physical system
Logical constant Slide rule Information Sine Multiplication sign Database Temporal logic Port scanner Icosahedron Cartesian coordinate system Template (C++) Revision control CAN bus Process (computing) Computer animation Personal digital assistant Set (mathematics) Revision control Physical system Physical system Row (database)
Server (computing) System call Multiplication sign Mathematical singularity Theory Revision control Frequency Selectivity (electronic) Physical system Metropolitan area network Standard deviation Constraint (mathematics) Information Uniqueness quantification Database Perturbation theory SQL Server Frame problem Oval Query language Personal digital assistant Right angle Quicksort Physical system Oracle Row (database)
Web page Run time (program lifecycle phase) Link (knot theory) Code Mathematical singularity Range (statistics) Function (mathematics) Sound effect Frequency Type theory Different (Kate Ryan album) Selectivity (electronic) Constraint (mathematics) Key (cryptography) Temporal logic Equals sign Port scanner Exclusive or Type theory Frequency Order (biology) Key (cryptography) Ideal (ethics) Table (information) Directed graph
Point (geometry) Software developer Temporal logic Mathematical singularity MIDI Function (mathematics) Port scanner Disk read-and-write head Sound effect Exclusive or Subject indexing Type theory Frequency Computer animation Blog Different (Kate Ryan album) Computer multitasking Ideal (ethics) Key (cryptography) Endliche Modelltheorie Normal (geometry) Oracle
Slide rule Table (information) Sine Multiplication sign Electronic mailing list Port scanner Window function Revision control Type theory Frequency Computer animation Personal digital assistant Revision control Figurate number Physical system Physical system Spacetime
and in its marketing and I talk about modern SQL and BI mode and SQL a basically mean everything that happened after SQL 92 which is still the 1 that town of released most people use and our most books care about but I think there so many interesting features in the more recent just standard of releases and many of them already supported and prosperous and I just want to raise the awareness of these features and the idea that you can use them so 1st of all with the using Windows 3 . 1 no 1 has all this function with the remembering Windows 3 . 1 because that's not everybody OK you have to be aware of that that's a more than 20 years ago 90 due to its it's quite a long time ago on and the question I have unified is actually real I mean nobody uses an industry that 1 anymore but everybody on kind of focuses on limited Test to a subset of 192 and that somehow it doesn't make sense to me so here today i will on introduce a few selected features of out of a few selected on standard releases of their distance so the first one are you
gonna talk about SQL 99 that was indeed the big bang in history world because it agreed broke with the relational model really SQL 99 introduced non relational features like nested tables and on the other end of the non 1st normal form kind of thing it really broke Mr. pure relational algebra and all of that and the 1st feature I want to show
you is lateral so what's later on words and use that OK quite a few the recent addition prosperous proficiency
before I show you on what you can do with it I would like to show you what kind of problem it's it's it's always how we wrote of statements before we had that feature and what you see here is inline few and for inline fused it is forbidden to refer to the outside part of the query so that reference which I have here from a T 1 inside that inline few is indeed so that doesn't work in SQL 92 we have to put that trying for during the long shots for using lots of whatever you like that's the way SQL 92 works and now we can add the modifier
lateral and while it's allowed now that's basically what it is about so this river is now OK that's fine but we still need to to satisfy the percentage of the joint yes it didn't come in a later flight to the question what version of the the code in a later slide and the lateral is really just a modifier for the joined the means and if we having a trying like you miss the on costs and if it's quite useless in that case we just putting them on true on you could order do across joined in that case Cross-joint natural and angry about them we don't if any more but the question is
why the heck do we need that watchful and there 2 common use cases the first one is the
codon table functions with arguments from previously mentioned on table In that case example here I'm you passing the T 1 . ID parliament had to take function which is joined in so this is the 1 use case and the other 1 is so if you want to
apply some logic inside the inline few we you could not apply
otherwise the most prominent example here is limiting the dataset so in that particular case what is clearly does it selects from categories so order categories and for each category it's going to a product statement ordering them by some ranking whatever that might be and then limiting limiting it to 3 rows so that's basically selecting the top 3 rows per category and that's something we could not do without lecturer in that way because of and then the semantics of the limit would apply to overly and not limited to 1 category so this is the 2nd use case
on various another another way to use that this is forward according source bang theory it's like thinking in twitter or whatever social network you like show me the latest 10 use out of my network so as many subscriptions and I want to estimate the latest will so this might be a query to do that it's basically just joining subscriptions by news filtering from my user then ordering by date descending and limiting to let's say 10 and if I'm out of luck cause this mind
and optimistic like that and to make it a little bit more visible at 1st choice basically order news which I subject subscribe to which happens to be 900 thousand rows in that case and then it's storing the top sort and filtering out of 10 most recent ones so maybe not the best thing to do so why do we produce 900 thousand throws basically all the news I have ever been interested in our if you have only eighty subscriptions in that case we can see that and execution plan and I'm only interested in tend to 10 most recent for answer is it should be enough to search for 10 the 10 most recent news for each subscription and then again sort date that 800 rows and interested and General and this is what can do with lateral we can just
write this top energy for the 10 most recent inside inline fuel and so kind of limiting that result to 800 18 by 10 number of subscriptions by number of rows and then in the end you have to sort and and limit again get just as 10 very most recent ones if you
look at the execution plan it does what we would expect to in that case it goes to each and every subscription features depend most recent rows and then just sort them again and shows that what we don't like provided you have to write next so here we have this limit to 800 that's the number of subscriptions by the number of lines so that's the upper limit for this kind of query just 10 100 thousand times as just a figured whatever data you put into your testing it works fine so
lateral I like to compare those recent SQL features 2 features we know from other programming anguages like the more imperative programming language and in that way I like to compare lateral this for each because it's executed in a predefined number of times natural works well with resort kind of out of joint on sort kind not right but trust trying out the Chinese finally trying to adjust to modify it's great for that kind of talk and optimization that's what I use it most of the time for and it's order great on to join tables functions and honest here is the most frequently used who is using honest OK so that's sort of a good use case for OK and now coming back to you
on the value to your question can you use it yet well I don't know but if you have a prosperous 9 of Sweden yet so it's a quite recent addition but still there for some wine the 2 1 3 it's much weaker yeah so maybe a segment to windows so it is 9 3 whatever the slides this is true I might be wrong but the slides that you FIL yeah and as you can see it's of course you appearing in other databases right now like in oratory from here after some 15 years in the next selected feature I wanna talk to you about its own way and we has to frame this is just a simple case so it's called common table expressions
and 1st again I would like to show you the problem you can sort so if you're having a query like that which is just scratched here and you want to make sense out of that because some critique dropped it on your paper then you have to positively be and the innermost part 1st and you can make sense out of that then you can go 1 step outside and make sense out of then you might find another subtree on the same level which is then joined so you might need to understand that just as well 1st before you can finally make sense out of the very 1st line so this is the weight on a streamlined it was written this we will use will it you might need to repeat yourself to that's also true so 1 coming up for
now is to switch statement and with in a simple form is really just uh statement scope few think of it like that statement scope if you not if you you create in a database no it's just a few which you put in front of your and it's just receiver for that query statement scoped few let's go through the syntax for revenue keyword with after which you define the name of the few which is 80 in that case and you optionally defined the names for the columns and returns and after that it just a s and some definitions select as we know it from different view but you can then go on you can write a comedy after that and add another commentator expressions In that case this commentator expression can already referred to the previously defined 1 that's just fine federal rule with that I don't think so by doing so so is to the you know yeah but then there are the smallest executed per row and system is just just a different way to to write your query so for the with few I like to to see is that Justice source code management feature that there's nothing more to it the so I ordered defined assert commentator expression accords and then finally the important part is that there is no coal-mining after that definition and that basically means that the mean query coming out which candidates is all of 2 common table expressions you have defined before so the big game changer kind of it is from me this you can now read top down and not innermost outside the treaty source code management iterative more nicer place monitoring this 1 did and so on for so that's what it basically
if I compare the tool to other programming languages and rotates like private methods it's something you can define you can also use it was in that statement you can refer to that might have at times but it's the visibility is strictly limited to the following state on technically and import factually which is just the prefix for select so you can put with everywhere where select could be that means you can write things like that in that in 2 weeks and then select you can also use with in subselects like line fuse whatever that already which is really just a prefix for select missing prosthesis donor database that implemented at right but if
you wanna use that there is a tiny issue I have to tell you about looking at this example so with common table expressions and just selecting everything from the newspaper and in the main queries and then filtering on some topic I B equals 1 and the problem here is that there are 4 common table expressions for stress does not pushed down those trade so that means even if you have an index on this topic it won't be used virtually executing the common table expressions independently and then kind of just figuring that afterward yes of course yes but under there there is a use case for for the it's just that I have to make you aware that on and telling you it's about source code management and then you have to be aware that some drawbacks the from the positive community and getting all the feedback that this was implemented because it's mentioned that we understand that I don't know where this is written if somebody knows that this should be written I would love to learn about that that's what I'm saying as well and every other databases is just inlining and the Carolyn discipline with line so but is it is the the is link I don't think so but it is it is a great great thing start is let's look for it what was the head of the arguments against is that will not use it as optimization you will worry people's optimization in villages our I would suggest to it just introducing new modify modified with of materialized with materialized to keep the old 1 that the keyword materialized is already used for materialized views so it's the perilous missing that would make sense from my perspective OK so here we go just to show you if you
in line that with few than the predicate pushed on just looks fine so if you would rearrange that clearly to the we example then you make badly brake performance as of now you can't just be aware
OK so this was something on which I would be more let's consider a downside of post this implementation but here's some upsides prosperous unlike just on allows are in update delete inside with your whatever that means so in that example let's go through the through with deleted rows and inside that you and deleting some rows from some source table and the state return what I've just written deleted so with this few deleting those rules and returning and and the main theory and just insert into destination from that few so that's basically moving rose from the source to the destination that is quite handy but it's not just your standard it's a prosperous extension so can you use it yet this with feature yes
probably so we're still running on 8 . 4 later you out of life and 0 my god so migrate I'm sorry but if you 8 for exactly and still works so that's fine so I would like to emphasize that there is only 1 red bar right now on the slide can even you like can do that was coming to the next feature
which that on got it in his you know we call on all of these things
you select so of all of this what is the selection of the you know you you think of it remains in that some of the quality in of the control of the if you have a lot that we tried the opposite it was so that you have in the case of element of the little or no only moving from select select so we have on the probably so uh I I can't respond to that right now because it's not about that before because this is a post extensions something simple simply look at some things that we can use it selects the start from the old to the new colony when i have that was you know because there is so that way we be the analysis that we know that if you don't want methods that litigation because you got all of which are objecting to the point where it was right over so if use with clause as sorting and there that actually get the correct interpretation is the 1st 1 is the 1st 1 stops and this 1 and following his message changes is associated with that from some of the of the of the of the of the sentence that the the user of the application of the theory here but I haven't got the extraction of the OK I has to go under the attention of the
vertices so recursive which is sort of the well something quite different but I'd like
1st again to show you on how resource that kind of problem before we had that feature and you can see here is virtually on discrete we so with this recursive you can sort of a completely new type of problems and it's always part of the game making SQL uttering complete so what it does allow its on it
and you modify with recursive and it allows that the 2nd leg of the inside of the union you have to put inside me refer to it so let's go through them that the new people OK great if you use this recursive the core you name list is mandatory greater than the start was and in there you must have the union the union or to and the 1st Lakers obviously 1st executed so in that case it's just returning 1 row is 1 where you which happens to be 1 which is the kind of signed up to this common table expressions and it appears everywhere where this common table expressions refer to so that's 1 thing the mean query and because I'm just selecting staff from that commonly come on give expression here it already becomes part of the the final result so then in the 2nd leg of the union of executed and you get that 1 in there as well so we are comparing that for the where clause which is fine 1 is less than 3 then the incrementing it because select n plus 1 needs to and this 1 is again send out to this commentator expression and the game repeats and here you see something which is actually a loop so we can now program loops in year that goes on until the 2nd leg doesn't produce any rose anymore and in my case studies the case when they're and less than 3 is not true anymore and then took the loop terminates so that's the most simple case of a recursive commentator expression and this use
case is cauda rule generator because it generates rules and generates series which will often prosperous is not standard version it's just a proper during extensions the most interesting use cases for which recursive processing graph but you have to be careful and to prevent Kida loops non-terminating will be careful and put in the most general way with recursive allows to be implemented loops in native SQL which have dynamic aboard conditions unlike lateral which is executed for each rule so this recursive you kind of freeze dynamic what condition another interesting features that you can pass data from 1 iteration to the next 1 like I've just done it here count the that sort of something come to later so I
like to compare with recursive was why you because it actually is so be careful infinite loops on scene of reality on and the recursive keyword or it is in the standard and monitoring the standard if you want to refer to yourself and most databases don't require some databases don't know it but still accept the self reference like SQL origin so can you use it it's
probably yes go ahead it's there it works in 5 years or so instrument OK so that was my selection of features out of SQL 99 the next 1 what SQL
thousand 3 and that was another big bang this was that all lot of feature set and you probably know that from that
kind of feature who knows full well a window function OK great because that's that's really an important future together with the recursive common table expressions SQL became a Turing-complete because of what you accidentally so
again let's show how we would write a specific clearly without that feature but I'm already using after 1990 chosen to eat my own dogfood I've put in with just there so I can explain it to you up down so I'm starting with a few caught total salary by department I like to type a lot of can that let's look what it is about so it's from employees it's group by department the sum of salary in our case was a price it's total salary by department then that let's look at the mean query so it's going to them pretty stable and joining the common table expression by department so I have to total on salary for the that apartment next to each of and and what I'm doing this that it's just calculating how much is this employee earnings out of our department that's what I'm doing in salary divided by the total by 100 in person so that a possible way to source that kind of problem without window function but before I show you how we know functions to source that I'd like to remind you of something which I really like it to organize source code in that example total cellular read by department who likes to type such long identifiers only if you mean idle but I like to put a mental model in the in your hands right up front that's what I really like that makes it easy to understand how to use it later on i like a shortcut like you that's what I really like and unlike if you right it was brackets inside there and you can actually give it to to me studying this problem with a few can it the review and then you can analyze it once more effective financing but I like it and show me what it is I would do
that just added another work shows that the main area if you it's your turn understand the world the efforts this post Christine Lagarde it's order for order entry section so that's just what I have to emphasize more than 1 sorry um OK so why do we need to do it
that way and the truth is that before I still 2003 there was no way to build aggregates without on group by back and through biology this rules like these things so if you want to have an an aggregate but don't want to collapse rows there was just no solution then the 1st being the aggregate and then joining it to the original back again and this instead doesn't streaming now have
a solution for that and this is what the new query looks like so now in which salary divided by some over salary over a petition by department so what does that mean let's go through the
snow in sympatry select department salary from entry recite and the 1st thing you should do when you ask think about using window functions is pressing the comic but that's already about you're adding another according to the results and that's what we know functions of then you can choose any aggregate function like some mean marks come name that peace order once you know and because you don't have a group by you need to define all which rows this aggregate functions to be applied and that's what we can begin was that all workshops and in the simplest case or what break it open break of just means just over the whole recite so that means over that path which means you will get another call you're having the total sum on each row and that's the difference it doesn't collapse anything OK so the next step is to put in that partition by which is quite simple it just segregates the result set of like to bind us that's it for the researcher getting now is that in the 1st group we only be the sum over the 1st group in the 2nd group over the next 2 rows and so on just segregating like provide us so over in the same
performance you can just use it would order aggregate functions you already know we still because it's safe you define their rules which should be put into the aggregate functions all were and because it's like grew by and because and over petition values like by but it's only the 1st part of the work the next part is all well and all that apply on the 1st sight it might not make a lot of sense to to put on by into context to on aggregate functions like some because it doesn't matter which way you put desired with the same but there's an interesting feature for
but 1st let's look how would source the problem without that feature and example and you can use as a running total have bank transactions and for specific account their ordered into some some specific order and I would like to have a running total next to each transaction to 1 way to do it is using a scholar such such you actually just putting here and just building up the someday and selecting the rows you want to sum so that's basically the same account and all growth that happened to be before that role including its for yeah that's system 99 1 of the things is that you have to keep their where and on appeals of instance which might become tricky if the order by Charles is nontrivial like more columns listing of with mixing as and this and you need to rephrase that order by just as a way in a in a query that's getting messy so before I get 2003
street running totals you had added to do as set of status of tree-like since he was safe trying on the maintainability was not great the performance was even worse for in all honesty the only real answer and in quite many cases is still the best answer was just do it in the application rate is clearly was just not about the best choice for that kind of but nowadays
we have all 1 and order and the funny thing is order it doesn't make any difference in which order you and summarized the transactions once you have put this reciting to a specific order you can narrow the window of rows which is to be feeding into the aggregate function this so read order by transaction ID and then is a modifier rows between unbounded proceeding and current role so that's the window of of rows that will be fed into the sum of functions so it's getting more and more and more rows proceeds can
besides defining frame like this there's a standard closer to narrowing the window for froze to be aggregated and once you have that order to find another type of functions makes sense like room number is ordering produced disaster 2003 but 1 part of rank then strength ranking functions like I'm questioning how do we call this x equals and placement in sports like 1st in 1st place and was coming next is is the 3rd place on the 2nd place that the difference between rank entering so that's basically what you can do with all work and order but can you
use it yet yes of course the look at that like to skip over to SQL 2 thousand 8 I must tell you that I have skipped 1 2006 there was always a standard release but it just don't like that 1 so much so I'm skipping it not of 1 slide texts in
the features of the big yeah for being implemented in and pushed it into the standard of what and I think there will be lower class was actually invented invented by the Oracle right now we will see that once more again OK as
2008 and surprise surprise they added something more
2 or 1 instance of so the example I'm showing you now is the difference it's not the running total it's I has the totals lets it could be but I would like to see the difference to the previous state how would that be done in SQL before 2008 1 way to do it and then more again using all the features which were introduced as 2003 or earlier is that you just number the the rules do I have a common table expressions which basically select all the data and putting another column in there which is just number in the rows using the window function room and then and since joining that common table expression to itself and then also sitting in their own chose the rose by 1 OK and enhance the current and the previous next to each other and it can be the difference that's 1 way to do it before I started off there are many other ways you can order sausages extra money to but it's not funny nowadays
SQL 2008 the crew looks like that so we can just take lag which is a new window functions and Canada previous value of the quantum balance according to the all the bytes of mentioning in the work that it same results OK but quite more easy and besides let we have already got some other functions like you need obviously in the other direction we have firstly we have lost their have and and engineers modifiers like from 1st from last and respect nights ignore nite which are not supported preposterous yet the most energy progress going to be there problem they can be emulated it's just attended Western yeah it will reduce the variance in it was it was initial value of the it's not original so by had never need it but it's handy if you need but it can be emulated quite by listing it once more OK so can you use get yes because it was
order integral to the hospital 5 years ago was 8 . 4 the next feature which was introduced settle to non-native 1 which I like very much it's called such
1st was ever use that some smiling faces OK what is it about I fused limit already in the slides but I did not mention that limit is not as just on so what
does French 1st to it's basically like limit but it's just on if you don't want to use limited if you want to stick to the standard 1 way to to do it is actually using the row number window function that's a pretty good way to use except in prosperous which does not optimize this problem because it doesn't realize it's an ever-growing number and aborting once it has fetched 10 rows like origin 7 single to give it to group but that 1 will run over the we recite number in all of them and then filtering without out of ones and so yes that's SQL standard conformance but don't do it in prosperous so yeah well let's use announced on future like you that that's the right answer here but now we have a
standard feature for the court fetch 1st 10 rows on it so that other people would like to Python not and on the next slide you can see who you are at which company these other working so yeah
it's it's the same question and so it is an instance of each feature but B 2 already had it in the 2 thousands so guess who who invented it can you use it yet yes source and prosperous you can already used that it sort stuff it's just on cross-platform benefits OK again this was just a select child of mysteries 2000 8 and the finally we would like to come to the most recent release of the standard that people yes for
the question can chemicals ones like yes can be the the of wait another slide that's coming coming because I can go ahead to SQL
2011 the most recent release of the standard and the killer feature of SQL 2011 is temporal and by temporal databases so who would ever use that who ever need who has no clue what I'm talking about an acute I will show you what temporal and by temporal databases is about but before that it's written there it's not
in this talk but it's written there are so it's best SQL 2008 the introduced a standard conformant way to implement limit but it didn't put off setting which is great want to estimated on 11 when they did so offset who knows again don't need to explain if we use it offset but I don't like
offset because of so many reasons so the problem at sources you know you know it the way
it is about to be written is yes you can do it that way but you shouldn't you should remember
pump the lecture on this kind of with faster OK OK of and the reason I don't like closer to much is I explain that way I like to compare SQL features to this functions of functionality of a programming and remember lateral for each with this recursive why islands on offset I like to compare to sleep the bigger the number of this fluid execution so that's that's the only good use case for the problem is is that it's probably not only so I don't mind about slowness and it's better than the it does just not wait and it even our kids resources for you and mean it it needs to be 1 of them but quite getting that point which offset you have to keep in mind that offset is renumbering the rows all the time so if you're paid moving in behind-the-scenes here inserting it's renumbering again so it's probably not delivering the recite you you up for so it's not the answer to pagination period the keep that in mind of gives you the wrong results and besides that it sort of great so use it all 1 gram of placed near to the exit their and their cost us something there is the no off the coast of or just because from all of and a lot of other stuff and then you wiIl wouldn't written there that 1 and this 1 explains how to do it right without offset getting the right beside foster disorder presentation on land and and you are but just keep in mind of cities noble don't use it can so can you use it yet
how you could and yeah well this is the only slide in this presentation where my SQL turns green and red is and we can again use that slide to make our conclusion we meant that that feature and we see the version number street . 20 . 3 of myostatin which happened to be in 18 9 so and recent release notes that come with this it is easy to do a poor man's next page previous page www applications yep woman for better what we have now and it's it's really it's bad don't use it just because it's done that doesn't make it good this is 1 of the costs you all 10 so that's 1 good use of said 0 then it's like that maybe like creates from optimization theory 1st OK so it creates an optimisation theory like which if it gets worse as well phosphorus we we like to say we don't know in this work we have these optimization here's the thing is that basically family moralists and 1 of the parts of Europe with the use of words and we can optimize it you know of OK so that there is a side effect impostors which can be exploited for good use let's put it that way but other than that so on the use of 0 not offset any any other number so but coming back to ask you and going over time it is a problem for you and other than than of such as the 2011 is about temporal and by temporal databases and the problem at source is a problem you or or everybody of you had and it is a problem introduced by normalization OK because normalization say if you have many orders for the same kind distorted kind only once but normalization done really and so what to do when the the kind data the master data changes and who has ever invented some kind of tracking history tracking table for the who did know some of you and now we have built in language support for that kind of problem again just a selection of the features first one itself I selected as because it will be in SQL Server 2000 16 it was just enough uh months ago and on radio like who 1st order and should up here so before it's
good 2011 changing data was destructive you have deleted it it's gone if you want to have a soft you have to implement it on your OK SQL thousand
11 allows us to here on table member that you would like the database engine to keep track for off on changes of this paper social media create table statement what you need for that according to the standard is to call misrepresent the stamp when the column stopped discordant Russian started to exist and many in the 2 extreme and you have to tell the database that it has to be maintained by the database it says that the generated base Rose Doctorow and other than that it's a pretty ordinary content you can always use of at the other data types you you could technically you state if you like my good idea but you could then you define a period which combines the start timestamp and pond and times that kind of in a kiss range kind of thing and finally you see with system versioning and that's the magic now this table is a system version table which means it behaves like that if you're
gonna insert something you don't need to mention you are not allowed to mention those to make the columns to stop constant and timestamp they are maintained by the system so if you in certain there will be populated automatically it's just there for better was into the can of coke it and update so but yeah well then it looks like that you will end up this 2 were with versions there it's not destructive anymore OK there's only 1 current 1 but there would 1 is still there and obviously if you deleted it means just the constant before is changed so
you have now my cable versions of that same row in the living in the database but it's transparent you you just updated and you get 1 more 0 and even if you selected it's then transparent you just see the current row no because it's their world but by changing the time since the time of the database but you you and you're not allowed to update those of things directly to the Assistant version that ordered the application version that's the reason it's scored bitemporal database and you can have a 2nd time axis on the status of well I don't have it in the slides but you can also use it for application of constant and then you can then you have to maintain them on your own but this is just a version of the of the and it's transparent even for the selected so we should select after 12 o'clock after it was believed that you won't see anything and transparent unless you want to see something or would they just say from T and then you have the new modified for system time s also and that constant and then you will see that information at that time stamp to modify it to from jobs so that's system version paper that's the temporal aspect and by templates and ordered application version can you use it gets more and more of this new way so
technically it is and it to and it seemed to be correct I'm technically it easy in origin but not in the same syntactic because already introduced it years ago are coded fresh-baked queries so it's there for a long time but it has a different index and step so it was announced for SQL Server 2016 about a month ago the last few days there was that the release candidate so you can already bounded extrasolar 2006 and people are starting to to play with this feature so that that's hot right now so that's just the reason I have put it into the flight here as well it the next month long not according to the stomach not not that not that I would remember not that I would remember the we if you have for you I would need to look how hard it doesn't the provisions of the all a different question it's a good question which I don't have an answer for us is that each can lower end yes but it's it's mostly useful info for slowly changing things came to you you you know you here's what you you have you know make you have of of the pain and I think it will be in the document I don't know for me I think the standard I don't even think is are always there's a standard way to actually deleted brutally and sort of all it's not yet a problem just in 10 years OK the the 2nd feature of and again this is just a selection some of it's going 2011 is without overlaps because as you have seen we have now my cable versions of the same row in the database at the same time so head how do we cope that uniqueness and primary constraints in that case and answer without a well constraints so it's best to
2011 if you want to prevent things like that having overlapping time period specified that need to be a system version theory can be arbitrary and time frames and there was previously no way to to make our but the at the curation to prevent that you have to create a trigger whatever know solution now we can write
a primary key that refers to a period which we have just defined in the table and sink without overlaps and on as lucky as we are became kind of use that already in PostScript it's not the same thing it doesn't it's not a key but without overlap of starting is 9 2 2 we have exclusive constraints with every use them OK and so range types we have combine those 2 and then you can write an exclusive constraint like shown here they're just the idea is to be compared with 2 equal signs and the period is to combat with the topic of sound that well let's operator and make sure there no to the on constant so it's different syntax but same functionality it's just that they curation it's not a runtime code like trigger look selection
2011 really really really really go to this link will be treated in maybe half an hour so go read this paper it's good it's 10 pages and it's it's really an awesome are explanation how to features and order that the other features which I haven't mentioned will work on our work of working according to its introduction 11 so without a 1 that's can you use it yet but yeah well in
PostgreSQL kind of difference indexing function so this is the point where has been toward the end of my talk is pretty accurate yes it is on the head you
have any questions you know we're able to use model on the history of the
use of the where it you can you can
you can do whatever you because the reason
because the reason for a short short
list just slide space on a slide is window making big enough so everybody can read you can use any type you like in figures but it needs to be a temporal about as I think I think time along origin not would be technically OK but that don't think it makes sense as and you will end up with some sometimes and NCDs a system version fibromodulin should times what they say and then you can have only 1 version of the war on the well if if this is your use case then you have great go ahead OK no it's so thank you if you have any more questions I'm outside and don't forget to grab the stuff down there and don't ever used to offset again him