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

2021-04-09

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?

2021-04-08

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.

2021-04-07

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:

INSERT Sales.CustomerGroups
(
    CustomerGroupID, CustomerGroupName
)
VALUES (1, 'Group A'),
       (2, NULL),
       (3, 'Group C');

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

2021-04-06

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.

2021-04-05

IT people get accused all the time, of being out of touch with other people, and for using language that is overly complex.

So many times lately, I’ve come across dialog boxes like the one above.

It’s clearly asking a Yes/No question. Why on Earth doesn’t it give the user Yes and No as answer choices?

Why do we do this to users?

If you’re building apps, please don’t do this. If you ask a question that in normal language would lead to a Yes or No response, please give the users Yes and No as answer choices.

2021-04-02

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:

If you create a table using the following code, what would the name of the primary key constraint look like?

CREATE TABLE Sales.CustomerGroups
(
    CustomerGroupID int IDENTITY(1, 1) PRIMARY KEY,
    CustomerGroupName nvarchar(50) NOT NULL
);

How could you specify the name?

2021-04-01

I love short stories, so I was really excited when one of the Audible Originals a while back was a set of eight short stories. The Other Animals is a compilation of short stories by a variety of authors (Daniel Mallory Ortberg, Ken Liu, R. Eric Thomas, Lulu Miller, Shruti Swamy, Max McClure, Kaeli Swift, and Kelly Weinersmith).

I really didn’t know what to expect with this book. The ratings from other listeners are high. Audible describe it as being “written by a crew of scientists, literary writers, and comics, and informed by animal behavioral science - explores what animal perspectives can show humans about the world we all share.”

2021-03-31

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 are writing an INSERT statement in T-SQL, the column list is usually optional.

Why would including it be a good idea? When would it ever be required (i.e. not optional)?

Answer:

2021-03-30

I’ve been talking about the basic window functions in T-SQL and one that’s not well known but is surprisingly useful is NTILE.

I’m not sure on the name but it’s probably short for percentile. I don’t know why they didn’t call it a slightly more meaningful name, but what it says is take the output and break it up into bands or chunks of data.

So if I say NTILE(10), that’ll give me a tenth of the rows with a value of 1, another tenth of the rows with a value of 2, and so on.

2021-03-29