Press "Enter" to skip to content

Month: September 2022

Pagination in SQL Server

Published 2022-09-28 by Kevin Feasel

Chad Callihan is paging us:

Pagination (or paging) is the process used to return a set of results in pages. When it comes to pagination in SQL Server, you have a couple of different options. Today, we’ll look at three of them. The first being pagination with the use of FETCH/OFFSET, the second using a CTE, and the third with the use of ROW_NUMBER. Let’s look at a brief example of how each works.

For each example, I’m using @PageSize to determine how many results to return and @PageNumber to determine what page of results to return from the StackOverflow2013 database.

Chad has three techniques listed here. Pagination is one of those things which should be simple but all of the simple options have major performance drawbacks once you get to datasets whose sizes require pagination.

Comments closed

SQL Server on Windows 11 WSL

Published 2022-09-28 by Kevin Feasel

Andrew Pruski provides an update on SQL Server running on Windows Subsystem for Linux:

This happens because up until now, WSL did not support systemd. However recently Microsoft announced systemd support for WSL here: –
https://devblogs.microsoft.com/commandline/systemd-support-is-now-available-in-wsl/

This is pretty cool and gives us another option for running SQL Server locally on linux (great for testing and getting to grips with the Linux platform).

So how can we get this up and running?

Read on for instructions, as well as a couple pitfalls Andrew ran into along the way.

Comments closed

Using Google Fonts on RMarkdown Pages

Published 2022-09-27 by Kevin Feasel

Thomas Williams has a change of font:

Setting a different font is a minor tweak to an R Markdown file that might help it fit better with a corporate or company look. Google Fonts is an industry-accepted method to reference fonts on web pages and can be used with R Markdown rendered by Shiny. There’s no need to download anything while developing the R Markdown file in RStudio, or viewing it in a web browser from a Shiny server.

I’ve recently used this technique to integrate the “Atkinson Hyperlegible” font, a font with “…greater legibility and readability for low vision readers…” from https://brailleinstitute.org/freefont. Since Shiny themes already specify a font, the steps below show how to override that with the Google Font, demo’d with the spacelab Shiny theme.

Click through to see how you can use this font, as well as others, in Shiny apps.

Comments closed

Real-Time Streaming ETL with Kafka and Debezium

Published 2022-09-27 by Kevin Feasel

Dursun Koc doesn’t have time for batched ETL:

Debezium is not extracting data using SQL. It uses database log files to track the changes in the database, so it has minimum effect on the source system. For more information about Debezium, please visit their website. 

After the data is extracted, we need Kafka Connect to stream it into Apache Kafka in order to play with it and reshape it as we required. And we will be using ksqlDB in order to reshape the raw data in a way we are required in the target system. Let’s consider a simple ordering system database in which we have a customer table, a product table, and an orders table, as shown below.

Read on for an overview as well as a link to the GitHub repo where you can try this all out.

Comments closed

RANKX in DAX

Published 2022-09-27 by Kevin Feasel

Marco Russo and Alberto Ferrari do some ranking:

Ranking is one of the most frequent calculations in Power BI reports. Needing to determine the top products, countries, customers and such is extremely common. RANKX offers a powerful and very fast way to produce ranking. Nonetheless, its use takes some understanding.

In this article we introduce the RANKX function and provide a few interesting examples of how it can be used. RANKX is not a complex function to learn. Nonetheless, most newbies find it intimidating because they do not fully grasp its internals. Once they learn exactly how RANKX works, its use becomes really simple.

Click through to learn how it works.

Comments closed

Using the DAC with Dockerized SQL Server

Published 2022-09-27 by Kevin Feasel

Joey D’Antoni needed to use the Dedicated Administrator Connection:

Because as shown in the image above, the table in question is a system_table, in order to query it directly, you need to use the dedicated administrator connection (DAC) in SQL Server. The DAC is a piece of SQL Server that dedicates a CPU scheduler, and some memory for a single admin session. This isn’t designed for ordinary use–you should only use it when your server is hosed, and you are trying to kill a process, or when you need to query a system table to answer a twitter post. The DAC is on by default, with a caveat–it can only be accessed locally on the server by default. This would be connected to a server console or RDP session on Windows, or in the case of a container, by shelling into the container itself. However, Microsoft gives you the ability to turn it on for remote access (and you should, DCAC recommends this as a best practice), by using the following T-SQL.

Read on to see how, as well as what else you’d need to do to get it working.

Comments closed

Understanding Purview Pricing

Published 2022-09-27 by Kevin Feasel

Rolf Tesmer disambiguates:

Like all services in Azure, there’s associated costs when using the service, and naturally Microsoft Purview is no different. If interested in reading the standard pricing model for Microsoft Purview it has been outlined here – and follows a similar layout to all Azure price models.

However – as a result of such a broad range of capabilities, its pricing model is one of the more difficult to understand!

Read on for a PDF which hits the various charges you’ll see.

Comments closed

CI/CD for Synapse Link for SQL Server 2022

Published 2022-09-27 by Kevin Feasel

Kevin Chant makes some changes:

In another post I showed how you can use CI/CD to update both ends of Azure Synapse Link for SQL Server 2022 using Azure DevOps. Allowing you to update both a SQL Server 2022 database and an Azure Synapse Analytics dedicated SQL Pool in the same deployment pipeline.

By my own admission, that method can become complex. Plus, I showed some more advanced concepts in that post. With this in mind, I have decided to cover an easier way in this post.

Read on for the simpler technique.

Comments closed

Modulus Computations on Large Numbers

Published 2022-09-27 by Kevin Feasel

Daniel Hutmacher does the math:

The modulus is the remainder of a division of two integers*. Suppose you divide 12 by 4, the result is 3. But divide 11 by 4, and the result is 2.75. This could also be expressed by saying that 11/4 is 2 with a remainder of 3. Computing that 3 is the work of the modulo operator, which in T-SQL is represented by the % operator.

Let’s explore how to compute the modulus of large numbers in SQL Server, and how this is useful in the real world.

Daniel’s example includes IBAN validation, though I think he’s secretly working on breaking asymmetric encryption…

Comments closed