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

Question:

You have a table in your database that contains six columns that are defined with the text data type.

When you run checks on your database, these columns are often flagged with deprecation warnings, and so you change each column as follows:

2025-04-25

I’ve been meaning to write about this book for quite a while. Complete Showplan Operators by Fabiano Amorim is a wonderful book, and a great example of the books that our friends at Red-Gate have made available for free, over the years, as part of their Simple Talk series.

Clearly, reading execution plans in detail isn’t for everyone, but any developer working with SQL Server should be across much of this content.

2025-04-24

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:

You have a column in your database that is defined as char(20). Whenever you query it, 20 characters are returned, with space padding any unused characters.

An administrator has suggested applying ROW compression to the table, to save space and improve performance.

2025-04-23

Over the years, I’ve done some work with Snowflake. In fact, I got certified in it at one point, and I’ve stayed across its capabilities. I have friends that work there.

Overally, I found the SQL language that Snowflake offered to be pretty limited, and particularly at the time I spent the most time on it, it seemed to have gaps in what it provided.

One thing that fascinated me though was the idea of an anonymous stored procedure.

2025-04-22

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. One tool that was added a while back is ScriptAnalyticsView.

When we build BI systems, we create a data warehouse with tables that are structured as cleansed versions of the incoming data, and with strong referential integritty and consistency.

When we expose those tables to external BI tools like Power BI, Analysis Services, etc. we limit those tools to a series of views that we create. Generally we put them in a schema called Analytics, but it could have any name.

2025-04-20

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

Question:

A developer in your team has suggested applying PAGE compression to all tables and indexes in your application database.

You are concerned that doing so might impact performance, and that ROW compression might be better.

2025-04-19

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: Administration Level: Advanced

Question:

You need to write code to avoid the impact of indexes during data loads.

You could DROP and CREATE the indexes, or you could DISABLE and REBUILD them.

Which should you choose and why? How would the performance compare between the two options?

2025-04-15

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

Question:

You are using linked servers to refer to tables in a database on another server.

You are concerned that the name of the server appears in three-part table names throughout your code. If the linked server name changes, it would be very messy to locate and correct all references to it.

2025-04-13

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. One common request that we’ve had, is that users need to be able to extract the component parts of a URL.

In the example above, you’ll see that we have added a table-valued function called URLComponents.

It supports protocols, host names, path names, search terms, and anchors.

In case, you need to process individual components, to help with that, we’ve added a number of scalar functions: URLToProtocol, URLToHostName, URLToPathName, URLToSearchTerms, and URLToAnchor.

2025-04-12

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:

You are reading a T-SQL script. One MERGE statement merges data from TableA into TableB, and contains two clauses:

WHEN NOT MATCHED BY SOURCE

and

WHEN NOT MATCHED BY TARGET

What is the difference between these? And what is the most common operation that’s executed in each of these clauses?

2025-04-11