Batches

Video Transcription

Welcome back everyone. In this video, we’re going to be starting our discussion on programmable objects. And we’ll be covering a lot of different topics in this video series. But first, we’re going to talk about batches. So batches aren’t necessarily a programmable object. But we really do need to understand how sequel runs, queries in batches. So we can understand scope, and how programmable objects are actually functioning. So batches are chunks of SQL queries, or a series of SQL queries that are are sent to the database server as one unit. So we could have five SQL queries in a row. And those could be sent as a batch to our Microsoft SQL Server service. So far, what we’ve used with batches is just highlighting SQL queries and running it or each of the, like Jupyter Notebook cells, that is, when you press run on a cell that is considered to be a batch. Or if you have a sequel file without any ghost statements, that entire file would be sent to the database server as a full batch. But the addition of the Go syntax is going to allow you to separate SQL queries inside one file into batches. And this is really important when we start talking about scope of things like variables, for example, also certain programmable objects and CTS and things like that. But it does allow us to separate things out. And this can help logically organize our SQL queries into smaller chunks.

When we are actually executing them on the server, some things actually need to be fully processed first before we can actually execute queries on them. So particularly difference between DDL and DML. So the data definition language, so that’s the create tables, and DML. So select queries, insert, updates, and deletes. So without batches, we can actually function properly when we’re working with both of these kinds of statements in one file. So if we have a create table, query, and then right after that CREATE TABLE query, I have an insert query, the CREATE TABLE query must finish executing first before the insert query actually executes. Likewise, similar issues arise with updates deletes, selects, all those sorts of things. So we can’t mix and match DDL and DML, the DDL has to be executed first. And then the DML can follow afterwards, assuming that the tables haven’t been created yet. And so the resolution of these table names and things like variables, functions, all of those sorts of things, that that naming resolution is done per batch. So just like if we declare a variable inside of a function, the scope of that variable is within that function. Very similar idea for when we work with batches in SQL. So the scope of a variable declared locally is going to be within a given batch. And there are ways we can declare global global scopes. And we’ll also show that here in a future video.

But for now, let’s take a look at some examples of batches and the effects that batches have on the way we write SQL queries. First off, I’m just going to show a very simple toy example here. So if I execute this, we notice that we have three different SQL queries, right, are actually three different batches. Sorry. And within those batches, you can see the total execution time and the number and the things that were affected by that batch. So each one of our batches here actually has just one SELECT statement. So here is batch one, batch two, and batch three. But notice how the second batch has an error, but the other two batches are not affected by that error. And this is really important. Because if I MC go over here to my results, you can see that I get Hello, and hello again. But I don’t get this message here, right. I don’t get this message here. But if I actually take out this batch operator, let me comment this out real quick. So if we comment that out, run this again. You notice Now I only have two things that actually executed. Here’s my first batch. So line one, and then here is my second batch. But notice that I only get Hello out as a result. So this batch executed completely and successfully, this batch executed, but the first query in that batch failed. And since the first query in that batch failed, the rest of the batch was not able to finish executing. So this is equivalent to a function throwing an exception, and the rest of the function doesn’t doesn’t finish executing because an exception occurred. Very similar idea here as an SQL. But the same idea here, this is why variables aren’t visible across batches.

So if I open up this example, here, I have this variable called greeting, which is called Hello. And notice I separated this out as a batch. And so if I execute this, I get an error again, right must declare the scalar variable greeting. So the scope of greeting exists only within the batch, it was declared, it does not exist in this batch, the second batch here. So the scope for our declare statement is local only to the batch that it is that is declared in. And so if I take out this batch operator again, and give us a run, now we actually see our query execute successfully. So this is really some of the fundamental uses of batches. And why batches are important to note the separation between different sets of sets of queries. And because it is really quite common, that we have multiple queries in one single dot SQL file. So this is really common usage. And so we can use that ghost statement to separate those out. So if we also, let’s see here, try this example here. Data Definition Language statements, creating objects must be the only things inside of the batch. So if I have additional things in my batch, we run this thing here. Haha, right, we get some issues, right, we get some issues.

So when we create database objects, that can be the only thing inside of that batch. So creating a table, creating a schema, so on and so forth. And so if I need to do other things like data manipulation, language queries, like selects, or even other DDL queries, like drop schema, we have to actually separate those out into batches. So if we try this, hmm, so we actually see that there is error here. But let’s go ahead and take this back out as a batch. There we go. Hmm, right, because I can’t actually create a table on a schema without the schema existing first. And so if I take out this batch here, but I take out that go statement, it actually gives me a syntax error. Because the schema must be created in its entirely entirety, before we can actually add tables to it. So this is the reason why we separate these things out batches. Because if we sum if we submit all of that at once, the queries don’t actually get to view the end results of the objects being created until after the queries have been finished executing until the batch finishes. And so if we want to create things and create database objects, and then run queries on that database object in the same file, they must be separated out in batches. So let’s take a look at one more example of our batches and action here. So this is just to showcase some more issues or more information on naming resolution with batches. And so if you notice, when I run this, I get an error down here, I get a couple batches that work successfully.

So my first batch which is this DROP TABLE statement up here at the top that runs successfully. And then I have a second batch here, which is my CREATE TABLE query that runs successfully. And then I have an alter statement, and then a select statement. But notice, right notice that name for One, my IDE doesn’t actually recognize name as being a valid column name. But my ALTER TABLE statement up here clearly adds that column into my table. But within this scope, right within this scope name does not exist yet name will only exist after this batch finishes executing. And so in order to get this error to go away, I have to separate these two queries out using a batch. And now, that actually works. Right now, the name column actually exists before I run the SELECT clause on it. So this is another issue. And again, my ID is my ID still showing invalid column name here, kind of ignore that error, but it actually executes now and I actually have that name column that exists. So just be careful when you’re working with ALTER TABLE statements, and basically any data definition language query. A lot of the times those are going to need to be separated out into their own batches for your rest of your queries to actually execute properly. But that will conclude our discussion on batches. And coming up next we’ll start to explore some more programmable objects and relation to that