The Bit Bucket

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: Medium

Question:

I have an application that connects to the database as a user called ReaderApp. The only role that ReaderApp has been added to is db_datareader. The application was previously only using SELECT statements to read data that was required.

2021-03-19

Many clients hire me to carry out job interviews on their behalf. If they’re hiring someone for a data-related role, they want to make sure they’re hiring the right person. That usually means two things:

• The person will fit well within the culture of the organization
• The person is technically competent.

I can’t help with the first one. If I’ve worked with the client for a long time, I might have a pretty good idea but ultimately, that’s something the client needs to decide.

2021-03-18

A few weeks back, I finished reading The Lean Startup: How Constant Innovation Creates Radically Successful Businesses by Eric Ries.

There have been a lot of books on Lean methodology over the years, so I didn’t know what to expect with this one. But it was published back in 2001 (i.e. the turn of the century) and that’s when the Lean methodology was still fairly new in entrepreneur circles.

I’ve seen others comment that this was the “single most informative book on the iterative entrepreneural process”.

2021-03-17

I’ve been amazed as the pandemic has continued, how many companies that claim to understand remote work, still really don’t get it. I’ve decided that a good test of that, is whether job advertisements still show a city or location for the work.

Now, not everyone can work remotely, and not everyone has anywhere suitable to work from remotely, but the majority of knowledge workers can do just that. That’s who I’m talking about here.

2021-03-16

In SQL Server 2000 and earlier versions, I often heard people ask “How do I output a row number beside each row that’s output in my query?”

I remember some people arguing that it wasn’t a valid request, as it didn’t feel “set-based” but it was an appropriate request, and it could be dealt with in a set-based manner. Sometimes it’s very, very useful to be able to do that.

2021-03-15

There’s a café in Melbourne that I’ve liked going to for a while. It’s not far from where I live when I’m in Melbourne, and it has all the makings of a really nice cosy suburban café. But there’s something missing.

When I sat there the other day, my meal came as ordered but it was just a shadow of what it normally looks like. I put that down perhaps to a different chef that day. It’s not great that they haven’t all agreed on what those meals are usually made like, but that could have been a one off thing.

2021-03-12

In the last few weeks, I’ve seen a surprising number of database scripts that deploy procedures and functions by dropping the object if it exists, then recreating it. Please don’t write scripts like this.

When you drop a procedure, you not only drop the procedure, but you drop all permissions associated with it. If you then create the procedure again, you’ll have the procedure, but you won’t have the permissions.

2021-03-11

Over the years, I’ve had a number of friends tell me that they really enjoyed Starting Strength by Mark Rippetoe, and illustrated by Jason Kelly. Many regard it as one of the best and most useful fitness books ever written. So I had to read it, even though some of its strongest supporters (pun intended) revered it, almost to some sort of cult status.

What an interesting book.

As I age, I realise the importance of mobility and core strength. I just wish I’d put more importance on it during my life.

2021-03-10

I’m a big fan of Azure Data Factory (ADF), but one of the things you need to get used to with tools like this, is that the UI keeps changing over time. That makes it hard for several reasons:

• It’s hard to train people. Any recorded demo you have will show them things that no longer exist, within a fairly short period of time.
• Every time a cosmetic change occurs, it immediately devalues blog posts, tutorials, etc. that are out on the Internet.

I think Microsoft don’t quite get how much blog posts, etc. supplement their own documentation.

2021-03-09

It’s unfortunate that the SELECT query in SQL isn’t written in the order that operations logically occur (if not physically). I suspect that’s one of the things that makes learning SQL a bit harder than it needs to be.

Without getting into really complex queries, you need to understand the logical order of the operations.

FROM

The starting point is to determine where the data is coming from. This is normally a table but it could be other sets of rows like views or table expressions.

2021-03-09