A Fine Slice Of SQL Server
Erik Darling shows us a table having an existential crisis:
This is one of the most frustrating things I’ve seen from the optimizer in quite a while.
Here are a couple tables, with a foreign key between them:
Read on to see how EXISTS and NOT EXISTS handle an empty child table.
Imke Feldmann continues pulling on a thread:
In a previous article I have described how to use Fuzzy merge to match misspelled data to valid categories. With the sample data at hand, all values could be matched to existing categories. But what can you do if there are some entries that cannot be matched to existing categories?
I would create an “unmatched” value to the categories table before loading to the data model and assign the “unmatched”-value to all unmatched entries. Then I would create a measure that counts the entries within the unmatched category. I would then create a data driven alert, based on this measure. That would trigger an e-mail to the person responsible for maintaining the list. Or, if responsibilities are a bit more complex, trigger a flow in Power Automate that could handle certain conditions to be followed as well.
Click through to see it in action.
Comments closedChad Callihan walks us through using AT TIME ZONE in SQL Server:
Dealing with time zones in general can be a headache. Thankfully, SQL Server 2016 included a new clause to make working with time zones more manageable. Let’s look at a couple examples:
I’m in Eastern Standard Time so we’ll start here. Keep in mind that when no offset information is included, SQL Server is going to assume that the date value is in the target time zone.
This works best when your dates are stored in UTC, but Chad does show how to convert between two other time zones. This does not perform all that well when you need to convert a lot of rows, but if you’re doing one or two conversions, it’ll do just fine.
Comments closedGail Shaw explains the concept of deferred compilation:
When I talked about row estimations for table variables, I mentioned ‘deferred compile’, but didn’t give a whole lot of details. What, then, is a deferred compilation? Let’s start with how batches work normally.
T-SQL is an interpreted language. While we talk about compiles, they’re not compilations in the sense of what happens to C++. There’s no conversion of the script to a machine language or intermediate language which is used from that point onwards. Every time a batch executes, it has to be parsed, bound and have an execution plan generated or fetched from cache.
Click through to learn what it does mean.
Comments closedPaul Turley explains a choice:
When should you use dataflows vs regular Power Query? I didn’t jump on the dataflows bandwagon and struggled to adopt them at first. Frankly, Power Query is easier to use. The browser-based dataflows designer is quite impressive but it is not as responsive and convenient as the desktop app, so this is a bit of a trade-off. The power and value of dataflows becomes apparently when the business reaches a certain stage of data culture maturity.
Before we can address the question of whether to use Power BI Dataflows, conventional Power BI queries, or any other approach to source and transform data; we need to briefly review different options for orchestrating a Business Intelligence solution in the Microsoft cloud ecosystem.
Read on for a bit of architecture and the explanation.
Comments closedEd Elliott continues a series on SQL syntax concepts in Spark:
The next example is how to do a CTE (Common Table Expression). When creating the CTE I will also rename one of the columns from “dataType” to “x”.
Read on for the answer.
Comments closedAndy Levy has a new reason for us:
I got a merge a while back that included a change I wasn’t expecting from one of my developers. All they were doing was renaming a column on a table. We had names settled a while ago. What’s up with that?
Turns out, when I created the table, I named one of the fields
BrithYear. This merge request corrected it toBirthYear. The misspelling slipped past me, past everyone else, it was only discovered because when this developer was building the a new query into their code, they spelled the field as one would expect, and the query threw an error.
There’s many a reason not to use SELECT * in application code; this is one I don’t think I’d heard before.
Joey D’Antoni shows off some of the benefits of using Azure Cloud Shell:
One of the challenges of being a consultant is having to work with a number of clients, and having different login credentials and accounts. In the early days of Azure, this was exceptionally painful, but over time the experience of using the portal with multiple identities and connecting to Azure tenants has gotten much easier. However, when writing PowerShell or Azure CLI code, switching accounts and contexts is slightly more painful. Also, when you are doing automation, you may be touching a lot of resources at one time, you want to be extra careful that you are in the right subscription and tenant.
Enter cloud shell.
Read on to see how to use it, get an idea of its cost, and see some of the benefits.
Comments closedJess Pomfret shows how we can copy a database using dbatools:
We’re working hard on the AdventureWorks2017 database, perhaps getting it ready for an upgrade – since it’s now 3+ years out of date.
dbatools has so many functions, and I know I’ve mentioned it before, but
Find-DbaCommandis a great way of looking for what we need. I want to know what the default backup path is set to, and since I’m just backing up and restoring to the same server, we already know that the instance has the required permissions here. If only there was an easy button for this…
Spoiler alert: there is.
Comments closedSheldon Hull has a hoarding problem to solve:
In my case, I’ve run into 50TB of old backups due to tooling issues that prevented cleanup from being successful. The backup tooling stored a sqlite database in one subdirectory and in another directory the actual backups.
I preferred at this point to only perform the lifecycle cleanup on the backup files, while leaving the sqlite file alone.
Click through to see how to do this using Powershell.
Comments closed