The Bit Bucket

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.

When you execute a script, it might look like it's all sent to the server at once, but that's not what happens. SSMS finds the word GO, breaks the script up into batches, and sends them to the server, individually.

So, in this case, the following four batches are sent, one batch at a time:

1. USE master;
2. CREATE DATABASE Interesting;
3. USE Interesting;
4. CREATE TABLE dbo.Information
   ( InformationID int,
   Thoughts varchar(20)
   );
   INSERT INTO dbo.Information
   VALUES (1, 'Hello');

We describe Batch 4 as having two statements, the CREATE TABLE statement, and the INSERT statement.

What is GO really?

I mentioned that GO is a batch separator. You could actually use almost anything instead of GO. In the Tools>Options>Query Execution>SQL Server section in SSMS, you could change it to something else.

I'd suggest that you avoid changing it because you want your scripts to run on other machines and programs as well, and GO is the standard batch separator.

I have a Star Trek friend. All his scripts have the word ENGAGE instead of GO.

And of course a nasty trick for new DBAs is to change it to the word SELECT and watch them try to work out what's wrong.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

If you ask SQL Server developers how to calculate the length of a string in T-SQL, most would tell you immediately that you need to use the LEN() function. What most don't realize though, is that it doesn't actually work properly.

The T-SQL LEN() function ignores any trailing spaces in a string.

That's not how a string length function should work, or how it works in most languages. So, in our free SDU Tools for developers and DBAs, we added a simple tool that works properly. It's called StringLength.

Nothing complex. It takes a string, and returns its length in characters.

Find out more

You can see it in action in the main image above, and in the video here:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

 

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".

Then by saying USE PopkornKraze; I've asked SQL Server to change to working with the PopkornKraze database. The commands that execute after that USE statement will be sent to the server, with PopkornKraze as the current database.

Did I need to change?

If you look at the third query, I've used a three part name to refer to the view in the master database. So even though the command will be sent with the PopkornKraze database as the current database, I'm referring to a view in the master database.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

In my last SDU Tools post, I described a tool for calculating date dimension columns. While dates are often enough, in many data warehouses, you also need to allocate time periods across each day as well. To make that very easy, in our free SDU Tools for developers and DBAs, we added a tool called TimePeriodDimensionColumns.

This tool is a table-valued function that takes a time (actual time of the day), and the length of each time period, in minutes. In the example in the main image above, I've asked for 8:34PM and I've said that each period is 15 minutes long.

You can also see the list of returned columns in the main image above.

To make it easy to create a time period dimension for an entire day, we also added a tool called GetTimePeriodDimension.

Find out more

You can see TimePeriodDimensionColumns in action in the main image above, and see both tools in the videos here:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

 

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.

When a function only returns a single value, we call it a scalar function.

If the function is user-written code (rather than system-supplied code in SQL Server), we call it a scalar user-defined function.

Note: There have been some performance problems over the years that are related to using T-SQL scalar user-defined functions, but we'll talk about that another day.

Table-Valued Functions

As well as just returning single values, functions can also return entire tables of data.

In the second query in the image above, a table-valued function (TVF) that takes a single parameter (i.e. the StateCode), and returns details of the cinemas in that state. Again I don't need to know how the code works or what it does. I can just use it.

Once again, there are system-supplied table-valued functions and user-defined table-valued functions.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

When you're building a data warehouse using SQL Server, one of the first things that most people start to design is a Date dimension. To make that really, really easy, in our free SDU Tools for developers and DBAs, we added a tool called DateDimensionColumns.

This tool is a table-valued function that takes a date, and returns a set of columns that many will find enough for a good Date dimension. You can see the list of returned columns in the main image above.

Range of Dates

If you then combine it with our DatesBetween tool that returns a set of dates between a start and end date, by using CROSS APPLY, you have a nearly complete solution for creating a date dimension.

And that's exactly what another of our tools GetDateDimension does!

Find out more

You can see it in action in the main image above, and see both tools in the video here:

You can see GetDateDimension here:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

 

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.

What 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

NULL represents a missing or unknown value. It's not a particular value; it's saying that the column has no value.

It's really important to understand that this is not the same as having an empty string or a 0 value. If a string has no characters in it, it's still a value. For example, if AddressLine2 is a string with no characters in it, that's entirely different to not having a value for AddressLine2.

Being NULL is a state that a column can be in, rather than a value that it has.

Calculations with NULL

Now it's important then, to start to understand that as soon as you have NULL involved in equations, things don't exactly work like you might imagine.

Here's a hint:

NULL does not equal NULL

The expression:

NULL = NULL

returns NULL, not true or false. So when you include this in a WHERE clause, things get messy. A WHERE clause predicate works when the expression is true. The expression in this predicate:

WHERE OrderComments IS NULL

can be true or false. This particular predicate would return all rows where there are no comments, but this expression in this predicate:

WHERE OrderComments = NULL

always returns NULL, not true or false, so no rows would be returned by that query.

Getting your head around NULL is a critical skill in working with the SQL language.

Note that there are options that allow you to change how NULL works and would make NULL = NULL return true, but don't use those.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

When you look at a SQL Server build number (actually a "Product Version", you'll see they have a format like this:

14.0.3256.1

The first number is the major version number. The second number is the minor version number. The third number is the build number. The fourth number is the release number.

These numbers are a little awkward to work with directly in T-SQL so, in our free SDU Tools for developers and DBAs, we added a set of tools that can do that for you.

ProductVersionToMajorVersion extracts the major version from a product version.

ProductVersionToMinorVersion extracts the minor version from a product version.

ProductVersionToBuild extracts the build number from a product version.

ProductVersionToRelease extracts the release number from a product version.

All the above functions take a ProductVersion as a parameter.

We also added a table-valued function ProductVersionComponents that extracts all the values from a product version at once.

Find out more

You can see them in action in the main image above, and in the video here:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

 

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:

When I used WITH TIES, SQl Server continues to return rows as long as they still match the same value that you are using for the order.

One challenge is that it also means that you do not know how many rows are going to be returned. If the entire table had 600ml as a Size, and I asked for the TOP (10) WITH TIES, I'd get the whole table back because they would all match.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.