A Fine Slice Of SQL Server
In this video, I cover a powerful ensemble method for classification: random forests. We get an idea of how this differs from CART, learn the best possible metaphor for random forests, and dig into random search for hyperparameter optimization.
Click through to see the video in all its glory.
Comments closedGilbert Quevauvilliers wants to know what time it is:
How to add current DateTime to existing PySpark data frame in a Fabric Notebook
In the blog post below, I am going to describe how to add the current Date Time to your existing Spark data frame.
This is really useful when I am inserting data into a Fabric Lakehouse table, and I want to know when the data got inserted.
Read on for the answer.
Comments closedSteven Sanderson counts what doesn’t exist:
Welcome back, R enthusiasts! Today, we’re going to explore a fundamental task in data analysis: counting the number of missing (NA) values in each column of a dataset. This might seem straightforward, but there are different ways to achieve this using different packages and methods in R.
Let’s dive right in and compare how to accomplish this task using base R, dplyr, and data.table. Each method has its own strengths and can cater to different preferences and data handling scenarios.
Read on for 3 1/2 separate methods.
Comments closedMartin Schoombee continues a series on orchestration in Azure Data Factory:
We’re finally ready to dive into the Data Factory components that form part of the framework, and we’re going to work our way from the bottom up. To paraphrase the previous blog post, worker pipelines perform the actual work of either moving data (from source to staging) or executing a stored procedure that will load a dimension/fact table.
Although worker pipelines can contain any number of tasks you may need, my worker pipelines that move data from a source system into the staging area follow a similar pattern with at least the following activities:
Click through for that list, as well as more information.
Comments closedA Fabric Pipeline uses JSON as source code. They are also saved in repositories as JSON.
We first idea we get is editing the pipeline in JSON format. We can copy the JSON and create new pipelines with small variations, making changes directly on the JSON.
However, at first sight we get disappointed, because the pipeline doesn’t allow the JSON to be edited. We have the option to view the JSON, but nothing else.
Read on to see how to tell the Fabric pipeline who’s boss.
Comments closedJoe Obbish has a public service announcement:
I found a few stored procedures with single plan cache entries over 500 MB of memory each according to sys.dm_exec_cached_plans while optimizing the plan cache in production. The stored procedures were relatively simple and both execution plans were less than 1 MB when saved as sqlplan files. I was confused, offended, and confused.
Read on to learn how this happened, see an example of it in action, and how you can avoid it in your own environment.
Comments closedThe NDepend blog talks discriminated unions:
In recent years, there has been a notable surge in inquiries from the C# community regarding language-level support for C# Discriminated Unions. What individuals truly desire is the ability to express complex data structures more elegantly and effectively.
This blog post offers a thorough introduction to simulating Discriminated Unions (DUs) in C# programs, highlighting their usefulness.
Discriminated Unions, also known as “or” types (because you select one from the set of options), are really powerful concepts in functional programming. And this article gives you a way to simulate the approach in C#, at least until C# catches up to F# in this aspect.
Comments closedEtienne Lopes goes deeper than I thought:
A few days ago a client brought me a question, he had two tables with the same data, one in each database (same SQL version and compatibility level in different environments with similar infrastructure and configurations). Both tables had an extremely selective column and both had indexes in that column. The thing is that when running a particular query on one Database, let’s call it DB1 here, it was really fast whereas running the same query on the other database (DB2) it was a lot slower. The subset of rows that matched the WHERE clause was the same and it was really small in both cases (since the tables had the same data).
“Ok, let’s have a look at that”, I said, and when doing so, the query running on DB1 was doing a Clustered Index Seek whereas the same query in DB2 was doing a Table Scan. Didn’t I say that both tables had indexes in that column?
Read on for the post. Etienne warns that it’s a big boy of a post, and it covers a lot, including “seek” operations that are scans in disguise, index levels, and more.
Comments closedErik Darling is keeping an eye on you:
Sort of recently, a client really wanted a way to figure out if support staff was manipulating data in a way that they shouldn’t have. Straight away: this method will not track if someone is inserting data, but inserting data wasn’t the problem. Data changing or disappearing was.
The upside of this solution is that not only will it detect who made the change, but also what data was updated and deleted.
Read on to see how it works. I’ve used temporal tables for this type of scenario, and they’re fine for stable table designs.
Comments closedBrent Ozar has a new chart for us:
The short story: SQL Server 2019 appears poised to swallow the SQL Server market altogether, hahaha.
The long story: ever wonder how fast people are adopting new versions of SQL Server, or what’s “normal” out there for SQL Server adoption rates? Let’s find out in the winter 2023 version of our SQL ConstantCare® population report.
Out of the thousands of monitored SQL Servers, SQL Server 2019 is now at 48% of the market! That’s the highest percentage we’ve seen for any version in the 3 years that we’ve been doing this analysis, up from last quarter’s 44% market share. Here’s how adoption is trending over time, with the most recent data at the right:
Click through for the graph and a bonus excursion on database size and associated hardware. And as always, this is an interesting view of one sample of SQL Server instances, but that sample may not (and probably does not) represent SQL Server usage as a whole.
Comments closed