The Bit Bucket

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

In an earlier post, I showed how to create a function that worked out if a date was a weekday or not. In this example, I’ve used that function to create a more complex function that adds a number of weekdays to a given date.

In Power Query, go to the Edit Queries option and choose to add a new query. (You could also start by duplicating an existing query). Then on the View menu, choose the Advanced Editor and replace any code that’s there with this:

2019-07-05

I’ve seen a number of interesting discussions lately around how to handle deletes in database tables. One discussion in Stack Overflow had a variety of suggestions.

One easy option in SQL Server 2016 and later is to use temporal tables as they’ll keep the history for you. But there are a number of reasons why they might not be suitable.

Sometimes, you just want to stop rows being deleted, and to set an IsDeleted flag instead. If you don’t want to depend upon the application doing that for you, the easiest way to accomplish that is to use INSTEAD OF triggers.

2019-07-04

SQL Server tables should have a primary key. I’m not talking about a clustering key (that’s a different topic) but a key that logically identifies each row, and isn’t ever NULL.

I have SQL purist friends that argue that a table without a primary key really isn’t a table.

When I’m reviewing database designs, one of the things I look for is the lack of a primary key on tables. It’s not just a purist issue. Without a primary key, other features like replication are problematic.

2019-07-03

I really love SQL Server Integration Services (SSIS). In fact, I wish Microsoft was giving it far more love than it is. So many of my clients use it, but unfortunately, not everyone uses it well, or for the right things.

One really common mistake is to perform lots of row-by-row logic within the SSIS packages. Let’s take an example of loading a fact table in a data warehouse as an example:

2019-07-02

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

I spend a lot of time doing consulting that involves Power BI. One of the core skills for working with Power BI is getting yourself comfortable with the M language that’s used in Power Query.

I was asked the other day, how to create a function that determines if a date is a weekday or a weekend. That’s actually quite easy. The Date.DayOfWeek function can be adapted to do that.

2019-06-28

In the T-SQL language you can specify a positive integer constant in an ORDER BY clause as I’ve shown in the main image above.

Please don’t do this !

For those that haven’t used this, the number refers to the position of the column in the SELECT list. If you do this, you are creating really fragile code and also code that’s really hard to read, particularly as the statements get more complex.

2019-06-27

I’ve lost count of the number of times I’ve seen people searching for details on what the latest SQL Server builds are. In a previous post, I mentioned that our free SDU Tools for developers and DBAs had a view called SQLServerProductVersions that showed all the SQL Server builds.

But that shows all the builds. What if you just want the latest details? We added a new view that works that out for you. It’s called LatestSQLServerBuilds. You can see it in use in the main image above.

2019-06-26

I’m an unashamed fan of TechSmith and it’s products. In particular, SnagIt and Camtasia.

Whenever I’m asked for a list of products that I wouldn’t want to live without, SnagIt is near the top of that list. I use it all day, every day. I’ve tried a number of screen capture programs over the years, and I’ve watched people struggle to do simple things, just to avoid buying a commercial application. Don’t be that person. Use the right tool.

2019-06-25