A Fine Slice Of SQL Server
Deepthi Goguri takes us through a fairly new feature in SQL Server:
With SQL Server 2017 and below versions, SQL Server always estimates one row for table variable as the table variable data gets inserted during the run time, so optimizer doesn’t know how many values it can expect coming out of the table variable. Due to this bad estimation, performance of the queries is effected.
Click through to see how this has changed in SQL Server 2019.
Comments closedDan Morris, et al, walk us through a survival analysis scenario:
In contrast to other methods that may seem similar on the surface, such as linear regression, survival analysis takes censoring into account. Censoring occurs when the start and/or end of a measured value is unknown. For example, suppose our historical data includes records for the two customers below. In the case of customer A, we know the precise duration of the subscription because the customer churned in December 2020. For customer B, we know that the contract started four months ago and is still active, but we do not know how much longer they will be a customer. This is an example of right censoring because we do not yet know the end date for the measured value. Right censoring is what we most commonly see with this form of analysis.
Click through for an intro as well as a half-dozen notebooks.
Comments closedPatrick LeBlanc explains why star schemas are so important for Power BI:
Are you using a flat table in your Power BI report? You may be missing out! Patrick explores what a STAR SCHEMA is and why you should be using it.
Click through for a video, as well as some guidance. That Kimball fellow sure was a sharp one, he was.
Comments closedHugo Kornelis starts a new sub-series within an existing series:
Welcome to part sixteen of the plansplaining series. The first of a few posts about how temporal tables affect execution plans. In this post, I’ll build on the last four posts on data modifications, building on the more generic discussion of data modification in the previous four posts. Later posts will look at data retrieval and some specific scenarios.
Hugo hits the highlights of temporal tables and how they handle insertion, deletion, and updating scenarios.
Comments closedMikey Bronowski shows how to shuffle data around:
ImportExcel is a powerful tool to manage MS Excel, however, the user is not limited to data stored in worksheets. We can import from and export to SQL Excel data using a combination of ImportExcel and dbatools modules.
You can read how to start with dbatools in this blog series.
Click through for a demo.
Comments closedChad Callihan has a few methods for us to avoid dividing by zero:
In the real world, everyone knows that if you divide by zero a wormhole will open up and swallow the universe. In SQL Server, it’s not good, but it’s not nearly as dramatic. I encountered the following error this week and thought it would make a good topic:
Msg 8134, Level 16, State 1, Line 6
Divide by zero error encountered.There are multiple ways to handle this error message in SQL Server and some are better than others. Let’s take a look at a few.
Click through for those methods and try to keep this universe existent—it’s where I keep all my stuff.
Comments closedVictoria Xia announces an improvement to ksqlDB:
One of the most highly requested enhancements to ksqlDB is here! Apache Kafka® messages may contain data in message keys as well as message values. Until now, ksqlDB could only read limited kinds of data from the key position. ksqlDB’s latest release—ksqlDB 0.15—adds support for many more types of data in messages keys, including message keys with multiple columns. Users of Confluent Cloud ksqlDB already have access to these new features as Confluent Cloud always runs the latest release of ksqlDB.
Read on for more information on this, as well as some of the ramifications of this change.
Comments closedShel Burkow has the start of an interesting series:
Neither loops nor branches were used in any of the T-SQL work, but that doesn’t mean they aren’t there. They are – visible underneath in the query plans, which describe the process steps (operators and data flow) the SQL Server database engine follows to produce results. A salient example of looping is the Nested Loops operator used in the T-SQL rewrite query plan to match each outer row to an inner row, i.e. perform the INTERSECT. This style of coding, in which the logic of the computation is expressed rather than the lower-level control flow detail, is declarative programming, and is a major theme of this series. (The latter being imperative programming.)
This is a concept I’m really big on: I think functional programming languages are easier for data platform developers to pick up than object-oriented or imperative languages for the reason that both are declarative, so many of the programming metaphors for one apply to the other.
Comments closedShane Ducksbury explains where Elastic Beats fits in the ELK stack:
After my last blog post about Logstash, Elasticsearch, and Kibana, I wanted to investigate something else I kept coming across during my Logstash research: Elastic Beats.
Beats initially appeared to me to be a way to send data to Elasticsearch, the same as Logstash, leading me to wonder how Beats is different and where it fits in the ELK stack. In this blog, I’ll take a deeper look at Beats to understand how it works, what you might use it for, and how it compares with Logstash.
Read on to learn more about Elastic Beats and how this is quite different from Logstash.
Comments closedAllan Hirt has a warning for us:
DNNs are supported as of SQL Server 2019 CU2 and require Windows Server 2016 or later. I wrote more about them in my blog post Configure a WSFC in Azure with Windows Server 2019 for AGs and FCIs. Go there if you want to see what they look like and learn more.
Right now, I cannot wholeheartedly recommend the use of DNNs for listeners or FCIs if you are using Enterprise Edition. Why?
Read on to learn why.
Comments closed