Azure-Sql-Db

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

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

Hamida Rebai Trabelsi started her professional career in Tunisia working for multinational corporations as a software developer, then served as a .NET consultant in Canada. She is currently a senior advisor and information and solution integration architect. She has been awarded as Most Valuable Professional (MVP) in Developer Technologies and as a Microsoft DevHero by Microsoft.

Thanks to the people at PackT, I recently had the pleasure of reviewing another one of her new books: A Developer’s Guide to Building Resilient Cloud Applications with Azure .

2025-04-18

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

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

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 file path.

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

It supports both drive paths and UNC paths.

In case, you need to process individual paths, to help with that, we’ve added a number of scalar functions: FilePathToFileName, FilePathToFolderName, FilePathToFileExtension. You can see them in action here:

2025-04-10

Sometimes, you want to insert a row if it’s missing but update it if it’s already there. We had asked Microsoft for an UPSERT statement as that’s what it’s called in other databases.

What we got in SQL Server 2008 instead was a MERGE statement. It’s more flexible than an UPSERT statement.

In the example above, I’ve said I want to merge into the dbo.CinemaGroups table. Note that this statement also has an optional INTO word, just like INSERT does. That’s then considered the target table. Only one table can be modified in a single query, including with a MERGE query.

2025-04-09

An UPDATE statement is the way we change data in an existing row or multiple rows of a table. In the example above, I’ve said I want to update dbo.OrderContacts; that’s the table.

I asked SQL Server to set the OrderContact column to Terry Jones, but only where the OrderID is 1. That’s how we modify values in a table.

If I needed to modify more than one column, after Terry Jones, I could just put a comma, and then I don’t have to use the word SET again.

2025-04-07