Press "Enter" to skip to content

Author: Kevin Feasel

T-SQL Tuesday 120 Round-Up

Published 2019-11-21 by Kevin Feasel

Wayne Sheffield summarizes T-SQL Tuesday #120:

The end of the first 10 years of T-SQL Tuesday blogging occurred this month, with me hosting T-SQL Tuesday #120. The theme this month was to talk about something you’ve seen that made you think “What were you thinking?” (you can read the invitation here). We had several bloggers jump in and post their thoughts. So let’s just jump into a quick recap of who posted what (for each blogger, I also include a link to their Twitter account, their main blog, and the link to their T-SQL Tuesday #120 post).

Click through for the recap.

Comments closed

Text Processing Tools and Methods

Published 2019-11-20 by Kevin Feasel

Ines Roldos takes us through several tools and techniques used in text processing:

Text processing is the process of analyzing and manipulating textual information. This includes extracting smaller bits of information from text (aka text extraction), assign values or tags depending on its content (aka text classification), or performing calculations that depend on the textual information. 

Since we naturally communicate in words, not numbers, companies receive a lot of raw text data via emails, chat conversations, social media, and other channels. This unstructured data is filled with insights and opinions about different topics, products, and services, but companies first need to organize, sort, and measure textual data to get access to this valuable information. One way to process text data is manually, which has been the most popular method – up until now.

We’re still in the early days of text processing, but there have been some nice improvements over the past decade.

Comments closed

Debugging Code in R

Published 2019-11-20 by Kevin Feasel

Marina Wyss walks us through debugging techniques in R:

There are many ways to approach these problems when they arise. For example, condition handling using tools like try(), tryCatch(), and withCallingHandlers() can increase your code’s robustness by proactively steering error handling.

R also includes several advanced debugging tools that can be very helpful for quickly and efficiently locating problems, which will be the focus of this article. To illustrate, we’ll use an example adapted from an excellent paper by Roger D. Peng, and show how these tools work along with some updated ways to interact with them via RStudio. In addition to working with errors, the debugging tools can also be used on warnings by converting them to errors via options(warn = 2).

Read on for a survey of what’s available in R. It’s a lot more than writing a bunch of print statements. H/T R-Bloggers

Comments closed

Power Query: Types from Text

Published 2019-11-20 by Kevin Feasel

Imke Feldmann shares a new M function with us:

Look at the M-code that has been generated in the formula bar: “type text” is in quotes and this makes it a text-string. The function dialogue doesn’t give an option to actually select or enter a type expression. This would be without quotes like so:

MyFunction( type text )

So if I aim to feed my function a text value to dynamically create a type from it, I need a function that returns a type and accepts a text value to identify the actual type.

Click through for Imke’s version as well as a second version in the comments.

Comments closed

Character Generation with T-SQL

Published 2019-11-20 by Kevin Feasel

Bill Fellows shows off what you can do with character generation in T-SQL:

The mod or modulus function will return the remainder after division. Modding a value is a handy way to constrain a value between 0 and an upper threshold. In this case, if I modded any number by 26 (because there are 26 characters in the English alphabet), I’ll get 0 to 25 as my result.

Knowing that the modulus function will give me 0 to 25 and knowing that my target character range starts at 65, I could use the previous expression to print any number’s ascii value like SELECT CHAR((2147483625 % 26) + 65) AS StillB;. Break that apart, we do the modulus, %, which gives us the value of 1 which we then add to the starting offset (65).

He also provides a DB Fiddle for it.

Comments closed

Named Volumes in Docker with SQL Server 2019

Published 2019-11-20 by Kevin Feasel

Andrew Pruski takes us through one of the biggest changes with SQL Server 2019 in containers:

I’ve seen a few people online asking how to use docker named volumes with the new SQL Server 2019 RTM images. Microsoft changed the way SQL runs within a container for the RTM versions, SQL no longer runs as the root user.

This is a good thing but does throw up some issues when mounting volumes to create databases on.

Let’s run through what the issue is and how to overcome it.

Click through to see what you need to add to your DOCKERFILE to get things working.

Comments closed

Installing Power BI Gateway

Published 2019-11-18 by Kevin Feasel

Paulina Nowinska shows how to install the Power BI Gateway in its two separate modes:

This On-premises was created for a multi-developer environment. Here multiple people can work on the same Data Gateway if the administrator authorized them before. With this Data Gateway, you will have much more fun than Personal Mode. Why? Because of on-premises support not only Power BI just like his brother Personal Mode. Here you can provide quick and secure data transfer between data which is not in the cloud and Microsoft cloud services: PowerApps, Microsoft Flow, Azure Analysis Services, and Logic Apps and of course Power BI. Depending on your needs, you can choose one of them.

Click through for step-by-step instructions on both techniques.

Comments closed

Cross-Validation Versus Regularization

Published 2019-11-18 by Kevin Feasel

Nina Zumel takes us through a pair of techniques for avoiding overfitting:

Cross-validation is relatively computationally expensive; regularization is relatively cheap. Can you mitigate nested model bias by using regularization techniques instead of cross-validation?

The short answer: no, you shouldn’t. But as, we’ve written before, demonstrating this is more memorable than simply saying “Don’t do that.”

Definitely worth the read.

Comments closed

Fun with CHAR(0)

Published 2019-11-18 by Kevin Feasel

Kenneth Fisher learns a bit about the 0 byte:

Ok, now things are getting interesting. An ASCII value of 0? I’ve never heard of that. I honestly didn’t know it was possible. As it happens, yes, it’s a real value and in SSMS it does a few strange things.

In the comments Denis Gobo is right: the 0 byte is the null terminator, which should appear at the end of a variable-length string to indicate that there’s nothing more to read there.

Comments closed