A Fine Slice Of SQL Server
Kenneth Fisher needs someplace to put all of his great ideas:
Last week I posted about a request to get a list of services. The first problem I ran into was expanding the column width, now I need to get the output off my screen and into a file to send the requestor. The cmdlet out-file is perfect for this and it’s pretty simple.
Click through to see how the Out-File cmdlet works as well as another cmdlet you may wish to use instead.
Daniel Hutmacher is making a list and checking it twice:
If you’ve worked with reporting, you’ve probably come across the following problem. You have a list of values, say “A, B, C, D, K, L, M, N, R, S, T, U, Z” that you want to display in a more user-friendly, condensed manner, “A-D, K-N, R-U, Z”.
Today, we’re going to look at how you can accomplish this in T-SQL, and what this has to do with window functions and gaps and islands.
Read on for a really good demonstration of how powerful window functions and STRING_AGG() can be.
Hasan Savran peeks through the windows:
WINDOW Operations in SQL Server can be hard to understand. I believe one of the main reasons for that is the long and repeated code it needs. SELECT…WINDOW Clause will help us to remove repeated code and hopefully it will make the WINDOW Operations more user-friendly.
I am really happy about this syntax change, as often times, we have to re-use the same window frame (PARTITION BY and ORDER BY) or a base frame with a minor change (add one more column to the end). This removes the kind of repetition which makes queries harder to read and introduces subtle bugs.
Comments closedJosh Darnell provides timeless advice:
As Deborah’s invite post suggests, this is a “that one time at that client” story. I was working at a consulting firm, and we had written an app for a particular client. Part of this application’s workflow involved users uploading images alongside some other information. These were not particularly large images in the grand scheme of things – they were taken by a microscope, and were a few kilobytes each, maybe.
However, this app had been in use for a long time. And as you might have guessed from the title of this post, each of these images was stored in a single table in the database that backed this application
Yeah, that’ll be a problem… Read on for some recommendations on how to avoid the issue. One thing I would add is FileTable, which came out in SQL Server 2012. In that case, the files are actually stored on disk but are queryable via T-SQL. It introduces its own set of problems but I do have some fond feelings about having used FileTable in the past.
Comments closedAmit Khandelwal announces a change:
To make this approach scalable & manageable, we will publish SQL Server container images to MCR based on the distribution’s most recent version, rather than publishing it for all versions of both RHEL and Ubuntu. Here’s an example to help you understand:
SQL Server 2017 supports Ubuntu 18.04 as the most recent distribution; thus, going forward SQL Server 2017 container images based on the Ubuntu 18.04 image will only be published to MCR and we will not publish the SQL Server 2017 container images for Ubuntu 16.04.
This seems fine to me. They focus on image support for the most recent fully-supported version and hopefully make it a bit better. I do wonder if that will change their overall Linux policy around version support, as that could be trickier to sell.
Comments closedRick Pack updates a package to support a project:
I am happy to announce a contribution to the biscale package that makes printing shorter labels using SI prefixes (e.g., 1,000,003 => 1M and 1,324 => 1.3k) far easier. This makes printing the legend in an attractive easier, although you can tell by the picture above that I still struggle with optimal uses of the cowplot package’s draw_plot(). I would love for the legend and map to be centered under the title.
The new si_levels argument for bi_class_breaks() takes a logical value of TRUE or FALSE for either a single or two-unit vector, with a single unit vector causing the specified value to be applied to both the X and Y variables. This matches Prener’s convenient functionality for the number of digits function dig_lab, as he requested in the Github Issue I created for this addition. Note that si_levels rounds the input number, if appropriate, based on the digits indicated by dig_lab, which defaults to 3.
Click through to get access to the update, as well as to see some of the visuals Rick put together with it.
1 CommentDerek Jones digs into an image:
A month or so ago, I found a graph showing a percentage of PCs having a given range of memory installed, between March 2000 and April 2020, on a TechTalk page of PC Matic; it had the form of a stacked density plot. This kind of installed memory data is rare, how could I get the underlying values (a previous post covers extracting data from a heatmap)?
Read on for an interesting attempt at reverse-engineering the original numbers used to create an image. H/T R-Bloggers.
Comments closedLiam Kalita continues a series:
We’ll assume you have a basic understanding of HTML and JavaScript, but you should be able to follow along with a basic programming background. Having a little knowledge of Linux shell commands would be beneficial for some of the terminal commands for generating directories, but you can also do most of it in VSCode using the user interface instead.
Let’s attempt an exercise in creating a small React+Plumber app; this will be very similar to a previous blog post recreating this tutorial {shiny} application using Python Flask.
Click through to see how to build the app. The final part of the series will show how to host the app.
Comments closedTomaz Kastrun shares some advice:
Tidyverse provides a handful of great functions for operating across multiple columns simultaneously. Across is a function, that makes it easy to apply the same transformation over numerous columns in summarise() and mutate() functions.
Across accepts two arguments; a) array of columns and b) function or list of functions to be applied to selected columns.
Check out eight tips for working with packages in R’s tidyverse.
Comments closedRichard Swinbank diagnoses a problem:
I encountered this error recently while using Azure Data Factory’s (ADF) new(ish) Script activity to run a SQL query. It took me a while to find a fix, and when it happened again two weeks later I’d completely forgotten it. With a bit of luck, writing it down will help me remember next time – and if it helps you too, great
Read on to see what the problem was and how Richard solved it.
Comments closed