A Fine Slice Of SQL Server
Erik Darling isn’t content with simple searches:
Like having a built-in type to make dynamic SQL more easily managed, it would also be nice to have some mechanism to manage dynamic searches.
Of course, what I mean by dynamic searches is when you have a variety of parameters that users can potentially search on, with none or few of them being required.
Erik provides two techniques and contrasts the two, so check it out.
Comments closedJosephine Bush walks around town with a lantern looking for a good foreign key:
There are plenty of times I’m called upon to fix data. To do this, I must know what dependencies are in the database. Foreign keys are a crucial aspect of maintaining data integrity within relational databases. They establish relationships between tables, ensuring data references remain consistent and accurate. In an Azure SQL Database, identifying and managing foreign keys is essential for maintaining a well-structured and reliable database architecture.
Click through for a primer on foreign key constraints, a few ways to find them, and some closing thoughts on working with tables containing foreign key constraints.
Comments closedNorm Matloff answers a reader question:
In my December 22 blog, I first introduced the classic parametric quantile regression (QR) concept. I then showed how one could use the qeML package to perform quantile regression nonparametrically, using the package’s qeKNN function for a k-Nearest Neighbors approach. A reader then asked if this could be applied to random forests (RFs). The answer is yes, and this will be the topic of the current post.
Read on to learn more about how to do this, including some of the challenges you’ll face along the way. H/T R-Bloggers.
Comments closedSteven Sanderson takes us through time series creation:
R offers two primary time series classes:
- “ts”: Base R’s classic time series object, designed for regularly spaced data.
- “xts”: Part of the ‘xts’ package, offering enhanced flexibility and features.
Click through for examples using both of these methods.
Comments closedIn this video, we will run through detailed configuration recommendations for SQL Server on Linux. We will also find out where you can find your error logs.
This is a mix of show and tell, as I couldn’t show some of the recommendations on my particular hardware. Still, there are a whole boatload of links to additional resources if you want to learn more about why Microsoft chose specific things to show in their SQL Server on Linux performance tuning recommendations.
Comments closedMeagan Longoria has a script for us:
While a private endpoint and vNets are preferred, sometimes we need to configure Azure SQL Database or Azure Storage to allow use of public endpoints. In that case, an IP-based firewall is used to prevent traffic from unauthorized locations. But Azure Data Factory’s Azure Integration Runtimes do not have a single static IP. So how do we keep the firewall updated so that ADF can access these resources?
One option is to run everything through a self-hosted integration runtime, which can have a static public IP. But that also means you have to install (and keep updated) a Java SDK if you are converting data to Parquet or ORC files.
Another option is to obtain the IP range list published by Microsoft and update the firewall. That is what I did for a recent project.
Read on to learn more about the latter option, including a Powershell script to do the work.
Comments closedToday, our elves are working hard on having us come up with the next number in a sequence. Thankfully, they do give us a few good examples to walk through on how they would like us to do it. And with that, it’s off to the races!
Click through for Kevin’s answer.
Comments closedTom Martens performs two of the three R’s:
JSON is a straightforward, text-based data-interchange format fully independent of any programming language. This simplicity is why JSON documents have become so widespread.
Often the result of querying a REST API is returned as a JSON document, but this is not the only use case why I consider it important to get familiar with reading from and writing to a JSON file. I think a JSON document is ideal for ingesting values into a Python script or any code, values that are helpful to control the logic and behavior of the script. But of course, it’s also easy to store the value of a variable inside the JSON document, where this value will be used when the script is running again.
Click through for some Python code to perform these operations. There are about a dozen other methods you can use as well, but this is one of the best.
Comments closedJosephine Bush automates a deployment:
A well-structured Terraform module for Azure SQL DB typically consists of the following elements:
- Main Configuration Files:
main.tf,variables.tf,outputs.tf- Helper Files: (if necessary)
locals.tf,providers.tf, etc.If you want to learn more about the basics of Terraform, you can visit my previous blog post.
Click through to see how Josephine has put together the Azure SQL Database deployment module.
Comments closedErik Darling goes back to the well on a popular and important topic:
One of the most fun parts of my job is telling people that parameter sniffing is a good thing. They’re often shocked, because the phrase has such ominous connotations to developers.
The parameters! They get sniffed! Performance! Something! Stuff!
Parameter sniffing does not mean the sky is falling. Like I’ve said before, if it were such a bad idea, it would have been abandoned.
Read on for more thoughts on the topic.
Comments closed