The Bit Bucket

Power BI: AddWeekdays function for Power Query M language

In our free SDU Tools for Developers and DBAs was an AddWeekdays function. Now that was for T-SQL. Recently though, I needed to do that for Power Query. While the M language has a wonderful set of date-related functions, it didn’t have this one.

That made it time to write one. Here’s the code that’s required:

So how does it work?

Forgive the formatting to fit this window, but let’s take a quick look through it:

2019-04-09

T-SQL 101: 12 Using Statement Terminators in T-SQL

I think T-SQL is an odd language in many ways. One aspect of this is the looseness of the language. The best example of this is the way that statement terminators (i.e. the semicolons at the end of the statements) are optional.

Back in 2005, the optionality of the statement terminators was deprecated. The SQL Server team told us to start using them, because one day they’ll be required.

2019-04-08

Podcast Review: The Drop Out

I like serious investigative journalism that’s released as podcasts. I enjoyed Serial with their story about Adnan Syed, and in a similar vein there’s now The Drop Out from ABC Radio.

I’m surprised that I hadn’t heard about Elizabeth Holmes or Theranos, or at least that if I had heard about them, I hadn’t taken much notice of them. After listening to The Drop Out, I’m stunned that it hadn’t been something I already knew all about.

2019-04-05

SQL: Cannot drop the database because it is being used for replication

If you go to drop a database, and you’re told you can’t because it’s being used for replication, you might be a little confused. For example, if you check your publications and subscriptions, you might see this:

Given there aren’t any, how could the database be being used for replication? Well, each database has a property that indicates if it’s enabled for replication, even if there aren’t any publications. That’s what’s stopping you from dropping the database.

2019-04-04

SDU Tools: StartOfMonth and EndOfMonth in T-SQL

In our free SDU Tools for developers and DBAs, we have a large number of useful functions. The topic for today is two simple ones: StartOfMonth and EndOfMonth.

SQL Server 2012 added a function EOMONTH. It returns the end of the month date for any given date. I really, really don’t like abbreviations like this. There is no need to save three characters.

I asked why it wasn’t ENDOFMONTH but was told that EOMONTH was the name of the function in Excel. It’s sad to think that’s the guiding principle for T-SQL function names. I also don’t get why sometimes the words in the names are separated by underscores, and other times they aren’t. Surely END_OF_MONTH would be more readable.

2019-04-03

Opinion: What are six SQLs and four Jiras?

Over the years, many product names have become verbs that describe what the product does. The typical example is to google for something, or to super-glue something to something else, and so on. The first and dominant product in their markets tends to become associated with the action that they perform.

But was has me puzzled in recent years, is I keep hearing company names used as nouns for something that their applications deal with.

2019-04-02

T-SQL 101: 11 The Basic Syntax of a T-SQL SELECT Statement

The most basic form of SELECT statement that we used to get information out of a table is the word SELECT followed by a list of columns or expressions that we want, and then details of where we need to get them from. Typically, that’s a table.

The basic syntax of a SELECT statement in SQL Server T-SQL is as follows:

Committees are curious things, and I can’t say that I always agree with their outcomes. I suspect if I’d been designing this, I would have had:

2019-04-01

SDU Podcast: Jakub Szymaszek on SQL Server 2019 Always Encrypted with Secure Enclaves

Yesterday I had the pleasure of recording another new podcast on a SQL Server 2019 topic. Jakub Szymaszek is a PM on the SQL Server team, working around security.

In this podcast, Jakub and I discussed the current state of security and encryption in SQL Server, starting from cell-level encryption (CLE) and transparent database encryption (TDE) in SQL Server 2005. We then discussed the introduction of Always Encrypted and Data Masking in SQL Server 2016.

2019-03-30

SQL: Our new SQL Server Replication online training course is now available

We’re really excited that our latest online on-demand course is available. If you’ve ever wanted to get comfortable with SQL Server Replication, now’s the time to do it, from the comfort of your own place, and at your own pace.

The course Working with SQL Server Replication is being released at just $195 USD, but for my readers, there’s an extra special offer: Use coupon code REPLAPR19 to get another 25% off, but just till Apr 13.

2019-03-30

SQL: Is REPLICATE in SQL Server not replicating enough?

Even though I’ve worked with SQL Server for what feels like forever, I still constantly trick myself up with T-SQL commands that give me output that’s different to what I was expecting. This week, it was REPLICATE.

What should the output of the following statement be?

SELECT REPLICATE(‘Hello’, 10000);

If you said the word Hello repeated 10,000 times, or didn’t know at all, read on.

The clue comes if I execute the following statement:

2019-03-29