Press "Enter" to skip to content

Month: April 2020

Running SQL Server on a Raspberry Pi

Published 2020-04-23 by Kevin Feasel

Andrew Pruski walks us through Azure SQL Database Edge:

This allows SQL Server to run on ARM devices which will expand the range of SQL Server considerably.

Just think how many devices are out there that run ARM. That includes my favourite device, the Raspberry Pi.

So, let’s run through how to get SQL running on a Raspberry Pi!

The process is fairly simple—among all of the steps, the one I had the most trouble with was actually finding the right marketplace, as I ended up in a different one somehow.

Comments closed

Dynamic String Formats for Power BI Calculation Groups

Published 2020-04-23 by Kevin Feasel

Kasper de Jonge shows how we can build out different format strings based on calculation groups in Power BI:

One of the cool things of calculation groups is they cannot just be used to apply a calculation over your “base measure” but also apply a dynamic formatstring. You could do this without calculation groups before too as I described here. This method had one big drawback though, it uses the FORMAT function and when doing that all results are transformed into strings. This does help in showing the right format, but you lose a lot of other functionality like sorting or conditional access. Now with calculation groups we can do custom formatting and keep the data type. How does that work?

Read on for an example.

Comments closed

Using Plan Guides to Bootstrap Query Store

Published 2020-04-23 by Kevin Feasel

Hugo Kornelis gives us a way to use Query Store to force plans on a different server, using plan guides as the instrument:

Query Store only allows you to force plans that the Query Store has “seen” on that instance, and in that database. If you have a query and you want to force a specific plan, you will need to first ensure that the query runs, at least once, under the right circumstances to create the desired plan, so that the Query Store can capture it. Sometimes that is easy, in which case this blog is not for you.

What if you know you need to force the execution plan, you know you’ll get that execution plan only when a lot of data is already in the database, and you need to ship your software with an empty database to your customers? How do you set it up to force the plan you need?

Click through for the answer, and I give Hugo bonus points for using Raleigh as the example.

Comments closed

Java Extension for SQL Server Now Open Source

Published 2020-04-23 by Kevin Feasel

Nellie Gustafsson announces some exciting news:

Today, we’re thrilled to announce that we are open sourcing the Java language extension for SQL Server on GitHub.

This extension is the first example of using an evolved programming language extensibility architecture which allows integration with a new type of language extensions. This new architecture gives customers the freedom to bring their own runtime and execute programs using that runtime in SQL Server while leveraging the existing security and governance that the SQL Server programming language extensibility architecture provides.

This opens up the possibility for additional languages. .NET languages (C# and F#) would be a natural fit and languages like Go might have enough dedicated support to give this a try.

Comments closed

Date and Time Storage in SQL Server

Published 2020-04-23 by Kevin Feasel

Randolph West covers the internals of how date and time data types are stored in SQL Server:

DATE is the byte-reversed number of days since the year 0001-01-01, stored as three bytes. It goes up to 9999-12-31, which is stored as 0xDAB937. You can check this value by reversing the bytes and sticking them into a hex calculator. 37 B9 DA equals 3,652,058, which is the number of days since 0001-01-01.

If you try to cast 0xDBB937 as a DATE value (by incrementing the least significant bit DA by 1), it will throw a conversion error. There is obviously some overflow detection that protects against corruption in a date type.

Randolph looks at DATE, TIME, DATETIME(2), and DATETIME and explains how each is storedon a page.

Comments closed

Automated ML and Data Scientists

Published 2020-04-22 by Kevin Feasel

Sophia Rowland takes us through an experiment:

Ever since automated machine learning has entered the scene, people are asking, “Will automated machine learning replace data scientists?” I personally don’t think we need to be worried about losing our jobs any time soon. Automated machine learning is great at efficiently trying a lot of different options and can save a data scientist hours of work. The caveat is that automated machine learning cannot replace all tasks. Automated machine learning does not understand context as well as a human being. This means that it may not know to create specific features that are the norm for certain tasks. Also, automated machine learning may not know when it has created things that are irrelevant or unhelpful.

To strengthen my points, I created a competition between myself and two SAS Viya tools for automated machine learning. To show that we are really better together, I combined my work with automated machine learning and compared all approaches. Before we dive into the results, let’s discuss the task.

The results are in line with my expectations: a good automated ML tool will make life easier, but doesn’t replace the expert system of a human.

Comments closed

R Checkpoint Package Update Now in Beta

Published 2020-04-22 by Kevin Feasel

Hong Ooi announces that a revamp of the checkpoint package is now in beta:

Checkpoint has been around for nearly 6 years now, helping R users solve the reproducible research puzzle. In that time, it’s seen many changes, new features, and, inevitably, bug reports. Some of these bugs have been fixed, while others remain outstanding in the too-hard basket.

Many of these issues spring from the fact that it uses only base R functions, in particular install.packages, to do its work. The problem is that install.packages is meant for interactive use, and as an API, is very limited. For starters, it doesn’t return a result to the caller—instead, checkpoint has to capture and parse the printed output to determine whether the installation succeeded. This causes a host of problems, since the printout will vary based on how R is configured. Similarly, install.packages refuses to install a package if it’s in use, which means checkpoint must unload it first—an imperfect and error-prone process at best.

In addition to these, checkpoint’s age means that it has accumulated a significant amount of technical debt over the years. For example, there is still code to handle ancient versions of R that couldn’t use HTTPS, even though the MRAN site (in line with security best practice) now accepts HTTPS connections only.

Click through to see what’s in the new checkpoint package.

Comments closed

Fun with tempdb

Published 2020-04-22 by Kevin Feasel

Andy Mallon walks us through setting up tempdb:

There are three problems I’ve got to fix. I need to (1) remove those two extra files, (2) grow the tempdb log file, and (3) even out the size of the data files (and shrink them a little to make room for the larger log file. We’re going to tackle these in the reverse order than I listed them–partially out of necessity, and partially because it’s going to be easier.

Click through to see how Andy sets up tempdb. This is a good way to set up tempdb.

Comments closed

The Pain of Code Noise

Published 2020-04-22 by Kevin Feasel

Chris Johnson talks about a concept dear to me:

Basically code noise is anything that pulls your attention away from what the code is supposed to be doing, or obscures the true nature of the code in some way. It’s not something we consider enough when writing T-SQL code, but I think there is a lot to be said for writing code the next person will be able to read.

As a small example, I was debugging something recently and found that all of the insert statements had ORDER BY clauses. I couldn’t work out why these were making me so angry, after all it’s not doing anything to hurt performance, and in fact isn’t doing anything at all, until one of the other devs in the office pointed out that it’s one example of the code noise that the whole code base is filled with.

Chris provides us a couple examples of noise. My bottom line on this is, develop to the minimum required standards of what the computer needs (i.e., accurate data, fast enough, etc.) and give the humans maintaining the code a fighting chance. Spend more time making it easy for humans and make everybody’s life easier.

Comments closed

Ownership Chaining in SQL Server

Published 2020-04-22 by Kevin Feasel

K. Brian Kelley walks us through the concept of ownership chaining in SQL Server:

Ownership chaining is a security feature in SQL Server which occurs when all of the following conditions are true:

– A user (which could be an app through a login/service account) tries to access an object that makes a reference to another object. For instance, the user tries to execute a stored procedure that accesses other objects or a SELECT from a view that accesses other tables.
– The user has access to the first object, such as EXECUTE rights on the stored procedure or SELECT rights on the view.
– Both objects have the same owner.

In this case, SQL Server will see the chain between the object the user called and the object being referenced. SQL Server will also determine that the owner for both objects is the same. When those conditions are met, SQL Server will create the ownership chain.

Read on for an in-depth example of ownership chaining and how it solves certain problems around managing database rights.

Comments closed