A Fine Slice Of SQL Server
Sebastian Sauer shows two ways to dereference a parameter:
Using the tidyverse ecosystem, programming – instead of interactive use – may be something different or unusual and it may take some time to wrap your head around it.
In this post, I’ll show how to deal with a standard situation (using tidyvserse’ nonstandard evaluation). More precisely, there are two (complementary) situations we’ll address:
Read on for those techniques.
Comments closedUst Oldfield notes the importance of data quality:
We can safely assume this because a lot of organisations do not have data quality at the top of the priority lists. Why might this be the case? Because monitoring data quality and correcting poor quality data is hard. If it was easy, every organisation would have a strategy and method for tracking and improving data quality.
Often, inertia – driven by an overwhelming amount of information to the risks – sets in as it’s difficult to know where to start. So, where do you start?
I’d say there’s an incentive alignment problem with data quality: organizations want it but not enough that they’d trade anything else for it. And agents within the organization consider data quality a chore, so they’re looking for the minimum viable path. Then, for end users, we consider it even more of a chore (or a nuisance). Furthermore, I’m one of those end users who will put in fake data if I can get away with it on the principle that I don’t want you to have my personal information because you’re probably going to sell it or lose it.
Comments closedTom Collins makes a connection:
Most SQL Servers use a large portion of the authentication as Windows Authentication – utilising Kerberos and NTLM protocols via Active Directory. So when it comes to considering moving on-prem SQL Server resources to Cloud Providers – Active Directory is a foundational question. There are other methods than Microsoft Directory – which I’ll discuss in future posts.
Utilising AWS RDS SQL Server with Windows Authentication methods is only possible using the AWS Directory Service. i.e The AWS RDS SQL Server is created and added as a resource to the AWS Directory Service . If on-prem users require access to the AWS RDS SQL Server via Kerberos , a forest trust is required between the AWS Directory Service and the on-prem AD.
For this post – the focus is on an existing on-premises SQL Server inventory using Microsoft Active Directory Services.
Read on to see what you’d need to do to implement this.
Comments closedKevin Wilkie unlocks the power of QUOTENAME():
When I first heard about QUOTENAME, I was like “This is rather useless. It just puts brackets around whatever. I can do it just as easily hard-coding the strings.”
Truly, I’m not completely wrong, but it’s a heck of a lot more fun to knock things out with the QUOTENAME function!
But there’s more that you can do with this function, as Kevin notes.
Comments closedDrew Furgiuele is asking for it:
Consider this: you’ve developed a data ingestion strategy that is taking in remote thermostat readings. Usually, the devices report in on a set frequency and you’re able to calculate aggregate readings an hourly interval. A materialized view could be created that does this calculation and stores the results out for querying. But what if something causes some of this data to become duplicated? You’d first have to eliminate these duplicates, re-ingest the data, and then do your calculations again.
This is where we can leverage creating a materialized view over a materialized view. Our first materialized view will handle the deduplication, and our second can handle the aggregation of the deduplicated data.
Yo dawg, I heard you like materialized views, so I put some materialized views in your materialized views so you can materialize views while you materialize views.
Comments closedErik Darling doesn’t need your costs:
Costs are okay for figuring out why SQL Server’s cost-based optimizer:
– Chose a particular query plan
– Chose a particular operator
Costs are not okay for figuring out:
– Which queries are the slowest
– Which queries you should tune first
– Which missing index requests are the most important
– Which part of a query plan was the slowest
Yep. And read on for more information.
Comments closedChad Callihan has a couple notes about dropping offline databases:
“Oops” is not a word you want to utter often as a database administrator. Sure, accidents happen (who hasn’t missed that WHERE clause when deleting data) but there are steps you can take to minimize problems that will ruin your day, week, etc. Let’s look at a few ways to delete databases that supposedly are no longer needed.
“I’m feeling lucky” isn’t something most DBAs choose.
Comments closedImke Feldmann has another Excel function to convert:
TRIMMEAN is a statistical function in Excel that calculates the “mean taken by excluding a percentage of data points from the top and bottom tails of a data set”. So you can use it if you want to exclude potential outliers from your data. Daniil Maslyuk has a nice approach for it in DAX, but in here I want to share my M version for Power Query for it.
Read on for the function and how you can use it.
Comments closed