A Fine Slice Of SQL Server
Am playing around with Always Encrypted for the first time. I was just following along the basic tutorial and encrypted some columns in my AutoDealershipDemo database. But then I decided to go crack open the data page using my friend DBCC PAGE.
Read on to see how you can get the results of DBCC PAGE into a table. My recollection is that there are some limits to what it can write into the table, but it’s pretty good on the whole.
Comments closedJose Manuel Jurado Diaz scales out:
In today’s data-driven landscape, we are presented with numerous alternatives like Elastic Queries, Data Sync, Geo-Replication, ReadScale, etc., for distributing data across multiple databases. However, in this approach, I’d like to explore a slightly different path: creating two separate databases containing data from the years 2021 and 2022, respectively, and querying them simultaneously to fetch results. This method introduces a unique perspective in data distribution — partitioning by database, which could potentially lead to more efficient resource utilization and enhanced performance for each database. While partitioning within a single database is a common practice, this idea ventures into partitioning across databases.
Click through to see what the code looks like for this.
Comments closedKevin Wilkie continues the advent of code series. The first part builds a small tally table and a loop:
Today we’re going racing! Sadly, it’s so not F1 or NASCAR racing. Snail racing is more like it since we’re moving millimeters by the end, but at least we’re closer to getting snow back to the elves, so let’s go racing!
Given a few numbers that are times and current record distances, this actually doesn’t look too bad to work with. First, as always, we have to load our data into SQL Server. This time, I loaded all of it into one table.
The second part goes back to the big tally table:
Comments closedSadly, this does make our numbers rather large, so we’re back to using the big ole Tally table we created for Walking Through Advent of Code Day 5.
This time I made it a little simpler on myself and just removed all of the spaces myself and placed the data in variables (one for time and one for distance). I thought this was an excellent idea since only one number would come out of all of this work.
Etienne Lopes takes us through a scenario:
Let me start this post with a question, “Do you think that it can be beneficial to have a single column index for the foreign key column in the child table?“
Well, I believe I can ear three types of answers to this question:
- Always!
- Never!
- It Depends…
Click through for Etienne’s answer. I’d still prefer these indexes to have multiple uses, which generally means having enough columns on the index to act as a covering index for one or more important queries. But Etienne does show a good use case for this single-column index.
Comments closedErik Darling plays the roles of both Goofus and Gallant here:
All sorts of bad things happen when you do this. You can’t index for this in any meaningful way, and comparing non-string data types (numbers, dates, etc.) with a double wildcard string means implicit conversion hell.
You don’t want to do this. Ever.
Unless you want to hire me.
Click through for good advice on the proper use of data types and input parameters.
Comments closedSebastian Sauer takes us through a demo:
In this post, we’ll investigate the consequence of z-standardizing the predictor variables, and in addition the outcome variable in a simple logistic regression setting.
Do some coefficients change as a result of standardizing the values?
Click through for the example and what z-standardization does to the model.
Comments closedTomaz Kastrun continues a series on Microsoft Fabric. Day 18 has us looking at Power BI:
We have created a Power BI report directly from the datalake and today we will check how to do same with dashboard and paginated reports.
Day 19 covers event streaming:
In Fabric, you can create streaming semantic model and when selecting you will get the usual sources:
Day 20 shows how you can work with notebooks in Microsoft Fabric:
Comments closedNotebooks have been around for a long time and people, community, and professionals have proven the usability, practicality, versioning and reliability of notebooks. Not to mention the clarity and hygiene. But opinions are also divided.
The purpose of this post today is to check for a couple of functionalities that might not be that straightforward when it comes to notebooks.
Sandeep Pawar digs into the REST API:
Accessing Fabric REST endpoints in Fabric notebooks was already easy but it became easier and straightforward with semantic-link version 0.4.0. You can use the FabricRestClient class from sempy to set up a REST client and call the APIs. Authentication is automatically managed for you.
Click through to see how it works, as well as some warnings or things to keep in mind along the way.
Comments closedAndy Brownsword asks if this thing is a number:
Data validation is key when ingesting from external sources. As we can’t always be certain of data quality we inevitably find bad data which needs to be handled. Here I wanted to look at a couple of options for validating numeric data.
Here’s the scenario – we’ve got data which may have been received via a flat file or passed into our database, and it should be a numeric value. How can we weed out the valid from invalid data?
Read on for the wrong answer (at least, the wrong answer given our expectations as developers or data platform specialists), followed by a good answer.
Comments closedErik Darling goes into isolation:
I’ve talked about isolation levels a bit lately because I need you all to understand that no isolation level is perfect, and that most everyone is completely wrong about how they really work.
For a very high percentage of workloads, Read Committed Snapshot isolation is the best choice. Why?
Read on for that answer. I think Erik is right about people misunderstanding how the different isolation levels work, as well as the root cause of not having a great place to try it out. You can build out demos of how different transaction isolation levels will work but some of the nuanced operations can be hard for one person with a couple new query tabs open to emulate.
Comments closed