Video Transcription

Welcome back everyone. In this video, we’re going to continue our discussion on data modification. And in particular, what happens when we use Insert on a table that has columns that are identity or sequence objects. So, in general, a lot of our tables are going to have an identity column or a sequence object, because a lot of what our keys are based off of our primary keys are usually going to be a surrogate key that is, generally speaking, auto numbered. And so that’s where the identity column or sequence objects are very beneficial to use for, in general, we do have three different possible ways to utilize the values coming out of the that identity function. And so we have the Add identity, and this is going to return whatever value was generated last, from the last query that we executed on that table. So if we inserted the first record in a table, and that identity, for that column was one at identity will actually pull the that one out. And we can utilize that as just like a variable inside of our queries. Likewise, we have scope identity, which returns the last value that was generated by that identity column by the last session in the current scope, and I’ll showcase an example of how this actually differs from the, the previous at identity.

And then we also have ident, current and then table name. And this returns the last identity value generated globally. And so you can either ident current without a table name, or specify a table name. So that can be specific, either global, or specific for a, a, a particular table. identity itself, remember, is non standard, but in general, is a simple, simple form to actually add an identity column to our database. And generally speaking, when we’re inserting, it’s as long as you remember those last couple of items, we can pull that value out of that identity column, if we need to do any advanced, advanced things to the data that we actually insert. But it is non standard, the standard SQL definition is going to use sequence objects which achieve a very similar result, sequence objects, remember, look something like this, I’ve showcased these before, we haven’t really used them a lot, so far and the queries that we’ve been executing, but a similar process, right, we have the start value, the smallest number that we want, and then and then the number that we actually increment from so this can be up by one or by two, or so on and so forth. And then we can indicate whether or not we want this window range. If we have a specific range of numbers, we can force it to cycle through. So if it ever hits a min or max, it’ll actually go back to where it started from. There are other options to achieve similar results to this. But generally speaking, they’re a little bit more advanced topics. And don’t come up as often or as in normal basic usage, and simple database designs. But you can do some more advanced things with Seekins objects when we’re working with inserting data into our tables. general syntax that we’ve covered so far, and again, I’m going to cover a little bit more into into more details on examples of the identity columns and things like that. But we have covered these three primary syntax of forms of our insert statements.

So insert values, insert, into Insert, select, and then insert exec and everything here. This is looks a little bit more complicated than it really is. This is just the full SQL Server Standard definition of the statements. But you can see the previous examples of what we have done so far, but this kind of shows you what different options you can actually include as part of your insert statements. But let’s take a look at some more examples of the insert clause. And in particular, how our insert is going to work when we’re dealing with identity columns. So this sequel is included in the Canvas module. So this is just going to be recreating all Have the tables that we for all the tables that we use as part of our demo schema. So person address, things like that. So nothing new that we haven’t seen yet there. But in particular, I want to go ahead and create and focus on particular, our identity columns. So if we pull this back up here, this is just going to highlight this identity of these identity constraints. So we have an identity constraint for Person ID and identity constraint for identity column for our customer. Both of these are primary keys, they start at one and increment by one each time. And again, in a different SQL language, other than Microsoft SQL Server, you might see a sequence object being used instead of identity. So let’s take a look at an example of inserting into our tables that have an identity column, where we want to actually manually add and add to the value for that identity column. Typically, when we want to pull a value particular for an identity column, it’s best practice to use scope identity, and rather than, rather than an identity, unless you need to ignore scope, and scope is typically going to refer to this the current session that you are executing in. So right now my my tab here, this is my connection to our database. And so this connection is our scope. And I’ll show an example of a different scope here in just a second. But let’s go ahead and execute this. And let’s go ahead and find our JD enterprises. We just inserted into our customer. And so our add to Person ID was linked to our contact person was linked to John Doe. And so this is a helpful use case for scope identity. And again, scope identity is going to be our typical preferred our preferred method two pulling out the last value for an identity column for for our insert statements, but we can also so if we so this was five, and then if I do a another connection here and execute this, you notice that my, the last identity now is six, whereas here, it was five.

And if I run another one here, this is still a different scope, but the identity, the last identity hasn’t changed yet. So it’s still going to be six. So this is a this is what the difference is going to be for that versus at identity, so we can actually showcase this. So our that at identity is going to be empty, right? Because there hasn’t been if we go back here there we go. If we go back to the slides here, remember the difference here. So this is an identity is returns last I didn’t the value generated in this session. Similar ish, similar thing was scope identity, but the session and current scope, and then ident current returns the last identity value value generated globally. So that’s the primary difference here, right? That’s the primary difference. So scope, so session is this connection, right session is this connection. Scope is going to be scope is going to be in the blocks of the batch that actually run in and then the session is going to be from a different connection to the database, right? So that’s why this here scope identity, even though it can include The value generated by the session in the current scope, there is no set there is this is a different session, right, this is a different session. And so the global last identity value was what is six. So that’s going to be the last item that we actually insert. So just some primary differences with what, what we actually see with scope, identity, and identity, and ident current, just some different behaviors there. So just be just be cognizant of when we need to actually use the value from an identity column like here, we’re inserting that into as a foreign key into another table, the you have three different options. Typically, you’re going to use this scope identity. But in some scenarios, you might want to use identity current. Or if you are using things in a different session, you may want to use an identity versus scope identity. So there’s some differences there. Okay, so that is the identity column, let’s showcase what this looks like with a sequence object. So let’s run this here. We need to also apologies. Demo dot person address, because we have a foreign key constraint that’s preventing us from doing so. There we go. So So now, what I’ve done here is I don’t have an identity property set on my person ID. So I’m actually creating a sequence object instead. And so to actually link that to our Insert clause. I’ll actually first let’s show let me showcase what that sequence object actually looks like. So I’ll actually run this underneath this tab here.

So run this and so here’s the next value out of our sequence object. So we can select that. So this is the demo dot Person ID sequence. And so select next value for demo dot Person ID sequence. So that gives us the next value that’s going to be used for the sequence object. And so typically, what we’re going to we can, we can actually do this before the insert if you’d like to. So you don’t actually have to, for First we don’t have to actually insert something into the table to get the value out of the sequence object. That’s, that’s number one. And two, rather than fetching the value, after we actually do the insert, query, you can do it before, so we can actually store this as part of a variable. So if we do this here, so we pull the next value four, so this is out of the sequence object, next value for the sequence, store that in a store that as new person Id give this run one row affected. And so if we pull that out, you can see here, our our next person that was inserted, so before, right, before, when we did every time, we actually execute this, this here, right, every time I run this, you see that my sequence goes up, because next value forces the sequence object to actually count to the next number. And so every time you execute this, the the sequence object actually increments the value regardless if you’ve inserted something into that table or not. So if I insert a another value, right, if I insert another thing into the person table and utilizing my variable here, which will be next value, that would be 23. The next person that I actually insert. So we are we don’t actually have to use a variable either here we can actually just put next value straight into our Insert query. So if we do this, insert into person, values next Next value for a demo personality sequence. Run that. And if we showcase this here we get 23, just like what we did just like what we saw before. So again, this is kind of the difference with between the identity column and the sequence object, the sequence object is disjoint, or separated from the data that’s being stored in the actual table. And that’s using that sequence object. And so you may get things like this, where even though I have only have two records here, doesn’t start at one, and doesn’t. And the second one is it number two, because I ran this statement here, next value for sequence multiple times before actually inserted the data there. You can also use it and multiple locations. So if we do something like this, so select Person ID, next value.

So as I mentioned, right, this is, doesn’t matter what table I’m pulling things from, I can use this in whatever I’m actually working with as part of my SQL queries, you can use the select clause and generate multiple values at once. So every time that next value in a column, if you use next value in a column that is generated every single time for every single row. So you know, things, things that we can achieve there, you can also reference it more than once. So if we do this, here, we have this so we have a person ID, P dot Person ID, P dot full name. And then and then this is from the wide world importers database, by the way, I’m pulling from the Alternate ID from our, from our sequence object in our demo schema. And then I’m using that same that value again, here for this column. So this is kind of a unique application here. So generally speaking, right? The, when you execute the statement, it’s all done at once, right? For every row, it’s all executed and pulled out at once. So the value used here, right, this statement here, both of these, this is executed all at once. And so the value that you see here is going to be the same value you see here, because we’re doing a row by row operation. And so the values for a row are executed all at once. And so the actual sequence number is the same for both columns. sequel is, is this see that number is used, the same or the number that is used as part of the sequence is the same for both columns. We can use sequences as default constraints as well. So so far, this is done manual. And as you can see our behavior and, and differentiation between things is kind of wonky, because we are our sequences, if we’re truly using our sequence only for our person ID and we want to sequentially you know, increasing as we insert records, doing it by hand can be a little bit tricky. It can be a little bit tricky. And you can, like I said, get a numbers that are kind of out of that are kind of weird. So if you run that next value multiple times you get something, skip a whole bunch of numbers. So if you don’t want to skip anything, we can actually force the sequence object to be part of our default constraint. And so this is going to act just like and I didn’t even call them, right.

So just like an identity, they call me now don’t have to define a value for that column when you insert data. And so we can execute a, let me scroll up here, we can execute this and get our next person ID. And so then if we wanted to also, you can also apply this as a window function. Right? So again, we can we can have it as a default value as part of our constraints here. Just like an identity column would be or we can also use this as part of a window function, which is pretty cool. So let’s run that. Here we go. So the same sequence over and then we’re, we’re separating our, our, we’re framing up our window and ordering it by full name. So if we run this here, so here is our window function being apply over our query here. So something that we can use, this is particularly used to define an order to our rows and our query. And we can also, if we want to, this is super handy. Something that is a little bit more flexible when compared to the identity column. But it’s very easy to restart our sequence. So if we force our sequence to restart, right, that becomes something a little bit easier to do. And if we sort, ascending here, you can see all of our sequence object values there. But if we sort by Person ID. That’s, that’s that. So just some different options that you can have when using sequence objects, particularly when inserting data into our database. So we have the identity columns, which are Microsoft, or T SQL specific. So Microsoft SQL Server, those are typically typically very easy to implement, because there’s a lot less that you have to do to actually attach that to a column. But we can also use anti standard syntax and use a sequence object, which is a lot more flexible. It’s not tied to necessarily one and only one table inside and one and only one column. And that sequence object can be used across multiple instances, multiple queries, and multiple different tables or even multiple databases at that as we shown. So generally speaking, a sequence object is going to be a little bit more flexible, and it is standard identity is going to be very quick and easy to use. If you’re using Microsoft SQL Server. I’ll leave into this video here on the general syntax that we’ve seen so far for the insert statement, but that will conclude our coverage on inserting data into your tables.