The Bit Bucket

I was pleased to recently get to review a new book by Nagaraj Venkatesan called Architecting Power BI Solutions in Microsoft Fabric . Nagaraj was an MVP for two years and now a cloud solution architect working for Microsoft.

NOTE: I reviewed the book while it was in preview.

I liked the way that Power BI was discussed for different personas: Business Users, Data Scientists, Administrators.

I was puzzled to see a requirement in the front matter for Angular 9, TypeScript 3.7, and ECMAScript 11. I couldn’t find any relevance to them in the book. Hopefully it will be removed before publication.

2025-04-16

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

I see software houses all the time that are worried about why users find their software hard to use. Or at least harder than they thought they would find it.

One thing that I see worrying users time and again, is applications that discourage you for discovering how they work. Users are afraid to click on options that they haven’t used before, because they’re worried that  something will happen that they can’t undo.

2025-04-14

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

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

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 have a database attached to a SQL Server 2022 server.

You need a copy of the database on a SQL Server 2017 server.

How can you do that?

Answer:

It’s messy.

You cannot restore a backup from a later version to an earlier version, even if the database compatibility level is at the lower version.

2025-04-08

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