Press "Enter" to skip to content

Curated SQL Posts

When Shrinking Makes Sense

Published 2023-01-10 by Kevin Feasel

Eitan Blumin reminds us that database shrinking isn’t always bad:

Shrinking databases and database files in SQL Server is a widely known “worst practice”.

Usually, it’s because it’s assumed that the database files are expected to auto-grow again after the shrink.
So, in truth, it’s not the shrink itself that’s the problem… It’s the auto-growth!

But… What if you DON’T expect the database file to auto-grow back to what it was before?
For example, what if you truncated/deleted/migrated/archived huge chunks of data from your database, which you don’t expect to be returned later? Or what if you performed a massive data compression or migrated to clustered columnstore indexes, which reduced your data size significantly?

Read on for more thoughts along these lines, problems you might run into, and scripts to help you along the way.

Comments closed

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