Sql-Server

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

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

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

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

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

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

I’ve previously talked about how the WHERE clause is used to limit the rows that are included in a query. If we’re using a GROUP BY, then WHERE is determining what goes into the grouping. But what if you want to apply a limit that’s based on the outcome of the grouping? That’s what HAVING does.

If I execute the following query:

SELECT Size, 
       COUNT(ProductID) AS NumberOfProducts
FROM dbo.Products 
WHERE IsShownOnPriceList <> 0
GROUP BY Size;

I see this output:

2021-03-08

I recently wrote about the ALL operator in T-SQL. It’s used to check how a given value compares to a list of values. That list generally comes from a sub-query that returns a single column. While the ALL operator isn’t well known, there are also other related operators SOME and ANY.

SOME and ANY are synonyms. You can use either.

Instead of returning whether or not all the values in a list meet the logical operation, these return whether any of the values in the list meet the requirement.

2021-03-05