Press "Enter" to skip to content

Curated SQL Posts

Against the Grain: Heaps, Clustered Indexes, and Fragmentation

Published 2023-01-10 by Kevin Feasel

Rob Farley cautions against rash decisions on three topics:

We’ve already mentioned how clustered indexes can help avoid some of the problems with heaps, but I’m rarely in favour of the standard clustered index key choice – that of an ever-increasing identity column. The arguments people make for this are typically around avoiding page-splits, and the smallness of the data type. But I see tables where the clustered index is on a unique ID column and nothing ever refers to it.

Read on for a dose of caution on three popular beliefs.

Comments closed

Multi-Language Support for Power BI

Published 2023-01-10 by Kevin Feasel

Marc Lelijveld supports several languages:

In case you’re working in an international company, you might have to deal with multiple languages and cultures at the same time. As not in all countries and businesses, it is common that everyone speaks and communicates in English all day, it can be relevant to support other languages for your Power BI solution. But what do you do? Should you start duplicating your entire solution and translate all elements to a different language? Or develop your entire solution in the corporate native language, like Dutch or German?

In this post I will elaborate on using translations in Power BI, to automatically translate your Power BI model meta data to different cultures. Besides the meta data, I will also elaborate on aspects like visual titles and translating the data itself. Happy translating!

Click through for one way to solve this problem.

Comments closed

Creating an Instance Alias in SQL Server 2022

Published 2023-01-10 by Kevin Feasel

Marco Russo asks, what’s in a name?

If you create server aliases for SQL Server connections, you should be aware of the changes introduced in SQL Server 2022. At SQLBI we use aliases to identify the server’s name in the connection of our samples (Power BI files and Analysis Services model), so this short article should help those who face similar requirements.

Read on to learn what that change is and how you can work around it.

Comments closed

CI/CD and Postgres

Published 2023-01-09 by Kevin Feasel

Ryan Booz thinks about Database DevOps:

At the core, the biggest problem we needed to solve was making our database change process visible to the whole team. We had to modify our processes and the expectations we had of individual people within the team to deliver better, more consistent value at the data layer.

The tools we chose for our circumstances don’t apply completely to PostgreSQL because they were SQL Server specific. But the process and benefits of building a “database as code” mindset within the team accelerated our velocity and quality. And Joe didn’t have the weight of the world on his shoulders.

Ryan includes links to additional resources and tells an interesting story along the way.

Comments closed

Thoughts on Software Development and Postgres

Published 2023-01-09 by Kevin Feasel

Ryan Lambert thinks about software development:

Question: Do you store your SQL code in GitHub (or other source control, e.g. GitLab)?

Yes! All mission critical SQL I am involved with is saved in a Git repo, either public or private depending on the project. The use of source control for mission critical code (SQL, Python, etc.) is non-negotiable. A good portion of my “not trivial” code is also stored in source control, with my trend leaning towards more code in source control. The trivial code isn’t worth the effort of putting it into source control or the bloat it creates in those projects.

Read on for more.

Comments closed

The Unimportance of IOPS

Published 2023-01-09 by Kevin Feasel

Kellyn Pot’vin-Gorman explains that there are things more important than IOPS when considering database choices:

IOPs is Overrated, yeah, I said it.

How many compute, storage area networks, hard drive vendors and storage services have posted their IOPs capabilities in marketing and didn’t include the throughput (MPBs)?  Why when someone sends me IOPs for an Oracle database do I thank them kindly and ask for throughput? 

Read on for more. This post is specific to Oracle but the general principle applies to SQL Server and all other relational databases as well.

1 Comment

Finding Skipped Identity Values in a Table

Published 2023-01-09 by Kevin Feasel

Brent Ozar minds the gap:

When someone says, “Find all the rows that have been deleted,” it’s a lot easier when the table has an Id/Identity column. Let’s take the Stack Overflow Users table:

It has Ids -1, 1, 2, 3, 4, 5 … but no 6 or 7. (Or 0.) If someone asks you to find all the Ids that got deleted or skipped, how do we do it?

Click through for two methods, one specific to SQL Server 2022 and one which works for all versions of SQL Server.

Comments closed

OData Feeds and Dynamic Data Source Errors

Published 2023-01-09 by Kevin Feasel

Chris Webb handles an error:

I’ve blogged about the “dynamic data sources” error and the Web.Contents function several times (most recently here), main post here, but never about the fact that you can encounter the same error when working with OData data sources and the OData.Feed function. More importantly, while it seems like the solution to the problem is the same for both functions this is not the case! In fact, the solution may be simpler than you might think.

Click through for an example.

Comments closed

Setting Up Transparent Data Encryption

Published 2023-01-09 by Kevin Feasel

Matthew McGiffen enables transparent data encryption on a database:

Transparent Data Encryption (TDE) is one of the easiest ways of encrypting your data at rest. In the previous posts we looked at what TDE is and how it works.

In this post we go through the steps of setting TDE up. You can set up TDE when you first create a database (which we will look at in this post), or you can apply it to an existing database (which we’ll cover in posts I’ve got coming up). In the latter case, once TDE has been enabled it will encrypt your existing data in the background. In either case the steps are the same.

Read on to see what you need to do.

Comments closed

End-to-End Testing via shinytest2

Published 2023-01-06 by Kevin Feasel

Russ Hyde begins a new series:

{shinytest2} builds upon the {shinytest} package and was written by Barret Schloerke and his colleagues at RStudio. Like puppeteer, {shinytest2} uses the Chrome DevTools Protocol to interact with the browser, which is a pretty stable basis for building a browser automation tool (the predecessor {shinytest} was built on a now-unsupported browser library called PhantomJS, so we strongly recommend migrating to {shinytest2} if you are still using {shinytest}). Test scripts are written in R and so should be accessible to R developers who are comfortable with {testthat}. There is an automated tool (described in the next post) for creating these test scripts. Also, {shinytest2} understands the architecture of shiny apps, and so it is simple to access the input and output variables that are stored by a shiny app at any given time, the inputs can be modified easily as well – to access these variables using the more general UI-based end-to-end testing tools is much more difficult.

Read on for the “why” behind this series and the next posts will get into more of the “how.”

Comments closed