Press "Enter" to skip to content

Curated SQL Posts

Reading Data from Deleted Columns

Published 2025-01-07 by Kevin Feasel

Michael J. Swart checks the typewriter ribbon:

It’s hard to destroy data. Even when a column is dropped, the data is still physically there on the data page. We can use the undocumented/unsupported command DBCC PAGE to look at it.

This is tied in with how we can drop a column in SQL Server and have it not take a very long time: because when we drop the column, we’re just modifying the metadata associated with the table and telling SQL Server to ignore this bit here. Do read the whole thing, and also check out a fun comment from Paul White.

Comments closed

Setting RCSI on a Database in an Availability Group

Published 2025-01-07 by Kevin Feasel

Haripriya Naidu makes a change:

I was working on modifying isolation level of database from Read Committed to Read Committed Snapshot(RCSI) and had to get exclusive access to the database. After letting the application team know about it and having stopped their processes, I tried to set the database to SINGLE_USER but it errored out.

It turns out that you cannot set a database to single user mode if it is in an availability group or part of database mirroring. Nonetheless, there is still a way to make this change. Read on to learn more.

Comments closed

Sending E-mails in SSRS over Ports other than 25

Published 2025-01-07 by Kevin Feasel

Eitan Blumin fixes an error:

After configuring an Email subscription, the subscription result shows: “Failure sending mail: One or more errors occurred.“. In this blog post I will share how I investigated and resolved one such failure.

My first step in troubleshooting this error was to query from the ExecutionLog3 view inside the ReportServer database. I normally do this to check if and why a report subscription has failed its run.

However, in this case all the log records showed success (rsSuccess status):

Read on to see what this indicates, how Eitan was able to troubleshoot the problem, and the ultimate fix.

Comments closed

Transposing Data Frames in R

Published 2025-01-06 by Kevin Feasel

Steven Sanderson does a switcharoo:

Data manipulation is a crucial skill in R programming, and one common operation is transposing data frames – converting rows to columns and vice versa. Whether you’re cleaning data for analysis, preparing datasets for visualization, or restructuring information for machine learning models, understanding how to transpose data frames efficiently is essential. This comprehensive guide will walk you through various methods to transpose data frames in R, complete with practical examples and best practices.

Read on for a few approaches to the problem.

Comments closed

Generating an Example of SQL Injection

Published 2025-01-06 by Kevin Feasel

Vlad Drumea points out that SQL injection is still a thing:

SQL injection, also refereed to as SQLi, is a security vulnerability that allows attackers to modify the queries that an application makes to its underlying database.
This type of vulnerability can allow attackers to interact with data that they are not normally able to access, including data belonging to other application users.

For at least a decade, injection attacks were either number one or number two on OWASP’s top 10 list. It dropped all the way to number 3 in 2021. We’ll see how it looks with the 2025 OWASP top 10 application security vulnerabilities list, but in the meantime, check out Vlad’s post.

Comments closed

Finding Capacity-Level Fabric Settings with Semantic Link Labs

Published 2025-01-06 by Kevin Feasel

Sandeep Pawar lists some Microsoft Fabric properties:

Just before the holidays last year Michael Kovalsky released version 0.8.10 of Semantic Links Labs with a bunch of new helpful functions, among them list_server_properties() lists properties of an Analysis Services instance. As you know, in Fabric, the workspace acts as a server which is tied to a capacity. You define these server properties in the Capacity Settings. As far as I am aware, there wasn’t an API to get these capacity settings for audit/monitoring/debugging. With this new function, you can programmatically get the Semantic Model (i.e. Power BI workload) settings.

Click through for an example.

Comments closed

Data Lakes, Warehouses, and Lakehouses

Published 2025-01-06 by Kevin Feasel

Noa Shavit disambiguates three terms:

A data warehouse is a repository and platform for storing, querying, and manipulating data. Warehouses are particularly suited for structured data used for decision support and business intelligence. Modern data warehouses have become more efficient, flexible, and scalable (particularly in the context of massively parallel processing and distributed computation), but they still bear the mark of their early development in the previous century.

The data warehouse concept dates back to data marts in the 1970s. After a long incubation period, the idea began to bear fruit commercially at IBM in the late 1980s and early 1990s. Data warehousing improved on the inefficiency of data marts, siloed data stores maintained by individual departments. 

Click through to learn more about each of the three concepts and how they relate.

Comments closed

Useful Tidyverse Functions

Published 2025-01-02 by Kevin Feasel

Tomaz Kastrun shares some code snippets:

Data engineering is important step that helps improve data usability, data exploration and data science. Preparing the data needs therefore needs to be done in a manner, that is easy to read, repeat and exchange between others engineers.

Tidyverse has a lot of data engineering functions, chaining different functions for getting most of your data. All six examples will show combinations of functions chained together for great result set.

Click through for those examples.

Comments closed

Automating tSQLt Test Execution via Azure DevOps Pipeline

Published 2025-01-02 by Kevin Feasel

Olivier Van Steenlandt automates some tests:

Before we dive into “how to automate”, I would like to explain why bothering about Unit Test Automation. Unit Tests in general are a good practice to identify potential issues with new source code upfront. Therefore, executing Unit Tests is a good idea. By automating Unit Tests, developers can be sure that Unit Tests will be executed for every single deployment that happens.

In the upcoming step-by-step guide, Unit Test automation will be implemented in a YAML pipeline. The pipelines I’m using are very simplified in comparison with many other pipelines I have seen in the past.

Read on for the process.

Comments closed