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

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: Query Performance Level: Medium

Question:

In SQL Server a column that is defined with a bit data type can only have the values 0, or 1, or it can be NULL if the column allows it.

Given such a small number of possible values, are bit columns ever useful in indexes? Can you give an example of when they might or might not be useful?

2021-03-26

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 a CREATE TABLE statement to define a new table, after each column, you can write NULL or NOT NULL, but doing that is optional.

Why is it a good practice to include it, and what happens if you leave it out? Are the columns defined as NULL or as NOT NULL? Are there any exceptions to this?

2021-03-25

I have a lot of people who recommend books to me. I order many of them. If the book has impressed one of my friends, I’m keen to see what they liked about it. That’s how I came to read Living Forward: A Proven Plan to Stop Drifting and Get the Life you Want by Seth Godin.

The title is making a pretty big claim, but the content? It’s ok.

2021-03-24

Dynamic Data Masking was added to SQL Server back in 2016. I’ve almost never used it since. Why? There were two reasons:

#1: The first reason was that it didn’t work in conjunction with other security features. In particular, you couldn’t use dynamic data masking in conjunction with Always Encrypted. From the start, I’ve been telling the product group that this is a misstep. There’s very little data that I’d want to hide from my own staff, that I’m happy still being visible to a hosting provider.

2021-03-23

In my last T-SQL 101 post, I mentioned ROW_NUMBER. It let you put a row number or position beside each row that was returned. Sometimes though, you want a rank instead. A rank is similar but it’s like a position in a race.

In the example above, I’ve used RANK to produce an ordering, based on an alphabetical listing of city names. Notice there’s Abercorn Abercorn Abercorn and then Aberdeen. So, like in a race, if three people came first they all get the value 1. The next person is fourth. Three people came forth, so then the next one is 7th, and so on.

2021-03-22

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