In my last T-SQL 101 post, I mentioned that GO was just a word that’s used to separate batches. In fact, it’s not a SQL word at all. If you actually send the word GO to the server, it wouldn’t know what you’re talking about.
I mentioned that the word is only understood by the program that you type the script into. SSMS also allows us to make use of another additional trick in relation to GO.
2019-08-26
The image below shows a T-SQL script.
It’s an example of contents of a file that you could have on your disk so the entire thing could be called MyProgram.sql. Inside this script file, we’ve got a few different types of things happening here.
The word script applies to all of the contents of the file.
You can see the word GO there a number of times. It’s a special word that’s not actually a T-SQL keyword. It’s a batch separator. It only has meaning to the tool you’re using to execute queries, like SQL Server Management Studio or Azure Data Studio.
2019-08-19
Take a look at the following query:
I’ve asked for a list of the databases from the sys.databases view. But rather than executing it against whichever database I was already connected to, I’ve said USE master; to change to the master database.
The command will be sent to the master database instead of any other database that I might have been connected to. At that point, master becomes my “current database”.
2019-08-12
Important concepts in any development are reusing code, and abstraction. Look at the first WHERE clause here:
I’ve asked SQL Server to return rows where the CreditRatingID is the one that has the maximum rating. I might not know how to find the maximum rating but if there is code that finds it for me (i.e. dbo.GetMaximumRating()), I don’t need to know that.
This is an example of a function that retrieves a value, and then I can use the value in my own query, without having to copy all the code that’s needed, and without even having to know how it works.
2019-08-05
Look at the following query:
I’ve asked SQL Server to return orders where there are order comments. I did that by saying:
OrderComments IS NOT NULL
If I had said IS NULL, I would have returned all the orders with no comments. The interesting concept though, is NULL.
The first thing to understand is that NULL isn’t a value. That’s why we can’t say:
WHERE OrderComments = NULL or WHERE OrderComments <> NULL
2019-07-29
In the previous T-SQL 101 post, I showed the TOP operator. One of the challenges that comes up though, is if I say SELECT TOP (10) FROM dbo.Products ORDER BY Size, what if there are 5 items with the first Size, and 7 products with the second Size? Which rows get returned when I just ask for 10?
Perhaps what you want is the TOP (10) but then, when you get to the end of the 10, if the next one has the same value you might want to continue on. T-SQL has a way of doing this. We say WITH TIES as shown in the code below:
2019-07-22
The TOP operator is used to restrict the number of rows being returned. If you’re familiar with other database engines, you might have used a LIMIT operator. TOP is a bit like that.
You can see that in the first query shown below:
If you look at the bottom query, you can see that it can also be used to limit the rows to a percentage instead of a number of rows.
2019-07-15
The T-SQL IN operator allows you to choose from a list of matching values.
In the top case shown below:
I’ve said where StateName is one of QLD, NSW, or VIC.
Now see that’s exactly the same as if I had written the option shown at the bottom where I said:
where StateName equals QLD or StateName equals NSW or StateName equals VIC.
The two options work exactly the same and mean the same thing, so having an IN operator is logically equivalent to having a whole lot of predicates joined by OR operators.
2019-07-08
The T-SQL BETWEEN operator let’s you specify a range of values. So in this case shown here:
I’m asking for rows where the DateCreated is between the first date and the second date. Notice how I’ve written the date here. We’ll talk about that a bit more in a later post. 2012, zero one, twenty-one is the 21st of January 2012. This is one of the formats that makes dates consistent.
2019-07-01
WHERE clauses can have more than one predicate.
In the case shown above, I’m saying
“where the description starts with hydro and not size equals 370ml”
The operators AND, OR, and NOT can be used to form a group of logical decisions that need to be made.
Now notice that in this case, it’s exactly the same as if I had said
“where the description starts with hydro and size is not equal to 370ml”
2019-06-24
