Press "Enter" to skip to content

Curated SQL Posts

Using the Model Database

Published 2021-01-27 by Kevin Feasel

Garry Bargsley explains the utility of the model database:

Below I will outline the model database, which has a unique purpose for SQL Server.

The model database is an empty shell of a database that has the sole purpose of providing a database template for any new User Database created in SQL Server. With that being said, you can make configuration changes to the model database and then expect those baseline settings to be applied to all new user databases.

Read on to see some of the things you can set. I was a bit disappointed in some of the things which model doesn’t apply across to other databases, but it does carry over quite a bit.

Comments closed

Living in the Lakehouse

Published 2021-01-26 by Kevin Feasel

James Serra defines the term “data lakehouse”:

As a follow-up to my blog Data Lakehouse & Synapse, I wanted to talk about the various definitions I am seeing about what a data lakehouse is, including a recent paper by Databricks.

Databricks uses the term “Lakehouse” in their paper (see Lakehouse: A New Generation of Open Platforms that Unify Data Warehousing and Advanced Analytics), which argues that the data warehouse architecture as we know it today will wither in the coming years and be replaced by a new architectural pattern, the Lakehouse. Instead of the two-tier data lake + relational data warehouse model, you will just need a data lake, which is made possible by implementing data warehousing functionality over open data lake file formats.

While I agree there may be some uses cases where technical designs may allow Lakehouse systems to completely replace relational data warehouses, I believe those use cases are much more limited than this paper suggests.

James is a sharp and perceptive fellow, so read the whole thing.

Comments closed

Power BI: New Features for Data Analysts

Published 2021-01-26 by Kevin Feasel

Tomaz Kastrun looks at some new functionality in Power BI which might interest data analysts:

Small multiples is a layout of small charts over a grouping variable, aligned side-by-side, sharing common scale, that is scaled to fit all the values (by grouping or categorical variable) on multiple smaller graphs. Analyst should immediately see and tell the difference between the grouping variable (e.g.: city, color, type,…) give a visualized data.

In Python, we know this as trellis plot or FacetGrid (seaborn) or simply subplots (Matplotlib).

In R, this is usually referred to as facets (ggplot2).

Read on for an example of this, as well as two other features, as well as how you might have worked with these ideas in Python and R.

Comments closed

Estimating Row Counts without Statistics

Published 2021-01-26 by Kevin Feasel

Matthew McGiffen dives into rules of thumb:

I find this is a question that comes up again and agan. What estimate for the number of rows returned does SQL Server use if you’re selecting from a column where there are no statistics available?

There are a few different algorithms used depending on how you’re querying the table. In this post we’ll look at where we have a predicate looking for a fixed value.

Read on for a few examples, noting that this specifically relates to tables and not things like table-valued parameters.

Comments closed

The Pain of SELECT *

Published 2021-01-26 by Kevin Feasel

Grant Fritchey strongly recommends against SELECT *:

Quite a few years ago, I wrote a post about SELECT * and performance. That post had a bit of a click-bait title (freely admitted). I wrote the post because there was a really bad checklist of performance tips making the rounds (pretty sure it’s still making the rounds). The checklist recommended a whole bunch of silly stuff. One silly thing it recommended was to simply substitute ALL columns (let me emphasize that again, name each and every column) instead of SELECT * because “it was faster”.

My post, linked above, showed that this statement was nonsense. Let’s be clear, I’m not a fan of SELECT *. Yes, it has some legitimate functionality. However, by and large, using SELECT * causes performance problems.

I’ll use SELECT * for one-off queries (well, something like SELECT TOP(100) * but same difference), but it’s a really bad practice to include that in application code for the reasons Grant mentions.

Comments closed

Change Tracking Runthrough

Published 2021-01-26 by Kevin Feasel

Erik Darling provides a runthrough (which is a walkthrough but at a faster pace) of change tracking in SQL Server:

I’ve been working with CDC and CT way too much, and even I’m annoyed with how much it’s coming out in blog posts.

I’m going to cover a lot of ground quickly here. If you get lost, or there’s something you don’t understand, your best bet is to reference the documentation to get caught up.

Check it out.

Comments closed

Using OAuth 2 in R Packages

Published 2021-01-25 by Kevin Feasel

Maelle Salmon explains how OAuth 2 works and also how you can use it in R packages:

When writing an R package wrapping an API using OAuth 2.0 you’ll need the user to grant access to an “app”, which will allow to create an access token and a refresh token. The access token will then often be passed to the API in a header when making requests, whilst the refresh token would be posted in a query string when the access token needs to be renewed.

Your problem is: how do I imitate a third-party app? Thankfully for you, in most cases the complexity can be handled by the httr package. For other cases, or if you want to e.g. only use curl, you will have to get creative. 

Read on for more detail.

Comments closed

SSAS and Database Loading

Published 2021-01-25 by Kevin Feasel

Nigel Foulkes-Nock explains why SSAS might not be available even if the service is running:

When starting SQL Server Analysis Services (SSAS) Tabular, the Service is quick to report that it has started. In my opinion, this Status is not entirely accurate – SSAS may be running but you cannot access data until it has loaded all associated SSAS Databases into memory and performed its consistency checks. This can take a long time.

After starting SSAS, if you try to browse the Databases using SQL Server Management Studio (SSMS) then SSMS becomes unresponsive. You will receive errors if you try to query a SSAS Database. It’s busy but it doesn’t report as such and doesn’t give any clue of how long it’ll take.

Read on for the explanation.

Comments closed