Press "Enter" to skip to content

Curated SQL Posts

Extracting Data from DAX Measures into CSV

Published 2022-06-17 by Kevin Feasel

Gilbert Quevauvilliers builds a process:

In this blog post I am going to demonstrate how to use the new Power Automate Flow to extract data from a DAX measure into a SharePoint CSV file.

I got this idea after reading the blog post from the Microsoft Power BI Team: Unlocking new self-service BI scenarios with ExecuteQueries support in Power Automate | Microsoft Power BI Blog | Microsoft Power BI

The great news is that this works on Power BI Pro, Premium Per User and Premium.

Read on to see how.

Comments closed

Refreshing SQL Managed Instances which Use TDE

Published 2022-06-17 by Kevin Feasel

Bradley Ball keeps the dev environment up to date:

Hello Dear Reader!  I was working with some friends lately and we needed to set up a process to refresh their Development Environment databases from Production.  Additionally, the databases are encrypted using Transparent Data Encryption, The SQL MI instances are in different regions, and the SQL MI Instances are in different subscriptions.  To duplicate the environment, in order to match our friends, we did the following setup.

Click through for a high-level overview, step-by-step guidance, and a whole lot of detail.

Comments closed

Reading the SQL Server Error Log

Published 2022-06-17 by Kevin Feasel

Lee Markum has two ways to read the SQL Server error log:

Reading the SQL Server Error Log is important when troubleshooting many issues for SQL Server. Some example issues would be errors related to Always On Availability Groups, long IO occurrence messages, and login failures.

Unfortunately, the SQL Server Error Log can be very full of information, making specific things hard to find, especially if you’re just visually scrolling the Error Log. Even if you’re recycling the Error Log each day and keeping 30 or more days of error log, on a busy system, the error log can still be quite full, even for a single day.

Click through for those techniques.

Comments closed

Searching for SQL Server Backup Locations

Published 2022-06-17 by Kevin Feasel

David Fowler can’t remember where those backups went:

Sometimes I find remembering where a particular server sends its backups to a nightmare.

You might have servers backing up to different locations, you might have different locations for individual databases and different locations for your fulls, diffs and logs. You might be trying to get your head around a customer’s set up, where the backups make no logical sense at all.

Whatever you’re up to, at some point, for some reason you’re going to need to access your backup location to get at the files.

Read on for a Powershell script which can help out with this task.

Comments closed

Normalization Layers in Deep Learning Models

Published 2022-06-16 by Kevin Feasel

Zhe Ming Chng explains why data normalization matters in data science:

You’ve probably been told to standardize or normalize inputs to your model to improve performance. But what is normalization and how can we implement it easily in our deep learning models to improve performance? Normalizing our inputs aims to create a set of features that are on the same scale as each other, which we’ll explore more in this article.

Also, thinking about it, in neural networks, the output of each layer serves as the inputs into the next layer, so a natural question to ask is: If normalizing inputs to the model helps improve model performance, does standardizing the inputs into each layer help to improve model performance too?

Click through for the tutorial.

Comments closed

Comparing Data Analysis in Java and Python

Published 2022-06-16 by Kevin Feasel

Manu Barriola does some data analysis in a pair of quite different languages:

Python is a dynamically typed language, very straightforward to work with, and is certainly the language of choice to do complex computations if we don’t have to worry about intricate program flows. It provides excellent libraries (Pandas, NumPy, Matplotlib, ScyPy, PyTorch, TensorFlow, etc.) to support logical, mathematical, and scientific operations on data structures or arrays.

Java is a very robust language, strongly typed, and therefore has more stringent syntactic rules that make it less prone to programmatic errors. Like Python provides plenty of libraries to work with data structures, linear algebra, machine learning, and data processing (ND4J, Mahout, Spark, Deeplearning4J, etc.).

In this article, we’re going to focus on a narrow study of how to do simple data analysis of large amounts of tabular data and compute some statistics using Java and Python. We’ll see different techniques on how to do the data analysis on each platform, compare how they scale, and the possibilities to apply parallel computing to improve their performance.

Read on to see how the two compare. Note that this is base Java and Python+Pandas, not Spark/PySpark, Koalas, etc.

Comments closed

When SELECT * Doesn’t

Published 2022-06-16 by Kevin Feasel

Chad Callihan protects the reputation of SELECT *:

There are plenty of scenarios where using SELECT * can be an issue. Using SELECT * with EXISTS isn’t one of them.

When using EXISTS and SELECT * together, SQL Server is smart enough to realize what you’re doing and knows you don’t care about what’s in the SELECT.

Read on for an example. I’ve trained myself (been trained?) still to use SELECT 1. The reason is, I know SELECT * works exactly the same way but the benefit of using SELECT 1 is that doing this consistently allows you to do a search for SELECT * in your code base to find actual perpetrators (people writing queries expecting to return the entire result set and which may be susceptible to performance problems or future maintainability problems). Using SELECT 1 in the EXISTS clause means you get fewer false positives in that search as a result.

That said, Joe Celko chimes in to provide some of the history behind SELECT * as the convention for references in the EXISTS clause.

Comments closed

Against Tibbling

Published 2022-06-16 by Kevin Feasel

Hugo Kornelis hates tibbling:

Probably the one I hate most. And one that is stubbornly persistent. Object name prefixing.

Or, to be more precise, the standard that enforces that all table names need to start with a prefix that designates them as a table, and all view names with a different prefix to clearly mark them as a view. Typically tbl_ and vw_ are used, though I have also seen just the letters t and v, and I have seen them with or without underscores.

I hate this coding standard (or rather, naming standard) with a vengeance. For a few reasons. The perceived benefit is in fact not a benefit at all. It is detrimental to a quick understanding of what I see on the screen. But my biggest objection is that it negates one of the greatest benefits of views.

Read on to understand why this is a bad idea. I completely agree with Hugo on this.

Comments closed

Coding Standards Writ Large

Published 2022-06-16 by Kevin Feasel

Kenneth Fisher points out the downside of coding standards:

I’m betting you can start to see the problem right? Joe is supporting application A. His team wrote it, and they wrote it using a strict set of coding standards. Jane supports application C. Her team didn’t write it, it was transferred over during the reorg last March. Her team has a set of coding standards they enforce, unfortunately, application C wasn’t written with them. All of their new code is however because their manager wants strict enforcement of their coding standards. Oh, and Joe and Jane are both being moved to be part of a new team next week. They’ll be supporting some older code, that, you guessed it, is using a whole different set of coding standards, if any.

Starting from the Coasean notion of the firm as a means of internalizing externalities and achieving economies of scale + scope, this is just about where you hit the margin for added productivity… Rephrasing this not to be in economist jibber-jabber, this kind of thing is a big part of why really large companies essentially spin off mini-companies and act nearly-independently under the parent company’s umbrella. It’s essentially impossible to create and enforce a meaningful set of standards once you hit a certain threshold of developers, especially when it comes to the more opinion-heavy standards.

Comments closed

Tips for SQL Developers

Published 2022-06-16 by Kevin Feasel

Lee Markum has a few tips for you:

SQL Server Developers are under-rated.

That’s right! I’m a DBA and I said, “SQL Developers are under-rated.” Dedicated SQL Developers help I.T. teams by writing efficient code that gets just the data that is needed and in a way that leverages how the database engine works best. How do you ensure you’re doing great work for your company and building code that will stand the test of time?

I’m so glad you asked!

Click through for Lee’s advice. One big thing I’d add to Lee’s list is to understand the domain. Query writing skills are quite fungible across domains—moving from health care to auto parts sales, you don’t need to re-learn SQL—but understanding some of the arcana of the organization and its industry makes it a lot easier to know you’re writing good queries, getting valid data back, and not forgetting some important business rule.

Comments closed