Category: SQL Interview

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:

If you don't include a column list, SQL Server will try to match up the values you are inserting, with the list of columns in the table. It will do that in column_id order (based on what you see in sys.columns) and it will ignore any columns that are auto-generated like IDENTITY columns, computed columns, and rowversion columns.

Sometimes you aren't wanting  to insert all the other columns. Using a column list allows you to specify just the columns that you are inserting.

This is also a good idea as it produces less fragile code. If the table gets redefined with a different order for the columns, your INSERT statement would still work if you have listed the columns you are inserting, in the order that you are providing them.

Column lists are usually optional but recommended, but they are required when you are inserting into a table with SET IDENTITY_INSERT set to ON.

 

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?

Answer:

The range of potential values for a data type is not the issue. The selectivity of the values is critical.

So while an index that includes a value that's evenly distributed (i.e. it's zero in half the rows and one in the other half), is unlikely to be useful, a highly skewed distribution of values can make them very useful.

For example, a query that's finding a hundred incomplete tasks amongst millions of completed tasks, will most certainly find the column useful in an index. It's also an example of what would potentially be useful in a filtered index.

For a more complete discussion, see this previous blog post.

 

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?

Answer:

If you leave it out NULL and NOT NULL in a column definition, many factors determine how the column will be defined. For system-defined data types, SQL Server uses a combination of ANSI_NULL_DFLT_ON SET option and the ANSI_NULL_DEFAULT_ON database option to determine which value to use.

There are several exceptions. For a few examples, a column declared as part of a PRIMARY KEY will bet set to NOT NULL regardless of settings. SPARSE columns will always be set to NULL. For CLR types and alias data types, the NULLability is determined by the data type.

It's always a good practice to write NULL or NOT NULL in the table definition.

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.

For performance reasons, the development team now wants the application to use temporary tables in their queries. They need to be able to create temporary tables, insert data into them, read the data back from them, and drop them..

What additional permissions (if any) need to be assigned to ReaderApp, or which additional roles (if any) should ReaderApp be assigned to?

Answer:

None. All users who have access to the database have permission to work with temporary tables.

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.

But I'm well-placed to help them with the second part, and that's the part that the client often doesn't feel they can do well themselves.

Note: It's not the point of this post, but if you'd like to get me involved in your interviews, please just reach out.

Technical interviews

For the technical interviews, it's not just a basic knowledge test. I aim to work out two things:

• Does the candidate actually understand the technology that they claim to, and at which level of expertise?
• How well do they approach problems that they don't immediately know the answer to?

For their knowledge understanding, I'll start with fairly easy questions so they get comfortable, then use increasing depth to probe different areas, to judge their real understanding in those areas.

For the problem solving, I'll give them scenarios, ask if they have any immediate ideas on potential solutions, then dive into how they'd try to solve it. They can tell me what they'd check, and I tell them what they would have found if they did.

Problem Solving Ability

I'm keen to see how logically they think and how they approach problem solving. I wish I could say that the ability to think logically can easily be learned, but I'm afraid my experience tells me that's just not true.

Back when I worked for HP in the mid 80's, they sent us on a Kepner-Tregoe course on logical troubleshooting. I'm sure it seemed like a great idea, and the course was well constructed. But the people in my class who could logically troubleshoot at the start, were the same ones who could do it at the end. It's great to have a core methodology. But to me, it's all about the ability to think logically, and that takes longer than a week to learn. However, it's a critical skill when getting to the bottom of problems.

Blog Series

I decided to make a blog post series that covers a lot of questions that check core understanding of SQL Server topics. I'll include questions at different levels, and on a variety of SQL Server related topics.

I'm not talking about tricky or gotcha questions, or anything like exam prep questions. Just checks on core understanding.

BTW: I've added a new category called SQL Interview. So you can easily find any/all of them amongst my other posts by using the category filter on the blog. I've also got a series of Business Intelligence questions (focussing on Power BI, Tabular data models, Data Factory, etc.) being prepared.

I hope you enjoy reading these. Nothing is ever absolute in this industry, so if you disagree with any of them, feel free to comment and say why.