The Bit Bucket

SQL Interview: 39 Char data types and row compression

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

SQL Server temporary stored procedures vs Snowflake anonymous stored procedures

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

Opinion: Buying new software to do what you already can do

I remember that back when Microsoft introduced the ribbon for the menu in Microsoft Excel, I was at a product group session where they explained why they did it. They told us that when they summarized all the requests from users for features to add to Excel, there was something amazing:

Almost every feature was already there

So, what they had was a discoverability issue, not a feature gap.

What prompted me to write this opinion today, is that I see exactly the same sort of issue in my data-related work. People are unaware of what their existing tooling and software can do, and wish features would be added, yet those features are already there.

2025-04-21

SDU Tools: Script Analytics View

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

SQL Interview: 38 Table Compression Candidates

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

Book Review: A Developer's Guide to Building Resilient Cloud Applications with Azure

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

Book Review: Business Intelligence Essentials You Always Wanted To Know

I recently had the opportunity to read a preview of Business Intelligence Essentials You Always Wanted To Know by Irene Tobajas from LUKiN Consulting.

I like the way each chapter started with a set of key learning objectives.

I also like the way that quiz questions are integrated into the text (even though there’s the odd question where I didn’t love the answer. For example, saying that Data Mining wasn’t a component of BI).

2025-04-17

Book Review: Architecting Power BI Solutions in Microsoft Fabric

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

SQL Interview: 37 DROP CREATE vs DISABLE REBUILD for SQL Server indexes

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

Opinion: Do your applications encourage discoverability?

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