SQL Day 2021 is on, and I'd love to see you in my Power BI pre-con

One of my favourite conferences each year is SQL Day. It's run by an enthusiastic group from Poland, and when I've attended in person, I loved it. This year it's virtual, and the upside of that, is you can attend from anywhere.

As part of the conference, I'm running a pre-con workshop. It's a low cost one day course on How I Implement Power BI in Enterprises. You'll find info on it here. The course is running on Poland time, but it looks to me like the times will suit a pretty wide variety of people, including from here in Australia.

More info here:

I'd love to see you there.

 

SQL Interview: #10 System-defined default names

This is a post in the SQL Interview series. These aren't trick or gotcha questions, they're just questions designed to scope out a candidate's knowledge around SQL Server and Azure SQL Database.

Section: Development
Level: Medium

Question:

When you define a column default with code like below:

the system will define the name of the default. Can you give examples of why specifying the name of the default instead of letting the system supply it would be a good practice?

Answer:

There are several reasons. Here are three:

If you ever need to change the default value, you will need to know the name of the default constraint to be able to remove it, before you add a new default. This is much easier if you already know the name of the default.

If you ever need to drop the column, in SQL Server, you must first drop any default on the column. Again, that is much easier if you already know the name of the default. (Note that other database engines like PostgreSQL do not allow you to name defaults, but they also automatically drop them when dropping columns).

If you have created the table in multiple databases and you are using database comparison tools to check for differences, having consistent names in the script avoids the potential detection of a difference. (Note that some comparison tools can ignore system names like these).

 

SDU Tools version 21 is now released for download

Version 21 of our free SDU Tools for developers and DBAs is now released and winging their way out to our SDU Insiders.

You can find details on the tools here.

If you haven't been using SDU Tools yet, I'd suggest downloading them and taking a look. At the very least, it can help when you're trying to work out how to code something in T-SQL.

Along with the normal updates to SQL Server versions and builds, we've added the following new functions:

CreateAnalyticView – this new tool helps to support automating the scripting of analytic views based upon existing data warehouse tables. It will be particularly of use to anyone following our methods in the Implementing Power BI in the Enterprise workshops or upcoming book.

WeekdayAcrossYears – this one was requested by Dave Dustin. It's a new function that returns the day name for a given day and month number for a range of years. (For example, which day of the week will my birthday be for the next ten years?)

SQLServerType – this new function returns the type of server that you are attached to. For example, it can help you to know if you are connected to a SQL Server database or an Azure SQL Database or an Azure SQL Managed Instance.

The following tools have been upgraded:

SplitDelimitedString – this tool now uses a much faster method. It works the same, just much faster.

ListUseOfDeprecatedDataTypes – this procedure has been updated and provides an additional output column that gives you an example of the T-SQL script that you might need to execute to correct the inappropriate data type. (Thanks to Michael Miller for the suggestion)

GREATER and LEAST have been added to the list of reserved words.

The list of exceptions in the ProperCase function has been expanded. (Thanks to John Reitter for the suggestions)

And of course, there are a few corrections included as well.

StartOfFinancialYear – we fixed a scenario where the wrong year could be returned.

ChineseYears – we fixed a typo in the year of the goat (Thanks again to Dave Dustin)

UnixTimeToDateTime2 and DateTime2ToUnixTime – now use bigint values to ensure they'll avoid the Unix 2038 problem. (Thanks to Bob Roberts for this one)

Scripting functions that output column lengths for nchar and nvarchar were corrected.

Check out all the available functions, procedures, and views here.

SQL Interview: #9: Computed columns in table definitions

This is a post in the SQL Interview series. These aren't trick or gotcha questions, they're just questions designed to scope out a candidate's knowledge around SQL Server and Azure SQL Database.

Section: Development
Level: Medium

Question:

Consider the following code:

Will the CreatedDate column return the same value in both SELECT statements?

Answer:

When you define a computed column, the value is calculated when it is SELECTed. In this case, the two SELECT operations will occur at different times, and different values will be returned for that column.

It is possible with some computed columns to add the term PERSISTED. In that case, the value is calculated at INSERT or UPDATE but is then stored. The same value would be returned every time it is SELECTed.

However, in this case, you cannot apply PERSISTED to the computed column, as the expression SYSDATETIME() is not deterministic.

 

T-SQL 101: #94 Returning messages to the client by using PRINT and RAISERROR in T-SQL

There are times when you're writing in any programming language, that you want to output messages back to the client. In T-SQL, the statement provided for that is the PRINT statement.

PRINT is simple enough to use. You give it a  string to return and it prints that string.

It's different to the SELECT statement, because the SELECT statement returns a rowset (i.e. a set of rows).

In the main image above, you can see a script with both a PRINT and a SELECT. When that script is executed, two things happen. The output of the SELECT statement is returned in the Results tab in SQL Server Management Studio as below:

But notice that the PRINT output isn't there. Instead, it's returned in the Messages tab as below:

Using RAISERROR instead of PRINT

PRINT is actually a specific instance of the RAISERROR statement. Instead of using PRINT, you could use RAISERROR like this:

Notice that the output is identical. The first parameter to RAISERROR is the message, the second is the severity, and the third is the state. In this case, any severity from 1 to 10 would have worked. You can just make the state be 1 and ignore it until we discuss RAISERROR in much more detail later.

RAISERROR and NOWAIT

So why would you ever use RAISERROR instead of PRINT? After all, PRINT is simpler.

The answer is related to when the messages are returned. For example, if you put PRINT statements in a stored procedure, it's only after the stored procedure completes that you'll see the output.

If however, you use the NOWAIT option in RAISERROR, the messages come back immediately.

This can be very useful when you are debugging code.

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.

SQL Interview: #8: Why should you avoid the use of db_datareader and db_datawriter?

This is a post in the SQL Interview series. These aren't trick or gotcha questions, they're just questions designed to scope out a candidate's knowledge around SQL Server and Azure SQL Database.

Section: Security
Level: Intro

Question:

Many applications connect to SQL Server databases with users that are members of the db_datareader and/or db_datawriter roles?

Why should you avoid using those roles?

Answer:

db_datareader and db_datawriter are fixed database roles that were provided for convenience. They aren't a good option from a security perspective.

Whenever you add a user (or group) to any fixed role, you are assigning them a collection of permissions. By definition, that list of permissions will need to include at least all the permissions they require.

However, invariably when you use fixed roles, you are also assigning additional permissions or access to objects that aren't required.

A more secure solution is to create a role and grant it only the permissions that are required, and then assign the user (or group) to that new role.

 

SQL Interview #7: Are statement terminators ever required in T-SQL?

This is a post in the SQL Interview series. These aren't trick or gotcha questions, they're just questions designed to scope out a candidate's knowledge around SQL Server and Azure SQL Database.

Section: Development
Level: Intro

Question:

Many versions of SQL require every SQL statement to be terminated with a semicolon.

Since it was created, T-SQL has listed statement terminators as optional.

Are there any situations where they are required, and not optional?

Answer:

Since SQL Server 2005, some T-SQL statements must be separated from previous statements by a statement terminator.

The statements involved are WITH, SEND, RECEIVE.

In addition, a MERGE statement must have a statement terminator.

Using statement terminators is generally considered good practice, even though many of the Microsoft-supplied tools generate scripts without them.

Ever since SQL Server 2005, the product deprecation list has stated that the optional nature of statement terminators is deprecated. For this reason alone, you should use statement terminators.

But there are other potential issues. Consider this nasty example from fellow MVP Erland Sommarskog:

What is not immediately obvious is that THROW would never be executed. Instead, it would be interpreted as a column alias for ERROR_MESSAGE().

Using semicolons as statement terminators avoids this type of issue.

 

Book Review: The Subtle Art of Not Giving a F*ck

I get a lot of book recommendations from friends. One that I'd heard about a number of times was Mark Manson's book The Subtle Art of Not Giving a F*ck: A Counterintuitive Approach to Living a Good Life so I thought I'd check it out.

I don't overly love the title. I think having expletives in book titles is a bad omen. For me, they are in the same category as, and are reminiscent of, childhood fart jokes. And the book is full of endless repetition of the same expletives. I can only imagine Manson thought they made for good shock value. For me, they don't.

The premise of the book is pretty straightforward. Manson argues that many aspects of life are pretty messed up and for so many of these aspects, there's nothing you can do about them.

This is not a "think positive" book. The analogy from the publishers is that instead of telling you how to turn lemons into lemonade, he's telling you that you need to learn to stomach lemons. At least then you have a chance to be happy.

Clearly, having bad things happen to you can sometimes produce good lessons, but obviously, too many bad things can't be borne.

What is an interesting discussion in this book, is about how you define happiness. I agree with him that there's little point worrying about things that you can't change. I'm a little more positive than him though, on how many things you can actually change.

Many people seem to go through life with the "if only" syndrome. If only I could get a better job, I'd be happy. If only I could pass this exam, I'd be happy. Often these same people have a very unhappy life in the meantime. A different frame of mind might help there.

The Verdict

I liked a lot of the messaging in this book, once you ignore the expletives designed to slap you in the face.  6 out of 10 for me.

 

 

SQL Interview: #6: Multi-row INSERT operations

This is a post in the SQL Interview series. These aren't trick or gotcha questions, they're just questions designed to scope out a candidate's knowledge around SQL Server and Azure SQL Database.

Section: Development
Level: Intro

Question:

Look at the following multi-row INSERT statement:

The column CustomerGroupName is defined as NOT NULL so the second row cannot be inserted.

How many rows are inserted by this statement, assuming there are no other errors?

Answer:

INSERT statements are atomic, even for multi-row INSERT statements. Either all the rows are inserted, or none are.

If one row fails (as in this case), no rows are inserted.

 

T-SQL 101: #93 Restarting row numbering by using PARTITION BY

Imagine that I've used ROW_NUMBER to number all the cinemas in my database. I'll get values from 1 to the maximum number of cinemas. But what if I want to number the cinemas within each city? i.e. Aberdeen has three cinemas, so number them 1, 2, and 3. But when we get to the next city, start the numbering again. We can do this by adding PARTITION BY to the OVER clause.

PARTITION BY and OVER can do many, many things and I don't want to get too far into complexity on this in today's post, but I do want to mention that when you have a window function like ROW_NUMBER, RANK, DENSE_RANK, and NTILE, instead of just returning one large set of values, you can partition the values by using PARTITION BY.

As another simple example, imagine I need a row number for each order line in an order. I can just partition by the order number.

It's not just row numbering though. With NTILE, I could allocate the results of exams into bands, but partition the entire set by which class the students are in, or by which exam they took.

Partitioning the data into groups can be very useful.

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.